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.