Scribd
Upload
19 views2 pages

Sample Questions

The document contains questions about analyzing order and order item data to find the date with the most orders, cumulative order quantities by day, and customers who placed orders on consecutive days with quantities over 2. It also includes questions about identifying unique flight paths, calculating agent sign-in/sign-off durations, and developing a data model for movie and actor information.

Uploaded by

kdas
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
19 views2 pages

Sample Questions

The document contains questions about analyzing order and order item data to find the date with the most orders, cumulative order quantities by day, and customers who placed orders on consecutive days with quantities over 2. It also includes questions about identifying unique flight paths, calculating agent sign-in/sign-off durations, and developing a data model for movie and actor information.

Uploaded by

kdas
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Q1) two tables, orders and order items.

Orders:
(PK: ORDER_ID)
ORDER_ID | ORDER_DATE | CUSTOMER_ID
xyz 1/1/2019 person_a
zzz 1/1/2019 person_b
... ... ...

Order_items:
(PK: ORDER_ID, ITEM_ID)

ORDER_ID | ITEM_ID | QUANTITY


xyz asin1 2
xyz asin2 1
zzz asin1 3
zzz asin3 2
zzz asin5 1
... ... ...

a. date with the greatest number of orders


b. by day, the cumulative sum of quantity
c.customers who placed orders on 2 consecutive days both days having greater than 2 order QUANTITY

Q2) Routes question: Given a table with a combination of flight paths, how would you identify unique
flights whichever city is the destination or arrival location.
FROM | TO | DIST
SEA SF 300
CHI SEA 2000
SF SEA 300
SEA CHI 2000
SEA LND 500
LND SEA 500

Q3) Sign on, sign off question:


You have a table as follows:
Agent_ID | Event | dateTimestamp
a sign in 9am
a sign out 10:02am
a sign in 10:15am (took a break)
a sign in 12am
a sign in 13pm
a sign off 19pm
a sign in 22 pm
a sign out 22:15 pm
How would you approach writing a query which would calculate the duration in which agents were
signed on during the day?. Do not include Break timings
Outcome:
A 1st jan 21 xxx hrs

Q4). Can you give me a data model which will help answer the following questions. No SQL queries just
the data model.
a. Top grossing movie last year?
b. All movies which starred Brad Pitt.
c. Every actor which Bradd Pitt has worked with.
d. Top Movies (star rating) for 2019?

Q5) Deep Dive. Tell me one scenario where you found some gaps in your work which impacted a huge
impact to the business/clients. How did you drill-through during that scenario?
employee(employee_id,manager_id,join_date)
find all the employees who joined before their manager

salary(department_id,employee_id,salary)
List all employees with salary greater than the everage department salary and also greather than $50K

Monthly Revenue : company_name,month,revenue


Write a query to pull the monthly revenue as columns instead of rows.

ID table with duplicate rows: id1,id2


Select only the duplicate rows.

1. Which JOIN, why?


2. foreign key relationship explain.
3. one easy leetcode SQL problem.
4. two medium leetcode SQL problem.
5. 2 python questions (string comparison, csv manipulation)

You might also like