Compute 7-day rolling complaint/order ratio in SQL

Q: Compute 7-day rolling complaint/order ratio in SQL

This is a Data Manipulation (SQL/Python) interview question from TikTok for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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.

Compute 7-day rolling complaint/order ratio in SQL

Comments (0)