018 min
Case 01 · The messy customer export
Open case →SELECT
Ask for only the columns you need
MISSIONThe sales lead only needs customer name and city. Return those two columns without exposing IDs or emails.
🗃️ DATA DETECTIVE AGENCY
Learn SQL by solving business mysteries.
Inspect small tables, decide what evidence you need, shape the query, then explain what the result means. The V1 journey moves from SELECT and filtering through grouping, join types, subqueries, dates, windows and NULL handling.
15 V1 cases≈ 183 guided minutesQuery lab + expected results✓ Final investigation
15 CASE FILES
Each query exists because somebody needs an answer.
Cases unlock in order. Completed cases remain reviewable and the final investigation unlocks only after all 15 foundations are closed.
YOUR DETECTIVE BOARD0/15 cases closed · 0% complete
029 min
Case 02 · The overdue invoice list
WHERE
Filter to relevant rows
MISSIONCollections needs only overdue invoices with invoice number, customer and amount.
🔒 Close case 1 first
039 min
Case 03 · Which orders deserve attention?
ORDER BY & LIMIT
Rank and focus
MISSIONThe owner wants the three largest orders, highest amount first.
🔒 Close case 1 first
049 min
Case 09 · Which cities do we actually serve?
DISTINCT
Remove duplicate answer values
MISSIONReturn the unique cities represented in the customer table, alphabetically.
🔒 Close case 1 first
0511 min
Case 04 · What did we actually sell?
Aggregates
Summarise many rows
MISSIONGive the owner order count, total revenue and average order value in one summary row.
🔒 Close case 1 first
0612 min
Case 05 · Which category drives revenue?
GROUP BY
Compare useful groups
MISSIONCompare revenue by category and show the strongest category first.
🔒 Close case 1 first
0712 min
Case 10 · Which categories are material?
HAVING
Filter groups after aggregation
MISSIONShow only categories whose total revenue is at least ₹3,000, strongest first.
🔒 Close case 1 first
0814 min
Case 06 · Orders have IDs, humans need names
JOINs
Connect related tables
MISSIONShow every order with the human-readable customer name instead of only customer_id.
🔒 Close case 1 first
0915 min
Case 11 · Customers with and without orders
INNER vs LEFT JOIN
Choose what happens to unmatched rows
MISSIONProduce a customer-first list that still shows Nina and Kabir even though they have no orders.
🔒 Close case 1 first
1012 min
Case 07 · Turn raw spend into useful segments
CASE
Create business rules in a query
MISSIONClassify customers as VIP (₹10k+), Regular (₹3k–₹9,999) or New (below ₹3k).
🔒 Close case 1 first
1114 min
Case 12 · Orders above the business average
Subqueries
Use one query result inside another
MISSIONReturn orders whose amount is above the average order value.
🔒 Close case 1 first
1215 min
Case 08 · Build a readable monthly performance query
CTEs
Break complex questions into steps
MISSIONFirst calculate revenue by month in a named step, then return the monthly result in chronological order.
🔒 Close case 1 first
1314 min
Case 13 · What happened in July?
Dates
Ask time-based business questions
MISSIONReturn only July 2026 orders using a half-open date range.
🔒 Close case 1 first
1416 min
Case 14 · Rank customers inside each region
Window functions
Compare rows without collapsing them
MISSIONRank sales reps by revenue within each region while keeping every rep row.
🔒 Close case 1 first
1513 min
Case 15 · Missing payment dates
NULL handling
Reason about missing and unknown values
MISSIONFind invoices with no payment date and display a readable fallback label.
🔒 Close case 1 first
🔒 FINAL INVESTIGATION
Close all 15 SQL cases to unlock the final investigation.Explain the business, not just the query.
Combine filtering, aggregation, grouping, joins, dates, NULL handling and higher-order analysis into an evidence-backed performance explanation.
HOW THE LAB WORKS
Local structural checks, deterministic teaching results.
LearnCode does not send arbitrary learner SQL to the application server. The guided lab checks whether the required query ideas are present, then reveals the deterministic result expected for the sample evidence.
Start SQL Detective →