Design SQL/Pandas aggregations on retail schema

Using the schema and sample data below, answer both parts. Assume today is 2025-09-01. Use standard SQL (e.g., PostgreSQL) and idiomatic pandas without Python for-loops over rows.

Schema and sample rows

customers
+---------+------+------------+
| cust_id | name | signup_date|
+---------+------+------------+
| C1 | Ada | 2025-08-28 |
| C2 | Ben | 2025-08-29 |
| C3 | Cy | 2025-08-25 |
+---------+------+------------+

orders
+----------+---------+------------+-----------+
| order_id | cust_id | order_date | status |
+----------+---------+------------+-----------+
| 101 | C1 | 2025-08-29 | completed |
| 102 | C1 | 2025-08-30 | completed |
| 107 | C1 | 2025-08-30 | completed |
| 103 | C1 | 2025-09-01 | returned |
| 104 | C2 | 2025-08-31 | completed |
| 105 | C2 | 2025-09-01 | completed |
| 106 | C3 | 2025-08-25 | cancelled |
+----------+---------+------------+-----------+

order_items
+----------+------------+-----+--------+
| order_id | product_id | qty | price |
+----------+------------+-----+--------+
| 101 | P1 | 1 | 10.00 |
| 101 | P2 | 2 | 5.00 |
| 102 | P1 | 1 | 10.00 |
| 107 | P2 | 1 | 5.00 |
| 104 | P3 | 1 | 20.00 |
| 105 | P3 | 2 | 20.00 |
+----------+------------+-----+--------+

products
+------------+----------+
| product_id | category |
+------------+----------+
| P1 | A |
| P2 | B |
| P3 | A |
+------------+----------+

Definitions

• Order revenue = sum(qty * price) over items for that order.
• Only orders with status = completed count toward revenue; returned or cancelled do not.
• If a customer has multiple completed orders on the same calendar day, keep only one order for that day: pick the order with the greater order revenue; if there is a tie on revenue, keep the higher order_id.

Part A (SQL) Write a single SQL query that returns, for each customer with at least two completed orders in the last 7 days (inclusive of today = 2025-09-01), one row per kept order (after the same-day de-duplication rule) with the following columns:

• cust_id
• order_id (after applying the same-day selection rule)
• order_date
• order_revenue
• rolling_3_day_rev: sum of the customer's order_revenue over the window [order_date - 2 days, order_date], considering only completed orders that survived the same-day selection rule
• rank_in_7d_by_revenue: dense rank of this kept order's revenue among the customer's kept orders in the last 7 days, highest revenue gets rank 1 Requirements: implement same-day selection without correlated subqueries; use window functions; do not use temporary tables.

Part B (pandas) Using pandas DataFrames with the same content, produce a DataFrame with one row per customer for the last 7 days (inclusive, relative to today = 2025-09-01) containing:

• cust_id
• total_7d_revenue
• top_category_7d: the product category with the highest revenue for that customer in the 7-day window (break ties by alphabetical order of category)
• top_category_share_7d: the fraction (0–1) of the customer's 7-day revenue attributable to top_category_7d Constraints: avoid Python loops; show how you enforce the same-day order selection rule prior to aggregation.

Rolling 3-Day Revenue with Same-Day Order De-duplication

You are given a simple retail schema with customers, orders, order_items, and products. Use the tables defined below.

Definitions:

• Order revenue = sum(qty * price) over items for that order.
• Only orders with status = 'completed' count toward revenue; returned or cancelled orders do not.
• Same-day de-duplication rule: if a customer has multiple completed orders on the same calendar day, keep only one order for that day: pick the order with the greater order revenue; if there is a tie on revenue, keep the higher order_id.

Consider only the 7-day window from 2025-05-26 to 2025-06-01 (inclusive), based on order_date.

Task: Write a single SQL query that returns, for each customer who has at least two completed orders in this 7-day window, one row per kept order (after applying the same-day de-duplication rule) with the following columns:

• cust_id
• order_id (after applying the same-day selection rule)
• order_date
• order_revenue
• rolling_3_day_rev: sum of the customer's order_revenue over the window [order_date - 2 days, order_date], considering only completed orders that survived the same-day selection rule
• rank_in_7d_by_revenue: dense rank of this kept order's revenue among the customer's kept orders in the 7-day window, highest revenue gets rank 1

Requirements:

• Implement same-day selection without correlated subqueries.
• Use window functions (e.g., ROW_NUMBER, DENSE_RANK, and a window frame for the 3-day rolling sum).
• Do not use temporary tables; use common table expressions (CTEs) or subqueries only.

Table customers

column	type	constraints
cust_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
signup_date	DATE	NOT NULL

Sample data:

