Identify Top-Buying Customers Daily with SQL Query
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
PRIME
+-------------+------------+----------+
| Customer_ID | Start_Date | End_Date |
+-------------+------------+----------+
| 1 | 2025-01-01 | 2025-12-31|
| 2 | 2025-03-15 | 2025-10-15|
+-------------+------------+----------+
ORDERS
+-------------+----------+------------+-----------+--------------+
| Customer_ID | Order_ID | Order_Day | Product_ID| Quantity_Sold|
+-------------+----------+------------+-----------+--------------+
| 1 | 1001 | 2025-04-01 | P123 | 3 |
| 1 | 1002 | 2025-04-01 | P456 | 2 |
| 2 | 1003 | 2025-04-01 | P123 | 6 |
+-------------+----------+------------+-----------+--------------+
##### Scenario
E-commerce platform wants to identify the top-buying customer(s) for every calendar day based on total quantity sold.
##### Question
Write an SQL query that, for each calendar day, returns the Customer_ID with the highest total Quantity_Sold and the corresponding total units. Return all customers in case of a tie.
##### Hint
Aggregate by day and customer, then use RANK() or DENSE_RANK() over daily totals to select rank = 1.
Overview: This question evaluates a candidate's competency in SQL data manipulation, including aggregation over calendar dates, grouping by customer, and handling ties when determining top buyers.
Top Customers Per Day
For each Order_Day, return the Customer_ID(s) with the highest total Quantity_Sold across all products and the corresponding total units. Return all customers in case of a tie.
Tables
ORDERS(Customer_ID INTEGER, Order_ID INTEGER, Order_Day DATE, Product_ID VARCHAR, Quantity_Sold INTEGER)
Hints
- Aggregate by Order_Day and Customer_ID first.
- Use DENSE_RANK over each day ordered by total quantity descending.
Top PRIME Customers Daily
For each Order_Day, among customers with an active PRIME membership on that date (Start_Date <= Order_Day <= End_Date), return the Customer_ID(s) with the highest total Quantity_Sold and their totals. Include ties.
Tables
PRIME(Customer_ID INTEGER, Start_Date DATE, End_Date DATE)
ORDERS(Customer_ID INTEGER, Order_ID INTEGER, Order_Day DATE, Product_ID VARCHAR, Quantity_Sold INTEGER)
Hints
- Join ORDERS to PRIME and filter by the date range where the PRIME membership is active.
- Rank totals per day with DENSE_RANK to include ties.
Daily Totals Per Customer
For each Order_Day and Customer_ID, return the total Quantity_Sold across all products. Only include customers with orders on that day.
Tables
ORDERS(Customer_ID INTEGER, Order_ID INTEGER, Order_Day DATE, Product_ID VARCHAR, Quantity_Sold INTEGER)
Hints
- Aggregate with GROUP BY on Order_Day and Customer_ID.