Coding questions · TikTok
Compute 7-day rolling complaint/order ratio in SQL
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.
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.
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 windowcomplaints_7d— the number of'complaint'events in that 7-day windowratio_7d—complaints_7d / orders_7d, rounded to 4 decimal places. Iforders_7d = 0, returnNULLforratio_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')))
Table sellers
| column | type | constraints |
|---|---|---|
| seller_id | INT | PRIMARY KEY, NOT NULL |
| country | TEXT | NOT NULL |
Sample data:
| country | seller_id |
|---|---|
| US | 101 |
| CN | 102 |
| US | 103 |
Table events
| column | type | constraints |
|---|---|---|
| event_time | TIMESTAMP | NOT NULL |
| seller_id | INT | NOT NULL, REFERENCES sellers(seller_id) |
| event_type | TEXT | NOT NULL, CHECK (event_type IN ('order','complaint')) |
Sample data:
| event_time | event_type | seller_id |
|---|---|---|
| 2025-05-26 10:00:00 | order | 101 |
| 2025-05-26 12:00:00 | complaint | 101 |
| 2025-05-27 09:00:00 | order | 102 |
| 2025-05-27 11:30:00 | order | 101 |
| 2025-05-28 14:00:00 | order | 103 |
| 2025-05-29 15:00:00 | complaint | 101 |
| 2025-05-30 08:00:00 | order | 102 |
| 2025-05-30 18:20:00 | complaint | 102 |
| 2025-05-31 10:00:00 | order | 101 |
| 2025-05-31 20:00:00 | complaint | 103 |
| 2025-06-01 07:00:00 | complaint | 101 |
| 2025-06-01 09:15:00 | order | 103 |
Expected output
One row per (date, seller) for dt from 2025-05-26 to 2025-06-01 inclusive (21 rows for 7 dates x 3 sellers), with trailing 7-day order/complaint counts and their ratio (NULL when orders_7d = 0), ordered by dt then seller_id.
| complaints_7d | dt | orders_7d | ratio_7d | seller_id |
|---|---|---|---|---|
| 1 | 2025-05-26T00:00:00+00:00 | 1 | 1.0000 | 101 |
| 0 | 2025-05-26T00:00:00+00:00 | 0 | NULL | 102 |
| 0 | 2025-05-26T00:00:00+00:00 | 0 | NULL | 103 |
| 1 | 2025-05-27T00:00:00+00:00 | 2 | 0.5000 | 101 |
| 0 | 2025-05-27T00:00:00+00:00 | 1 | 0.0000 | 102 |
| 0 | 2025-05-27T00:00:00+00:00 | 0 | NULL | 103 |
| 1 | 2025-05-28T00:00:00+00:00 | 2 | 0.5000 | 101 |
| 0 | 2025-05-28T00:00:00+00:00 | 1 | 0.0000 | 102 |
| 0 | 2025-05-28T00:00:00+00:00 | 1 | 0.0000 | 103 |
| 2 | 2025-05-29T00:00:00+00:00 | 2 | 1.0000 | 101 |
| 0 | 2025-05-29T00:00:00+00:00 | 1 | 0.0000 | 102 |
| 0 | 2025-05-29T00:00:00+00:00 | 1 | 0.0000 | 103 |
| 2 | 2025-05-30T00:00:00+00:00 | 2 | 1.0000 | 101 |
| 1 | 2025-05-30T00:00:00+00:00 | 2 | 0.5000 | 102 |
| 0 | 2025-05-30T00:00:00+00:00 | 1 | 0.0000 | 103 |
| 2 | 2025-05-31T00:00:00+00:00 | 3 | 0.6667 | 101 |
| 1 | 2025-05-31T00:00:00+00:00 | 2 | 0.5000 | 102 |
| 1 | 2025-05-31T00:00:00+00:00 | 1 | 1.0000 | 103 |
| 3 | 2025-06-01T00:00:00+00:00 | 3 | 1.0000 | 101 |
| 1 | 2025-06-01T00:00:00+00:00 | 2 | 0.5000 | 102 |
| 1 | 2025-06-01T00:00:00+00:00 | 2 | 0.5000 | 103 |
Hints
- Build a dense grid first:
generate_seriesover the date range CROSS JOINsellers, then LEFT JOIN your per-day counts andCOALESCEmissing 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 ROWordered by the date column (a calendar-aware RANGE, not a ROWS frame). - Cast the counts to
::numericbefore dividing so you get decimals instead of integer division, and wrap the division in aCASE WHEN orders_7d = 0 THEN NULLto avoid divide-by-zero.
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).
Table sellers
| column | type | constraints |
|---|---|---|
| seller_id | INT | PRIMARY KEY, NOT NULL |
| country | TEXT | NOT NULL |
Sample data:
| country | seller_id |
|---|---|
| US | 101 |
| CN | 102 |
| US | 103 |
Table events
| column | type | constraints |
|---|---|---|
| event_time | TIMESTAMP | NOT NULL |
| seller_id | INT | NOT NULL, REFERENCES sellers(seller_id) |
| event_type | TEXT | NOT NULL, CHECK (event_type IN ('order','complaint')) |
Sample data:
| event_time | event_type | seller_id |
|---|---|---|
| 2025-05-26 10:00:00 | order | 101 |
| 2025-05-26 12:00:00 | complaint | 101 |
| 2025-05-27 09:00:00 | order | 102 |
| 2025-05-27 11:30:00 | order | 101 |
| 2025-05-28 14:00:00 | order | 103 |
| 2025-05-29 15:00:00 | complaint | 101 |
| 2025-05-30 08:00:00 | order | 102 |
| 2025-05-30 18:20:00 | complaint | 102 |
| 2025-05-31 10:00:00 | order | 101 |
| 2025-05-31 20:00:00 | complaint | 103 |
| 2025-06-01 07:00:00 | complaint | 101 |
| 2025-06-01 09:15:00 | order | 103 |
Expected output
A single row giving the total 7-day rolling orders and complaints across all sellers for dt = '2025-06-01', and their ratio.
| complaints_7d | dt | orders_7d | ratio_7d | seller_id |
|---|---|---|---|---|
| 5 | 2025-06-01 | 7 | 0.7143 | ALL |
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.