cust_id	name	signup_date
C1	Ada	2025-05-28
C2	Ben	2025-05-29
C3	Cy	2025-05-25

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
cust_id	VARCHAR(10)	NOT NULL
order_date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

cust_id	order_date	order_id	status
C1	2025-05-29	101	completed
C1	2025-05-30	102	completed
C1	2025-05-30	107	completed
C1	2025-06-01	103	returned
C2	2025-05-31	104	completed
C2	2025-06-01	105	completed
C3	2025-05-25	106	cancelled

Table order_items

column	type	constraints
order_id	INT	NOT NULL
product_id	VARCHAR(10)	NOT NULL
qty	INT	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

order_id	price	product_id	qty
101	10	P1	1
101	5	P2	2
102	10	P1	1
107	5	P2	1
104	20	P3	1
105	20	P3	2

Table products

column	type	constraints
product_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
category	VARCHAR(10)	NOT NULL

Sample data:

category	product_id
A	P1
B	P2
A	P3

Expected output

For each customer who has at least two completed orders between 2025-05-26 and 2025-06-01, return one row per kept order (after same-day de-duplication) with its revenue, a 3-day rolling revenue sum over kept orders, and the dense rank of each kept order's revenue within the 7-day window for that customer.

cust_id	order_date	order_id	order_revenue	rank_in_7d_by_revenue	rolling_3_day_rev
C1	2025-05-29	101	20	1	20
C1	2025-05-30	102	10	2	30
C2	2025-05-31	104	20	2	20
C2	2025-06-01	105	40	1	60

Hints

1. First compute order_revenue for each completed order in the 7-day window, then use ROW_NUMBER over (cust_id, order_date) to keep a single order per customer per day.
2. After filtering to customers with at least two kept orders, use a RANGE frame on order_date for the 3-day rolling sum and DENSE_RANK to rank revenues per customer.

Top Revenue Category per Customer in 7-Day Window

Using the same retail schema and definitions as in Question 1, consider again only completed orders in the 7-day window from 2025-05-26 to 2025-06-01 (inclusive), based on order_date.

Definitions (reused):

• Order revenue = sum(qty * price) over items for that order.
• Only orders with status = 'completed' count toward revenue.
• Same-day de-duplication rule: for each customer and calendar day, if there are multiple completed orders, keep only one: the order with the greater order revenue; if tied, keep the higher order_id.

Task: Write a SQL query that produces one row per customer who has at least one completed order in this 7-day window, with the following columns:

• cust_id
• total_7d_revenue: total revenue for that customer from completed orders in the 7-day window, after applying the same-day de-duplication rule.
• top_category_7d: the product category with the highest revenue for that customer in the 7-day window (after same-day de-duplication). If there is a tie on revenue between categories, pick the category with the alphabetically smaller category code.
• top_category_share_7d: the fraction of the customer's 7-day revenue attributable to top_category_7d, as a decimal between 0 and 1 (e.g., 0.67). You may round to two decimal places.

Ensure that you enforce the same-day order selection rule before aggregating revenue by product category.

Table customers

column	type	constraints
cust_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
signup_date	DATE	NOT NULL

Sample data:

cust_id	name	signup_date
C1	Ada	2025-05-28
C2	Ben	2025-05-29
C3	Cy	2025-05-25

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
cust_id	VARCHAR(10)	NOT NULL
order_date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

cust_id	order_date	order_id	status
C1	2025-05-29	101	completed
C1	2025-05-30	102	completed
C1	2025-05-30	107	completed
C1	2025-06-01	103	returned
C2	2025-05-31	104	completed
C2	2025-06-01	105	completed
C3	2025-05-25	106	cancelled

Table order_items

column	type	constraints
order_id	INT	NOT NULL
product_id	VARCHAR(10)	NOT NULL
qty	INT	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

order_id	price	product_id	qty
101	10	P1	1
101	5	P2	2
102	10	P1	1
107	5	P2	1
104	20	P3	1
105	20	P3	2

Table products

column	type	constraints
product_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
category	VARCHAR(10)	NOT NULL

Sample data:

category	product_id
A	P1
B	P2
A	P3

Expected output

For each customer with at least one completed order between 2025-05-26 and 2025-06-01 (after applying same-day de-duplication), return their total 7-day revenue, the product category contributing the most revenue (breaking ties alphabetically), and that category's share of total revenue.

cust_id	top_category_7d	top_category_share_7d	total_7d_revenue
C1	A	0.67	30
C2	A	1	60

Hints

1. Apply the same-day de-duplication rule at the order level first, then restrict all downstream aggregations to the kept orders only.
2. Aggregate revenue by customer and category, then use ROW_NUMBER with an ORDER BY on revenue (and category for tie-breaking) to select the top category and compute its share of total revenue.