Drill 2 of 3 in Search & ads questions
0

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…