SQLBeginnerBroken SQL FixFree

LEFT JOIN Turned Into INNER JOIN by WHERE Filter

Marketing is preparing a reactivation campaign and needs a customer list with the latest Spring campaign click when one exists. The exported audience is missing active customers who never clicked.

Scenario context

The query uses a LEFT JOIN, but a filter on the campaign table is placed in the WHERE clause. That removes NULL right-side rows and silently turns the result into an inner join for this campaign.

Business requirement

Return every active customer. For customers who clicked campaign SPRING_26, show their latest click timestamp. For customers with no click, keep the customer row and return NULL for last_click_at.

Sample production data

Use these small tables to reason about the bug before writing the fix. This data is used for the visible sample check when you click Run.

customers

customer_id	customer_name	is_active
1	Asha	1
2	Ben	1
3	Chen	1
4	Diya	0

campaign_clicks

customer_id	campaign_id	clicked_at
1	SPRING_26	2026-05-01 09:00:00
1	SPRING_26	2026-05-03 10:30:00
2	WINTER_25	2026-01-11 08:00:00

Schema

customers(customer_id, customer_name, is_active)
campaign_clicks(customer_id, campaign_id, clicked_at)

Broken logic / code

SELECT c.customer_id, c.customer_name, MAX(cc.clicked_at) AS last_click_at
FROM customers c
LEFT JOIN campaign_clicks cc ON c.customer_id = cc.customer_id
WHERE c.is_active = TRUE
  AND cc.campaign_id = 'SPRING_26'
GROUP BY 1, 2;

Expected output / expected logic

customer_id | customer_name | last_click_at
1 | Asha | 2026-05-03 10:30:00
2 | Ben | NULL
3 | Chen | NULL

Your attempt

Write the corrected SQL

Write the corrected query, run it against the sample tables, and compare the result with the expected output.

Saved

Interview-style explanation

Now explain your solution as if you are in an interview: symptom, root cause, fix, edge cases, trade-offs, monitoring, and prevention.