Compute CTR drop with exclusions

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.

Compute advertiser CTR drop across two 7-day windows with suspicious-ad exclusions

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.

Table advertisers

column	type	constraints
advertiser_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
vertical	VARCHAR(50)	NOT NULL

Sample data:

advertiser_id	name	vertical
101	Acme Co	Gaming
102	Beta LLC	Ecommerce

Table ad_events

column	type	constraints
date	DATE	NOT NULL
advertiser_id	INT	NOT NULL
ad_id	INT	NOT NULL
impressions	INT	NOT NULL
clicks	INT	NOT NULL
spend_cents	INT	NOT NULL

Sample data:

ad_id	advertiser_id	clicks	date	impressions	spend_cents
9001	101	250	2025-08-19	5000	120000
9001	101	220	2025-08-20	5500	118000
9001	101	200	2025-08-23	4500	100000
9001	101	210	2025-08-26	7000	130000
9002	101	50	2025-08-27	5000	40000
9001	101	120	2025-08-31	4000	80000
9100	102	700	2025-08-22	14000	280000
9100	102	600	2025-08-29	15000	300000
9101	102	240	2025-08-30	8000	160000

Table suspicious_ads

column	type	constraints
ad_id	INT	PRIMARY KEY, NOT NULL

Sample data:

ad_id
9002

Expected output

One row per advertiser meeting the criteria, with CTRs computed over each 7-day window after excluding suspicious ads, and rel_drop >= 0.20. Ordered by largest relative drop first.

advertiser_id	last7_ctr	prev7_ctr	rel_drop
101	0.03	0.044667	0.328358
102	0.036522	0.05	0.269565

Hints

1. Filter out suspicious ads first (e.g., LEFT JOIN suspicious_ads and keep only NULL matches).
2. Use conditional aggregation (SUM(CASE WHEN date BETWEEN ... THEN ... END)) to compute window totals in one pass.
3. Cast to DECIMAL and use NULLIF to avoid integer division and division-by-zero.