Overview
Welcome to our first SQL case study.
Those who submit an entry will be eligible to win a free copy of SQL For Data Analytics by Packt! The more weeks you participate, the more chances you get to win!
There is no deadline for submission.
Submission Instructions
Make a LinkedIn or twitter post with a screenshot or PDF of your answers. You can explain your thought process if you’d like. Just make sure to tag the Data in Motion LLC LinkedIn page or Twitter page.
Overview
This case study uses PostgreSQL. To successfully answer all the questions you should have been exposed to the following areas of SQL:
- Basic aggregations
- CASE WHEN statements
- Window Functions
- Joins
- Date time functions
- CTEs
Questions
- Which product has the highest price? Only return a single row.
- Which customer has made the most orders?
- What’s the total revenue per product?
- Find the day with the highest revenue.
- Find the first order (by date) for each customer.
- Find the top 3 customers who have ordered the most distinct products
- Which product has been bought the least in terms of quantity?
- What is the median order total?
- For each order, determine if it was ‘Expensive’ (total over 300), ‘Affordable’ (total over 100), or ‘Cheap’.
- Find customers who have ordered the product with the highest price.
DB Fiddle Instructions
Click ‘Edit on DB Fiddle’ to be taken to the in browser SQL playground where you can run your queries.