SQL cookbook

8 recipes · 52 steps · about 42 minutes of reading

Answer an ad hoc data request
No dashboard covers this. Someone just needs one number, fast.
BeginnerRead 4 minDoing: 20 to 30 minutes6 steps
Pull customer names onto every order with a join
Customer details live in one table, order details in another, and the person asking does not care which table anything sits in, only that the final numbers match up.
BeginnerRead 4 minDoing: half an hour6 steps
Which customers have never placed an order?
How do you find every customer who signed up but never bought anything?
Beginner to intermediateRead 5 minDoing: an hour6 steps
Leadership wants revenue grouped by region, not a spreadsheet of every order
Every time leadership asks how one region compares to the others, I end up exporting a spreadsheet with thousands of order rows for them to sift through by hand.
Beginner to intermediateRead 5 minDoing: 20 to 30 minutes once you know the schema, a bit longer the first time7 steps
Collapse duplicate signups down to one row each
Duplicate signups, thanks to a broken import.
IntermediateRead 6 minDoing: 30 to 45 minutes once you've settled on the tiebreak rule, longer the first time the copies genuinely disagree7 steps
One global top-spenders list hides the winners in each region
One company-wide top-spenders list crowns a single winner and hides every region's own best customer underneath it.
IntermediateRead 6 minDoing: 30 to 45 minutes once the completed-order totals exist, longer the first time you write a partitioned ranking query7 steps
Standardize a status column so every capitalization spelling counts as one value
The same status shows up as Active, active, and ACTIVE, and every grouped report I run treats the three spellings as separate values instead of one.
Intermediate to advancedRead 6 minDoing: 25 to 40 minutes to count, decide, fix, and verify; a few more minutes if you add the guard constraint too6 steps
Stack two monthly exports into one combined report with UNION
This month's export and last month's export sit in two separate query results, and the chart needs every row from both stacked into a single table.
Intermediate to advancedRead 6 minDoing: 30 to 45 minutes once you know both exports' columns, longer the first time a column turns out to be the wrong type7 steps

Cookbook badge

Finish all 8 recipes to earn this.

In these roadmaps

+2 more

SQL cookbook

Answer an ad hoc data request

Pull customer names onto every order with a join

Which customers have never placed an order?

Leadership wants revenue grouped by region, not a spreadsheet of every order

Collapse duplicate signups down to one row each

One global top-spenders list hides the winners in each region

Standardize a status column so every capitalization spelling counts as one value

Stack two monthly exports into one combined report with UNION

In these roadmaps

SQL cookbook

Answer an ad hoc data request

Pull customer names onto every order with a join

Which customers have never placed an order?

Leadership wants revenue grouped by region, not a spreadsheet of every order

Collapse duplicate signups down to one row each

One global top-spenders list hides the winners in each region

Standardize a status column so every capitalization spelling counts as one value

Stack two monthly exports into one combined report with UNION

In these roadmaps