Katkat
Back

Identify Top-Buying Customers Daily with SQL Query

AmazonBusiness Intelligence Engineer · Onsite
MediumBusiness Intelligence EngineerOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.