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