SQL for Data Analysis cookbook

12 recipes · 75 steps · about 59 minutes of reading

Fix a filter that hides NULL rows
A report's row count comes up short, because a comparison filter quietly drops any row where the column it's testing is blank.
BeginnerRead 4 minDoing: 20 minutes5 steps
The same customer's order shows up three times
The CRM export lists the same order three separate times. Each update just added a new row instead of replacing the old one.
BeginnerRead 4 minDoing: 20 to 30 minutes5 steps
Add a running total to a report
Finance wants to see today's number next to the total for the month so far, in one table.
BeginnerRead 3 minDoing: 15 to 20 minutes5 steps
Combine three monthly sales exports into one table with UNION
The pipeline drops one CSV export per month into its own file, and the board deck needs one query that treats all three months as a single continuous quarter.
Beginner to intermediateRead 5 minDoing: half an hour5 steps
Turn a deeply nested subquery into a CTE anyone can follow
You open a report you inherited and it takes ten minutes just to work out what it returns.
Beginner to intermediateRead 5 minDoing: half an hour6 steps
Track month over month growth with LAG
Your manager wants one number: did revenue grow or shrink compared with last month?
IntermediateRead 4 minDoing: 30 to 40 minutes7 steps
Best sellers by category, ranked without a manual count
Not the best sellers overall. The best sellers inside each category.
IntermediateRead 5 minDoing: 30 to 40 minutes6 steps
Smooth a noisy daily count into a 7-day rolling average
Daily numbers jump so much that a line chart of them looks like an alarm going off.
IntermediateRead 5 minDoing: under an hour7 steps
One row per product, pivoted from twelve monthly rows
A manager wants one row per product with a column for each month, spread out the way it already looks in an Excel pivot table on their screen.
IntermediateRead 5 minDoing: under an hour7 steps
Flag lapsed customers who've gone quiet
Nobody can say which customers have actually stopped buying without opening every account by hand.
Intermediate to advancedRead 6 minDoing: 45 minutes to an hour5 steps
Customers sorted into spending deciles for a campaign list
Marketing keeps asking for the top spenders, then the middle group, then everyone else, as three separate lists.
Intermediate to advancedRead 6 minDoing: 45 minutes to an hour7 steps
Build a week-one retention table from a signup cohort
Product asks what fraction of signups from a given week are still active a week later, and the next week after that.
AdvancedRead 7 minDoing: an hour to 90 minutes10 steps

Cookbook badge

Finish all 12 recipes to earn this.

SQL for Data Analysis cookbook

Fix a filter that hides NULL rows

The same customer's order shows up three times

Add a running total to a report

Combine three monthly sales exports into one table with UNION

Turn a deeply nested subquery into a CTE anyone can follow

Track month over month growth with LAG

Best sellers by category, ranked without a manual count

Smooth a noisy daily count into a 7-day rolling average

One row per product, pivoted from twelve monthly rows

Flag lapsed customers who've gone quiet

Customers sorted into spending deciles for a campaign list

In these roadmaps

SQL for Data Analysis cookbook

Fix a filter that hides NULL rows

The same customer's order shows up three times

Add a running total to a report

Combine three monthly sales exports into one table with UNION

Turn a deeply nested subquery into a CTE anyone can follow

Track month over month growth with LAG

Best sellers by category, ranked without a manual count

Smooth a noisy daily count into a 7-day rolling average

One row per product, pivoted from twelve monthly rows

Flag lapsed customers who've gone quiet

Customers sorted into spending deciles for a campaign list

In these roadmaps

SQL for Data Analysis cookbook

Fix a filter that hides NULL rows

The same customer's order shows up three times

Add a running total to a report

Combine three monthly sales exports into one table with UNION

Turn a deeply nested subquery into a CTE anyone can follow

Track month over month growth with LAG

Best sellers by category, ranked without a manual count

Smooth a noisy daily count into a 7-day rolling average

One row per product, pivoted from twelve monthly rows

Flag lapsed customers who've gone quiet

Customers sorted into spending deciles for a campaign list

Build a week-one retention table from a signup cohort

In these roadmaps

SQL for Data Analysis cookbook

Fix a filter that hides NULL rows

The same customer's order shows up three times

Add a running total to a report

Combine three monthly sales exports into one table with UNION

Turn a deeply nested subquery into a CTE anyone can follow

Track month over month growth with LAG

Best sellers by category, ranked without a manual count

Smooth a noisy daily count into a 7-day rolling average

One row per product, pivoted from twelve monthly rows

Flag lapsed customers who've gone quiet

Customers sorted into spending deciles for a campaign list

Build a week-one retention table from a signup cohort

In these roadmaps