Case Studies

Real-world scenarios where QueryCase taught engineers SQL through narrative detective investigations.

The Missing Customer Orders

Difficulty: Intermediate Topics: JOINs, WHERE, GROUP BY

A customer reports that recent orders are missing from their dashboard. You receive three tables: customers, orders, and order_items. The narrative guides you through writing a query to find why orders from specific months vanished.

By the end, you'll have written a multi-JOIN query that surfaces the root cause: a data pipeline bug affecting orders placed Feb 1-15. The investigation teaches proper OUTER JOIN usage and performance hints for large datasets.

JOINs Debugging

The Fraud Ring Investigation

Difficulty: Advanced Topics: Subqueries, Window Functions, HAVING

A payment processor asks you to find a fraud ring testing stolen cards. You have transaction logs with timestamps, card numbers, merchant IDs, and amounts. The narrative unfolds as a detective case: spot the pattern (same card across merchants in minutes), then aggregate by card to find coordinated testing sequences.

You'll write nested subqueries and window functions (ROW_NUMBER, LAG) to rank transactions and spot impossible timings. By the end, you've identified 47 stolen cards and the merchants involved in the scheme.

Fraud Detection Window Functions

The Churn Prediction Puzzle

Difficulty: Intermediate Topics: CTEs, Aggregation, Date Functions

A SaaS product manager suspects certain user cohorts churn faster than others. You have user_accounts, subscriptions, and cancellations tables. The puzzle: which signup month had the worst 90-day retention, and why?

You'll use Common Table Expressions (CTEs) to build intermediate datasets: accounts by signup month, their first subscription, and whether they cancelled within 90 days. The narrative reveals that January signups hit a platform outage in March. The answer surfaces a real operational issue.

Retention Analytics CTEs

The Inventory Crisis

Difficulty: Beginner Topics: COUNT, SUM, GROUP BY, ORDER BY

A warehouse manager needs to know which products are understocked. You have inventory levels, recent orders, and supplier lead times. The puzzle: which SKUs will run out of stock in 14 days if order velocity continues?

This case teaches GROUP BY and aggregates without JOINs yet. You'll calculate daily order volume per SKU, project forward, and flag items needing emergency restocking. A perfect entry point for non-analysts new to SQL.

Inventory GROUP BY