Write SQL for profit, growth, retention
Overview
This question evaluates a candidate's ability to perform advanced SQL data manipulation — specifically rolling-window aggregations, revenue and gross profit computations that account for refunds, order status and test flags, plus cohort retention and week-over-week growth analysis.
Write ANSI-SQL (PostgreSQL preferred) for the tasks below. Assume all timestamps are UTC and that "today" = 2025-09-01. Schema:
- products(product_id INT, name TEXT, category TEXT)
- users(user_id INT, signup_date DATE, country TEXT)
- orders(order_id INT, user_id INT, order_ts TIMESTAMP, status TEXT, is_test BOOLEAN)
- order_items(order_id INT, product_id INT, qty INT, unit_price_cents INT, discount_cents INT, cogs_cents INT)
- refunds(refund_id INT, order_id INT, product_id INT, qty_refunded INT, refund_cents INT, refund_ts TIMESTAMP)
- events(user_id INT, event_ts TIMESTAMP, event_name TEXT) Small sample data (for clarity only): products product_id | name | category 1 | Pod Mini | Speaker 2 | Beans 1lb | Grocery 3 | Mug | Merch 4 | Pod Max | Speaker
users user_id | signup_date | country 101 | 2025-08-20 | US 102 | 2025-08-25 | US 103 | 2025-08-28 | CA 104 | 2025-08-30 | US
orders order_id | user_id | order_ts | status | is_test 1001 | 101 | 2025-08-26 10:00:00 | placed | false 1002 | 101 | 2025-08-27 12:00:00 | shipped | false 1003 | 102 | 2025-08-27 15:00:00 | cancelled | false 1004 | 103 | 2025-08-30 09:00:00 | shipped | false 1005 | 104 | 2025-08-31 20:00:00 | shipped | true
order_items order_id | product_id | qty | unit_price_cents | discount_cents | cogs_cents 1001 | 1 | 1 | 4999 | 0 | 3000 1002 | 2 | 2 | 1299 | 100 | 600 1002 | 3 | 1 | 999 | 0 | 400 1004 | 4 | 1 | 8999 | 0 | 5500 1005 | 2 | 1 | 1299 | 0 | 600
refunds refund_id | order_id | product_id | qty_refunded | refund_cents | refund_ts 1 | 1002 | 2 | 1 | 1299 | 2025-09-01 11:00:00 2 | 1004 | 4 | 1 | 8999 | 2025-09-02 10:00:00
events user_id | event_ts | event_name 101 | 2025-08-26 09:00:00 | app_open 101 | 2025-08-27 10:00:00 | purchase 102 | 2025-08-26 08:00:00 | app_open 102 | 2025-08-27 08:30:00 | app_open 103 | 2025-08-30 09:15:00 | app_open 104 | 2025-08-31 21:00:00 | app_open 104 | 2025-09-01 21:05:00 | app_open Tasks:
- For each calendar day d in [2025-08-26, 2025-09-01], compute the 7-day rolling gross profit by category bucket ('Speaker' vs 'Other'). Daily gross profit uses orders where order_date = d and status not in ('cancelled') and is_test = false: sum over items of qty*(unit_price_cents - discount_cents - cogs_cents). Treat refunds as negative profit on refund_ts date (reduce revenue and associated COGS proportionally to refunded qty). Output: d, category_bucket, rolling_7d_gross_profit_cents.
- Among non-test, non-cancelled orders, find the top 2 products by week-over-week revenue growth for week [2025-08-26..2025-09-01] versus the prior 7 days; treat refunds as negative revenue on refund_ts. Output: product_id, name, wow_growth_pct.
- For users whose first event_date is between 2025-08-26 and 2025-08-31 inclusive, compute D1 retention (had any event exactly one day later) by country. Output: country, cohort_start_date, d1_retention_rate. Use window functions and calendar tables; do not assume dense dates.