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. The browser checker seeds this data when you click Check Answer.
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
All active customers should appear. Customers without SPRING_26 clicks should have last_click_at = NULL.Your attempt
Write the corrected SQL
Write the corrected query. The browser will run it against the sample tables and compare the result with the expected output.
Saved locally
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.