TikTok·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

TikTok data scientist interview, SQL-heavy technical screen. One big multi-part query problem that took up basically the whole session. Not a vibe check at all, just pure SQL under pressure.

Questions Asked (1)

Given a sellers table and an events table with order and complaint records, write a single Postgres query (no procedural code) to compute a 7-day rolling complaint-to-order ratio per seller per day. The window for each date d is [d-6, d] inclusive, events are bucketed by date, and if the order count in the window is zero the ratio should be NULL. Also produce a second result row aggregating across all sellers for a specific reference date, with seller_id shown as 'ALL'.

Product Analytics & MetricsData Modeling

Author's notes

This wrecked me a little.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the schema and assumptions (e.g., event types, date bucketing, seller_id consistency). Then outline a SQL strategy: aggregate daily orders and complaints per seller, use a window function with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW to compute rolling sums, and handle division by zero with NULLIF. Finally, address the 'ALL' row by either using GROUPING SETS or a UNION with a separate aggregate query.

Pro tip: Mention that you would validate the rolling window logic with a small test dataset, especially around boundary dates and zero-order windows, to ensure NULLs are correctly produced and the 'ALL' row aggregates properly.

1. Clarify requirements and schema

Ask about the events table structure (e.g., event_type values, date column granularity) and confirm that seller_id is consistent across both tables. Confirm that the rolling window is based on calendar days and that missing dates should be treated as zero events.

2. Aggregate daily counts per seller

Write a CTE that groups events by seller_id and date, counting orders and complaints separately. Ensure that dates with no events are included (e.g., by generating a date spine or using a LEFT JOIN from a calendar table) so that rolling windows are correct.

3. Compute rolling sums and ratio

Use window functions SUM() OVER (PARTITION BY seller_id ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for both orders and complaints. Then compute the ratio as complaints / NULLIF(orders, 0) to yield NULL when orders = 0.

4. Produce the 'ALL' row

For a specific reference date, compute the aggregate ratio across all sellers. This can be done with a separate query that sums orders and complaints over the same 7-day window for all sellers, then UNION ALL with the per-seller results, labeling seller_id as 'ALL'.

5. Finalize and validate

Combine the per-seller and 'ALL' results, order the output, and mentally test edge cases (e.g., zero orders, missing dates). Mention that you would verify the query on a sample dataset.

Key Points to Mention

Use of window functions with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for rolling 7-day sums.
Handling division by zero with NULLIF to produce NULL when order count is zero.
Importance of a date spine or calendar table to ensure all dates are represented, avoiding gaps in rolling windows.
Using GROUPING SETS or UNION ALL to generate the 'ALL' aggregate row.
Performance considerations: indexing on (seller_id, date) and filtering to relevant date ranges.
Assumption that events are bucketed by date (e.g., using DATE_TRUNC or casting timestamp to date).

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.