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.
Overview: 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.
Read the full TikTok Data Scientist interview experience this question came from
7-day rolling complaint-to-order ratio per seller per day
**SQL only (PostgreSQL).** Using the schema and sample data below, write a **single** PostgreSQL query (no procedural code) that computes a **7-day rolling complaint-to-order ratio per seller per day**.
### Task
Consider the date range **2025-05-26 to 2025-06-01 (inclusive)**. For each date `dt` in this range, the 7-day window is every event whose `event_time::date` falls between `dt - 6 days` and `dt`, inclusive. Bucket events to a day using `event_time::date`.
For each `(dt, seller_id)` pair, compute:
- `orders_7d` — the number of `'order'` events in that 7-day window
- `complaints_7d` — the number of `'complaint'` events in that 7-day window
- `ratio_7d` — `complaints_7d / orders_7d`, rounded to **4 decimal places**. If `orders_7d = 0`, return `NULL` for `ratio_7d` (do not divide by zero).
Every seller must appear for every date in the range, even if it has no events in the window — generate the full date series from `2025-05-26` to `2025-06-01` and cross-join it with all sellers, so sellers with no events still emit rows (with zero counts and a `NULL` ratio).
### Required output
Return exactly these columns, one row per `(dt, seller_id)`:
| column | meaning |
|---|---|
| `dt` | the calendar date in the range |
| `seller_id` | the seller |
| `orders_7d` | count of `'order'` events in the trailing 7-day window ending at `dt` |
| `complaints_7d` | count of `'complaint'` events in the trailing 7-day window ending at `dt` |
| `ratio_7d` | `complaints_7d / orders_7d` rounded to 4 decimals, or `NULL` when `orders_7d = 0` |
**Sort the result by `dt` ascending, then `seller_id` ascending.**
### 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')))`
Tables
sellers(seller_id INT, country TEXT)
events(event_time TIMESTAMP, seller_id INT, event_type TEXT)
Hints
- Build a dense grid first: `generate_series` over the date range CROSS JOIN `sellers`, then LEFT JOIN your per-day counts and `COALESCE` missing counts to 0 — this is what lets every seller appear on every day and makes the rolling window gap-safe.
- For the trailing 7-day total, use a window frame `RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW` ordered by the date column (a calendar-aware RANGE, not a ROWS frame).
Overall 7-day rolling complaint/order ratio across all sellers for a specific date
Using the same schema and date logic as in Question 1, write a PostgreSQL query that returns a single row with the overall 7-day rolling complaint-to-order ratio aggregated across all sellers for dt = '2025-06-01'.
The 7-day window for dt = '2025-06-01' is all events where event_time::date is between '2025-05-26' and '2025-06-01', inclusive.
Output columns:
- dt (should be '2025-06-01')
- seller_id (use the literal string 'ALL')
- orders_7d (total orders across all sellers in the window)
- complaints_7d (total complaints across all sellers in the window)
- ratio_7d = complaints_7d / orders_7d as a decimal with 4 decimal places (NULL if orders_7d = 0).
Tables
sellers(seller_id INT, country TEXT)
events(event_time TIMESTAMP, seller_id INT, event_type TEXT)
Hints
- You can reuse the rolling 7-day per-seller metrics from Question 1 and then aggregate them for dt = '2025-06-01'.
- Alternatively, directly sum orders and complaints across all sellers over the date range '2025-05-26' to '2025-06-01' and compute the ratio with numeric division.