CRED mid CROSS JOINLEFT JOINWHEREORDER BY

For each user, find every reward category they have NOT successfully redeemed any points in this month — meaning either they have no redemption row for that category at all, or their only redemption row for that category has a NULL points_redeemed (a reversed attempt). Build the full user-times-category universe first, since a missed category has no row to start from in redemptions. Return user_name and category_name for every missed combination, sorted by user_name then category_name.

CRED · Missed Reward Categories — practice this real-world SQL scenario live in your browser.

📖 Story CRED · Missed Reward Categories

Tuesday morning at CRED's Rewards team. The rewards catalog has five spending categories — Travel, Dining, Shopping, Fuel, Entertainment — and the team wants to nudge each user toward categories they haven't touched this month, to boost engagement. redemptions only has a row when a user actually redeemed points in a category, so a category a user never engaged with simply has no row at all. One redemption is also sitting with a NULL points_redeemed — the redemption was initiated but reversed before it completed, so it shouldn't count as the user having actually engaged with that category.

🎯 Your Mission

📋 Table Structure

🗂 users

user_id INTEGER 1

user_name TEXT Aarav

🗂 categories

category_id INTEGER 1

category_name TEXT Travel

🗂 redemptions

redemption_id INTEGER 1

user_id INTEGER 1

category_id INTEGER 1

points_redeemed INTEGER 500

⚡ Step-by-Step Walkthrough

Build the full user-times-category universe with CROSS JOIN

query.sql

SELECT u.user_name, c.category_name
FROM users u
CROSS JOIN categories c
ORDER BY u.user_name, c.category_name;

💡 Explanation

CROSS JOIN pairs every row in users with every row in categories — no ON clause, no matching condition, just every possible combination. Five users times five categories produces exactly twenty-five rows.
This is the one join type where nothing needs to relate the two tables at all — users and categories share no common column, and CROSS JOIN doesn't need one, because it isn't matching anything, it's generating combinations.
Nobody has actually redeemed anything in Entertainment yet — it's the newest category — and Aarav in particular hasn't touched Fuel or Shopping either. This table isn't a record of what happened, it's every combination that could possibly appear in redemptions, whether it does or not.
redemptions itself will never contain a row for a category a user skipped entirely — this CROSS JOIN result is what makes it possible to notice that absence at all, the same way the recursive CTE date range made it possible to notice a missing day.

LEFT JOIN redemptions, but only count a successful one

query.sql

SELECT u.user_name, c.category_name, r.points_redeemed
FROM users u
CROSS JOIN categories c
LEFT JOIN redemptions r
  ON r.user_id = u.user_id AND r.category_id = c.category_id AND r.points_redeemed IS NOT NULL
ORDER BY u.user_name, c.category_name;

💡 Explanation

r.points_redeemed IS NOT NULL sits inside the ON clause, not a separate WHERE — that keeps it part of what counts as a match during the join itself, so a category with only a failed redemption still gets treated as unmatched rather than being filtered out after the fact.
Priya's Dining row does exist in redemptions, but because its points_redeemed is NULL, the join condition's IS NOT NULL check fails for it — Priya-Dining comes back with points_redeemed as NULL here, exactly as if no redemptions row existed for that pairing at all.
Aarav's Fuel and Shopping rows show NULL points_redeemed for a different, simpler reason — there's genuinely no row in redemptions for either combination, so the LEFT JOIN has nothing to match regardless of the extra condition.
Aarav's Travel and Dining, and Priya's Fuel, Shopping, and Travel, all show their real point values — those are the combinations with a genuine, successful redemption behind them.

Keep only the combinations with no successful redemption

query.sql

SELECT u.user_name, c.category_name
FROM users u
CROSS JOIN categories c
LEFT JOIN redemptions r
  ON r.user_id = u.user_id AND r.category_id = c.category_id AND r.points_redeemed IS NOT NULL
WHERE r.redemption_id IS NULL
ORDER BY u.user_name, c.category_name;

💡 Explanation

WHERE r.redemption_id IS NULL checks a column from the right side of the LEFT JOIN, not points_redeemed again — after step 2's join condition, a row that failed to match has every column from redemptions come back NULL, including redemption_id, so checking it here is just asking 'did this pairing fail to find a successful redemption.'
Eight rows survive: Aarav-Fuel, Aarav-Shopping, Priya-Dining, and every single user's Entertainment row — nobody has redeemed anything in that brand-new category yet. Two genuine no-shows, one reversed attempt, and a category with zero adoption across the board all get treated the same correct way here, even though only Priya-Dining has a literal row sitting in redemptions.
Aarav-Travel, Aarav-Dining, Priya-Fuel, Priya-Shopping, Priya-Travel, and every Travel, Dining, Shopping, and Fuel combination for Rohan, Meera, and Karthik are all missing from this result — every one of them matched a real, successful redemption back in step 2, so they correctly don't belong in a list of categories someone hasn't engaged with.
Without the CROSS JOIN from step 1, there would be no Aarav-Fuel or Aarav-Shopping row to begin with — a query built only from redemptions and a plain WHERE clause can only ever filter rows that already exist, and a genuinely missing combination was never a row to filter in the first place.

▶ Practice this live ← Browse all questions