Back to problems

Compute CTR drop with exclusions

SQL · ByteDance · Medium

Given the current date $$2025-10-01$$, analyze daily ad-delivery records to identify advertisers whose click-through rate declined by at least $$20\%$$ from one seven-day period to the next. Recent window: $$2025-09-24$$ through $$2025-09-30$$, inclusive. Baseline window: $$2025-09-17$$ through $$2025-09-23$$, inclusive. Before computing totals, discard every row whose ad_id is present in suspicious_ads. A calendar day with no rows contributes zero impressions and zero…

Checking your access…