Compute 7-day rolling complaint/order ratio in SQL
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
SQL only. Given the schema and sample data below, write a single Postgres query (no procedural code) to compute a 7-day rolling complaint-to-order ratio per seller per day. Use "today" = 2025-09-01, so the 7-day window for each date d is [d-6, d], inclusive. Count events by date using event_time::date. If the denominator (orders) in the window is 0, return NULL for the ratio. Also return an overall ratio (aggregated across sellers) for 2025-09-01. Output columns for part (a): dt, seller_id, orders_7d, complaints_7d, ratio_7d (decimal with 4 decimals). Output columns for part (b): dt='2025-09-01', seller_id='ALL', orders_7d, complaints_7d, ratio_7d.
Schema:
- sellers(seller_id INT PRIMARY KEY, country TEXT)
- events(event_time TIMESTAMP, seller_id INT REFERENCES sellers, event_type TEXT CHECK (event_type IN ('order','complaint')))
Sample tables (minimal):
sellers
+----------+---------+
| seller_id| country |
+----------+---------+
| 101 | US |
| 102 | CN |
| 103 | US |
+----------+---------+
events
+---------------------+-----------+------------+
| event_time | seller_id | event_type |
+---------------------+-----------+------------+
| 2025-08-26 10:00:00 | 101 | order |
| 2025-08-26 12:00:00 | 101 | complaint |
| 2025-08-27 09:00:00 | 102 | order |
| 2025-08-27 11:30:00 | 101 | order |
| 2025-08-28 14:00:00 | 103 | order |
| 2025-08-29 15:00:00 | 101 | complaint |
| 2025-08-30 08:00:00 | 102 | order |
| 2025-08-30 18:20:00 | 102 | complaint |
| 2025-08-31 10:00:00 | 101 | order |
| 2025-08-31 20:00:00 | 103 | complaint |
| 2025-09-01 07:00:00 | 101 | complaint |
| 2025-09-01 09:15:00 | 103 | order |
+---------------------+-----------+------------+
Requirements/hints:
- Generate all dates from 2025-08-26 to 2025-09-01 and join so that sellers with no events still emit rows.
- Use window functions over date ranges (not ROWS BETWEEN) so days with no events are still included.
- Avoid double-counting events; ensure event_type filters are correct.
- Be careful with integer division; cast to numeric for the ratio.
Quick Answer: This question evaluates SQL data manipulation skills, specifically time-based aggregations, sliding window functions, joins to include entities with no events, and numeric casting within the Data Manipulation (SQL/Python) domain.