The customers who vanished from the report
A monthly report lists every customer with how many orders they placed in 2026, and a customer who placed none must appear with 0.
The query in production uses a LEFT JOIN and the report is missing about a third of the customer base. Nobody has changed the join. Write the version that is right.
Example
- input
customers(id, name), orders(id, customer_id, placed_at, total)outputid, name, orders_2026Every customer appears exactly once, ordered by id, with a count that may be 0.
Constraints
- Every row of customers must appear, whatever orders contains.
- Only orders placed in 2026 are counted. Orders from other years count as 0, they do not remove the customer.
- One query. No temporary tables.
Hints
Hint 1
A LEFT JOIN keeps the left row and fills the right side with NULLs. Ask what a WHERE clause then does to those NULLs.
Hint 2
`WHERE o.placed_at >= '2026-01-01'` is false for NULL, so the row it was protecting is discarded — the LEFT JOIN has been turned into an INNER JOIN after the fact.
Hint 3
The date test belongs where it can run BEFORE the join decides what to keep.
Stuck? The lesson behind this problem: 🐘 Joins
sql
Tab indents · Escape first to tab out
Test cases
These are the specification. Run tests checks your answer against them.
| Case | Input | Expected |
|---|---|---|
| a customer with no orders at all | 2 customers, 1 order | both rows, counts 1 and 0 |
| orders only from last year | 2 customers, one 2025 order | the 2025 customer stays, with 0 |
| only 2026 is counted | 1 customer, orders across three years | count 2 |