Interview Question Bank · NOT EXISTS · NOT IN and NULL · correlated subquery
2. Advertisers with no campaign in Q2
MediumSearch & adsQuestion #257
Concept: NOT EXISTS · NOT IN and NULL · correlated subqueryshow
'Rows with no match' is an anti-join, and NOT EXISTS is the safe way to write it: for each outer row it asks whether any matching row exists. NOT IN looks similar but breaks when the subquery returns a NULL, because x NOT IN (1, NULL) is NULL, never true, so every row is filtered out. A correlated subquery, one that refers to the outer row, can also fetch a per-row value such as a latest date without a GROUP BY.
Example (different data):
SELECT d.name, (SELECT MAX(v.visited_on) FROM vet_visits v WHERE v.dog_id = d.id) AS last_visit FROM dogs d WHERE NOT EXISTS ( SELECT 1 FROM vet_visits v WHERE v.dog_id = d.id AND v.visited_on >= DATE '2025-01-01' );
Your task
Findwell's ad sales team wants to call advertisers that went quiet: those that started no campaign in Q2 2025. Findwell also runs house campaigns promoting itself, and those have no advertiser_id.
For every advertiser that started no campaign in Q2 2025 (2025-04-01 to 2025-06-30, both included), return name and last_campaign_on (the start_date of its most recent campaign of any date, NULL if it never ran one). Sort by name.
Starting Postgres in your browser…
Checking your access…