Compute CTR drop with exclusions
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Assume today = 2025-09-01. Using ad delivery logs, find advertisers whose CTR in the last 7 days (2025-08-25 to 2025-08-31) dropped by at least 20% relative to the previous 7 days (2025-08-18 to 2025-08-24), after excluding suspicious ads. Return advertiser_id, last7_ctr, prev7_ctr, rel_drop (as a positive fraction), ordered by rel_drop DESC. Enforce that both windows have >= 10,000 impressions per advertiser after exclusions. Treat missing days as 0, guard against division-by-zero and integer division. Schema and small samples:
Table: advertisers(advertiser_id INT, name TEXT, vertical TEXT)
+---------------+----------+-----------+
| advertiser_id | name | vertical |
+---------------+----------+-----------+
| 101 | Acme Co | Gaming |
| 102 | Beta LLC | Ecommerce |
+---------------+----------+-----------+
Table: ad_events(date DATE, advertiser_id INT, ad_id INT, impressions INT, clicks INT, spend_cents INT)
+------------+---------------+-------+-------------+--------+-------------+
| date | advertiser_id | ad_id | impressions | clicks | spend_cents |
+------------+---------------+-------+-------------+--------+-------------+
| 2025-08-19 | 101 | 9001 | 5000 | 250 | 120000 |
| 2025-08-20 | 101 | 9001 | 5500 | 220 | 118000 |
| 2025-08-23 | 101 | 9001 | 4500 | 200 | 100000 |
| 2025-08-26 | 101 | 9001 | 7000 | 210 | 130000 |
| 2025-08-27 | 101 | 9002 | 5000 | 50 | 40000 |
| 2025-08-22 | 102 | 9100 | 14000 | 700 | 280000 |
| 2025-08-29 | 102 | 9100 | 15000 | 600 | 300000 |
| 2025-08-30 | 102 | 9101 | 8000 | 240 | 160000 |
+------------+---------------+-------+-------------+--------+-------------+
Table: suspicious_ads(ad_id INT)
+-------+
| ad_id |
+-------+
| 9002 |
+-------+
Write a single SQL query (ANSI SQL preferred) that produces the required output.
Overview: This question evaluates a candidate's ability to compute time-windowed click-through rates using SQL aggregations, joins, filters and data-cleaning (excluding suspicious ads) while handling edge cases such as missing days, minimum-impression thresholds, division-by-zero, and integer-division pitfalls.
Read the full TikTok Data Scientist interview experience this question came from
You are given daily ad delivery logs and a list of suspicious ads to exclude.
Compute each advertiser's CTR (clicks / impressions) for:
- Last 7 days: 2025-08-25 to 2025-08-31 (inclusive)
- Previous 7 days: 2025-08-18 to 2025-08-24 (inclusive)
Exclude any rows whose ad_id appears in suspicious_ads before calculating impressions/clicks.
Return advertisers whose CTR in the last 7 days dropped by at least 20% relative to the previous 7 days. Define rel_drop as a positive fraction:
rel_drop = (prev7_ctr - last7_ctr) / prev7_ctr
Requirements:
- Return columns: advertiser_id, last7_ctr, prev7_ctr, rel_drop
- Enforce that BOTH windows have at least 10,000 impressions per advertiser AFTER exclusions
- Treat missing days as 0 (i.e., if there are no rows for a day, that day contributes 0 impressions/clicks)
- Guard against division-by-zero and integer division
- Order by rel_drop DESC
Write a single ANSI-SQL query.
Tables
advertisers(advertiser_id INT, name VARCHAR(100), vertical VARCHAR(50))
ad_events(date DATE, advertiser_id INT, ad_id INT, impressions INT, clicks INT, spend_cents INT)
suspicious_ads(ad_id INT)
Hints
- Filter out suspicious ads first (e.g., LEFT JOIN suspicious_ads and keep only NULL matches).
- Use conditional aggregation (SUM(CASE WHEN date BETWEEN ... THEN ... END)) to compute window totals in one pass.