Katkat
Back

Compute CTR drop with exclusions

TikTokData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.