codejump
HardPro challengeSQL

Find Churned Customers with a CTE

SQLDatabases

Write a SQL query using a CTE (Common Table Expression) to find customers
who placed at least one order in 2023 but no orders in 2024 — i.e. they
churned. Return their names ordered alphabetically.

Table: `orders`

idcustomerorder_date
1Alice2023-03-10
2Bob2023-07-22
3Alice2024-01-15
4Carol2023-11-05
5Dave2023-06-30
6Dave2024-03-20

Alice and Dave ordered in 2024 → retained. Bob and Carol did not → churned.

Expected output (column: customer)

Bob
Carol

Hint — define two CTEs (active_2023, active_2024), then LEFT JOIN

and filter on IS NULL.

Sample tests

Test #1Bob and Carol ordered in 2023 only
Input: "CREATE TABLE orders (id INTEGER, customer TEXT, order_date TEXT);\nINSERT INTO orders VALUES\n (1, 'Alice', '2023-03-10'),\n (2, 'Bob', '2023-07-22'),\n (3, 'Alice', '2024-01-15'),\n (4, 'Carol', '2023-11-05'),\n (5, 'Dave', '2023-06-30'),\n (6, 'Dave', '2024-03-20');"
Output: "Bob\nCarol"
← All challenges