Katkat
Back

Compute 7-day rolling complaint/order ratio in SQL

TikTokData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.