Identify Top-Buying Customers Daily with SQL Query

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.

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.

Table ORDERS

column	type	constraints
Customer_ID	INTEGER	NOT NULL
Order_ID	INTEGER	PRIMARY KEY, NOT NULL
Order_Day	DATE	NOT NULL
Product_ID	VARCHAR	NOT NULL
Quantity_Sold	INTEGER	NOT NULL

Sample data:

Customer_ID	Order_Day	Order_ID	Product_ID	Quantity_Sold
1	2025-04-01	1001	P123	3
1	2025-04-01	1002	P456	2
2	2025-04-01	1003	P123	6

Expected output

One row per Order_Day per top-buying customer. If multiple customers tie for the highest total units on a day, return all tied customers for that day.

Customer_ID	Order_Day	Total_Quantity_Sold
2	2025-04-01	6

Hints

1. Aggregate by Order_Day and Customer_ID first.
2. 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.

Table PRIME

column	type	constraints
Customer_ID	INTEGER	NOT NULL
Start_Date	DATE	NOT NULL
End_Date	DATE	NOT NULL

Sample data:

Customer_ID	End_Date	Start_Date
1	2025-12-31	2025-01-01
2	2025-10-15	2025-03-15

Table ORDERS

column	type	constraints
Customer_ID	INTEGER	NOT NULL
Order_ID	INTEGER	PRIMARY KEY, NOT NULL
Order_Day	DATE	NOT NULL
Product_ID	VARCHAR	NOT NULL
Quantity_Sold	INTEGER	NOT NULL

Sample data:

Customer_ID	Order_Day	Order_ID	Product_ID	Quantity_Sold
1	2025-04-01	1001	P123	3
1	2025-04-01	1002	P456	2
2	2025-04-01	1003	P123	6

Expected output

One row per Order_Day per top PRIME customer. If multiple PRIME customers tie for the highest total units on a day, return all tied PRIME customers for that day.

Customer_ID	Order_Day	Total_Quantity_Sold
2	2025-04-01	6

Hints

1. Join ORDERS to PRIME and filter by the date range where the PRIME membership is active.
2. 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.

Table ORDERS

column	type	constraints
Customer_ID	INTEGER	NOT NULL
Order_ID	INTEGER	PRIMARY KEY, NOT NULL
Order_Day	DATE	NOT NULL
Product_ID	VARCHAR	NOT NULL
Quantity_Sold	INTEGER	NOT NULL

Sample data:

Customer_ID	Order_Day	Order_ID	Product_ID	Quantity_Sold
1	2025-04-01	1001	P123	3
1	2025-04-01	1002	P456	2
2	2025-04-01	1003	P123	6

Expected output

One row per day per customer with the sum of units sold that day.

Customer_ID	Order_Day	Total_Quantity_Sold
1	2025-04-01	5
2	2025-04-01	6

Hints

1. Aggregate with GROUP BY on Order_Day and Customer_ID.