Interview Question Bank · NOT EXISTS vs NOT IN with NULLs · 7-day window
10. Spend with zero conversions in the last 7 days
HardMarketingQuestion #178
Concept: NOT EXISTS vs NOT IN with NULLs · 7-day windowshow
To find rows with no match in another table, NOT EXISTS is the safe tool. NOT IN (subquery) returns nothing at all if the subquery yields a single NULL, because x NOT IN (1, NULL) is unknown, never true. For 'the last 7 days' including the report date, that's the date minus 6 through the date; on timestamps, end with < the next day so the evening isn't cut off.
Example (different data):
SELECT d.name
FROM drivers d
WHERE NOT EXISTS (
SELECT 1 FROM deliveries x
WHERE x.driver_id = d.id
AND x.delivered_at >= TIMESTAMP '2025-05-25'
AND x.delivered_at < TIMESTAMP '2025-06-01'
);Your task
Larkspur Roasters' weekly review on 2025-03-14 flags money spent without results. Conversions with no campaign_id are organic orders, and the data has since been loaded past the report date.
For the 7 days ending 2025-03-14 (2025-03-08 through 2025-03-14), find campaigns that spent more than 0 but had no conversions in that window. Return name, channel, spend_last_7d and last_conversion_date (the date of the campaign's latest conversion before 2025-03-15, NULL if none). Sort by spend_last_7d descending, then name.
Starting Postgres in your browser…
Checking your access…