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.
Quick Answer: 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.