Design SQL/Pandas aggregations on retail schema

Quick Overview

This question evaluates ability to perform time-based aggregations and data manipulation using SQL and pandas, including per-order revenue calculation, same-day de-duplication, rolling-window sums, and intra-customer ranking.

Company: Amazon

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

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.

Overview: This question evaluates ability to perform time-based aggregations and data manipulation using SQL and pandas, including per-order revenue calculation, same-day de-duplication, rolling-window sums, and intra-customer ranking.

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.

Tables

customers(cust_id VARCHAR(10), name VARCHAR(100), signup_date DATE)

orders(order_id INT, cust_id VARCHAR(10), order_date DATE, status VARCHAR(20))

order_items(order_id INT, product_id VARCHAR(10), qty INT, price DECIMAL(10,2))

products(product_id VARCHAR(10), category VARCHAR(10))

Hints

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.
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.