🔥 Someone is Leaving. You Just Don't Know Who Yet.
It is Monday morning at Uber's Rider Retention lab. The weekly churn report is due in one hour. Your manager slides into your seat and whispers three words that will haunt you: 'Find the churners.'
A churner, in this context, is any customer who placed at least one order in 2023 — but placed zero orders in 2024. They were loyal. Then something broke. And now they are gone.
Your task: Identify customers who ordered in 2023 but NOT in 2024. Return their customer_id, name, and the number of orders they placed in 2023.
This is a medium-difficulty trap. The naive approach uses a subquery with NOT IN — which catastrophically breaks if the subquery returns any NULLs. The elegant approach uses NOT EXISTS or a LEFT JOIN anti-pattern.
customers Table:
| Column Name | Type |
|---|---|
| customer_id | integer |
| name | text |
| text |
orders Table:
| Column Name | Type |
|---|---|
| order_id | integer |
| customer_id | integer |
| amount | integer |
| order_date | text |
Expected Outcome (Illustrative Sample):
| customer_id | name | orders_in_2023 |
|---|---|---|
| 2 | James Bold | 3 |
The clock is ticking. Find them before they are gone forever.