The ask
A manager sends a real, slightly vague request, just like at work.
The thinking
The developer clarifies, plans the query, hits a bug and explains the fix.
Your turn
Write the query yourself. Every query runs on a real SQLite database and gets checked instantly.
ShopKart · #growth-data
E-commerce
An online store with customers, orders and a growth team that always needs numbers by 3pm.
Top 5 customers last quarter
A vague request for the board deck. Learn to pin down what “top” and “last quarter” mean before writing a single line.
- JOIN
- GROUP BY
- SUM
- ORDER BY / LIMIT
Customers who signed up but never bought
Marketing wants a win-back list. A classic anti-join, plus the LEFT JOIN trap that silently returns the wrong people.
- LEFT JOIN
- Anti-join
- ON vs WHERE
Monthly revenue with month-over-month growth
The CEO wants a growth trend. Build it with a CTE and LAG(), and dodge the integer-division bug that turns every growth rate into 0.
- CTE
- LAG()
- strftime
- Integer division
Why don't the dashboard numbers match finance?
Finance says average order value is wrong. Find the fan-out bug hiding in an existing dashboard query.
- Join fan-out
- COUNT(DISTINCT)
- Grain
- Debugging
Best-selling product in each category
“Top N per group” is the most common window-function task at work. And the ties question separates juniors from seniors.
- ROW_NUMBER()
- RANK()
- PARTITION BY
- Ties
How long until customers buy again?
Retention question from leadership: who came back for a second order, and how many days did it take?
- ROW_NUMBER()
- Conditional aggregation
- julianday()
- CTE chain
Northstar Insurance · #claims-reporting
Banking & Insurance
Policies, premiums and claims. The questions that matter to underwriting and the CFO.
Active policies and premium by product
A simple-looking summary that comes out wrong because the source system can't spell “active” consistently.
- GROUP BY
- COUNT / SUM
- LOWER()
- Dirty data
Pending claims older than 30 days
The regulator cares about slow claims. Build an aging report that gives the same answer no matter which day you run it.
- 3-table JOIN
- julianday()
- Fixed as-of date
Loss ratio by product line
The CFO's favourite KPI. The obvious join double-counts premium, and the fix is the most reusable pattern in analytics SQL.
- Pre-aggregation
- CTE
- LEFT JOIN
- COALESCE
Flag possible duplicate claims
The fraud team needs a rule turned into SQL: same policy, filed close together, similar amount. A self-join done right.
- Self-join
- julianday()
- ABS()
- Fraud rules
PeopleFirst · #people-analytics
HR & People
Headcount, salaries, attrition and org charts. Sensitive data, precise definitions.
Headcount and average salary by department
Leadership wants a department snapshot, including the brand-new team that has no one yet.
- LEFT JOIN
- AVG / ROUND
- ON vs WHERE
Who earns more than their manager?
A pay-equity review question and the most famous self-join in SQL interviews, in a real context.
- Self-join
- Aliases
- Hierarchies
Attrition rate by department for 2025
“Attrition” has a dozen definitions. Agree on one with the manager, then compute numerator and denominator in a single pass.
- CASE WHEN
- Conditional aggregation
- Date ranges
- Metric definitions
Everyone who rolls up to the VP of Engineering
A reorg needs the full tree under one leader, at any depth. The recursive CTE, explained step by step.
- Recursive CTE
- Hierarchies
- Path building
Flowdesk · #product-metrics
SaaS Product
Signups, activation, churn and retention for a B2B software product.
Daily active accounts last week
The product team wants a DAU chart. Learn why counting events is not the same as counting users.
- COUNT(DISTINCT)
- GROUP BY date
- Event tables
Which accounts churned in September?
Paid in August, didn't pay in September. One NULL in the invoices table makes the obvious query return nothing at all.
- NOT EXISTS
- NOT IN + NULL
- Subqueries
Activation funnel by plan
Signed up → created a project → invited a teammate. Build a per-plan funnel and avoid counting events instead of accounts.
- COUNT(DISTINCT CASE)
- LEFT JOIN
- Funnels
Monthly cohort retention
The classic growth table: group accounts by signup month and track how many are still logging in 1, 2 and 3 months later.
- Cohorts
- Date arithmetic
- CTE chain
- Conditional aggregation
CityCare Hospital · #hospital-ops
Healthcare
Appointments, no-shows, admissions and length of stay. Numbers that drive staffing and patient care.
No-show rate by doctor
Clinics lose slots to patients who never turn up. Get the rate right by choosing the correct denominator.
- CASE WHEN
- Conditional aggregation
- HAVING
- Denominators
Patients overdue for a follow-up
Find patients whose last real visit was over six months ago. Filtering rows before grouping gives the wrong people.
- MAX()
- GROUP BY
- WHERE vs HAVING
- julianday()
30-day readmission rate by ward
A key hospital quality metric. Compare each stay with the patient's next one, without double-counting.
- LEAD()
- PARTITION BY
- julianday()
- Self-join pitfalls
Median length of stay by ward
One very long stay makes the average lie. Calculate a median in a database that has no MEDIAN function.
- Median
- ROW_NUMBER()
- COUNT() OVER
- Outliers
SwiftShip · #ops-control
Logistics & Supply Chain
Carriers, delivery promises and warehouse stock. Every late parcel and empty shelf costs money.
On-time delivery rate by carrier
Renegotiating carrier contracts? Make sure parcels still in transit aren't counted as late.
- CASE WHEN
- NULL handling
- COUNT(column)
Which SKUs need reordering?
Current stock is the sum of every movement. Learn why SQL refuses an aggregate in WHERE.
- SUM()
- HAVING
- Aggregate errors
- 3-table JOIN
When did stock go negative?
Stock can't be below zero, yet the ledger says it was. Use a running balance to find the bad entries.
- Running total
- SUM() OVER
- ORDER BY in windows
- Data quality
Longest streak of late deliveries per carrier
“How many late deliveries in a row?” is a classic gaps-and-islands problem, and a favourite senior interview question.
- Gaps & islands
- ROW_NUMBER() difference
- Streaks
Brandly · #marketing-analytics
Marketing & Ads
Ad spend, clicks, conversions and attribution. Where marketing budgets are won and lost.
CTR and cost per click by channel
Averaging daily percentages gives the wrong CTR. Learn when to divide totals instead.
- Ratio of sums
- Average of ratios
- SUM / ROUND
Return on ad spend by campaign
Joining spend and conversions together multiplies both. Aggregate each side separately, then join.
- Pre-aggregation
- Two-way fan-out
- LEFT JOIN
- COALESCE
7-day moving average of ad spend
A moving average over rows breaks when a day is missing. Build a calendar first so 7 rows really means 7 days.
- Window frames
- ROWS BETWEEN
- Recursive date spine
- Missing days
First-touch vs last-touch attribution
Which campaign gets credit for a sale: the one that introduced the customer or the one they clicked last?
- ROW_NUMBER()
- Attribution
- Join on conditions
- COALESCE
StreamBox · #content-insights
Media & Streaming
Watch time, binge behaviour and viewing sessions for a video streaming service.
Watch hours by genre in September
BETWEEN on a timestamp quietly drops the last day of the month. Use a half-open range instead.
- Datetime ranges
- Half-open intervals
- SUM / 60.0
Who binge-watched a series?
3+ episodes of the same series in one day. Getting the GROUP BY grain right is the whole problem.
- GROUP BY expressions
- date()
- HAVING
- Grain
Heavy vs light viewers (quartiles)
Split subscribers into quartiles by watch time and see how concentrated viewing is. Don't forget the people who watched nothing.
- NTILE()
- LEFT JOIN
- Zero-activity users
- Share of total
Sessionize viewing activity
Turn a stream of views into sessions: a new session starts after a 30-minute break. A core skill for product analytics.
- Sessionization
- LAG()
- Running SUM flag
- Datetime maths