TikTok·Data Scientist·Technical Phone Screen·Senior

Senior

Jul 2026

Summary

TikTok data scientist interview, technical phone screen or onsite SQL round. One meaty query question that looked straightforward until I started counting all the edge cases they wanted handled.

Questions Asked (1)

Using ad delivery logs, write a SQL query to find advertisers whose CTR dropped by at least 20% in the most recent 7-day window compared to the prior 7-day window. Exclude flagged suspicious ads, enforce a minimum impression threshold of 10,000 per window per advertiser, handle missing days as zero, guard against division-by-zero and integer division, and return advertiser_id, both CTRs, and the relative drop ordered by drop descending.

Product Analytics & MetricsData ModelingRoot Cause Analysis

Author's notes

This one had way more moving parts than it looked.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a CTE to aggregate daily impressions and clicks per advertiser, filtering out flagged ads, then compute 7-day sums for the current and prior windows using date ranges relative to the latest date in the data. Join the two windows, enforce the 10k impression threshold on both, compute CTRs with proper casting to avoid integer division, and calculate the relative drop, ordering by drop descending.

Pro tip: Explicitly state your assumptions about the date windows (e.g., whether the most recent 7 days are based on the max date in the data or the current date) and how you handle missing days—this shows you think about data completeness and edge cases.

1. Filter and aggregate daily metrics

Filter out flagged suspicious ads and aggregate impressions and clicks per advertiser per day. Ensure missing days are treated as zero by using a left join with a date spine or by summing over a date range.

2. Compute 7-day window sums

Use conditional aggregation or separate CTEs to calculate total impressions and clicks for the most recent 7-day window and the prior 7-day window. Define the windows based on the latest date in the data.

3. Apply impression threshold and compute CTRs

Join the two window aggregates, filter to advertisers with at least 10,000 impressions in both windows, and compute CTR as clicks divided by impressions, casting to float to avoid integer division.

4. Calculate relative drop and order results

Compute the relative drop as (prior_ctr - current_ctr) / prior_ctr, guarding against division by zero (e.g., using NULLIF). Filter to drops >= 0.20 and order by drop descending.

Key Points to Mention

Handling missing days as zero by using a date spine or calendar table to ensure accurate window sums.
Excluding flagged suspicious ads early in the query to avoid skewing metrics.
Enforcing the minimum impression threshold of 10,000 per window per advertiser to ensure statistical significance.
Avoiding integer division by casting clicks or impressions to float before division.
Guarding against division-by-zero when computing CTR and relative drop using NULLIF or CASE statements.
Defining the most recent 7-day window relative to the maximum date in the data, not the current date, to handle data latency.

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