CASE: if/then buckets 7 min live
Real reports rarely want raw numbers - they want labels. Is this product premium, standard, or value? CASE is SQL's if/then: it checks conditions in order, top to bottom, and returns the value for the first one that is true. It is how you turn a continuous price into a tidy tier a human reads at a glance.
LiveBucket products into price tiers3 min▶
The CASE below reads top to bottom and returns the first branch whose condition is true. A $24 product hits >= 20 first and gets premium; a $16 one skips that and lands on standard. Press ▶ Run, then change the thresholds and watch the tiers shift.
SELECT name, price,
CASE
WHEN price >= 20 THEN 'premium'
WHEN price >= 15 THEN 'standard'
ELSE 'value'
END AS tier
FROM products;
ELSE, any row that matches no condition returns NULL. Adding ELSE 'value' guarantees every row gets a label. When in doubt, always give CASE an ELSE.
Self-studyCASE inside SUM - counting by condition2 min read▶
A favourite trick: put CASE inside an aggregate to count or sum only the rows that meet a condition, all in one pass. SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END) counts completed orders while other columns still see every row. This "conditional aggregate" is how you build side-by-side comparison columns.
- Conditional count:
SUM(CASE WHEN ... THEN 1 ELSE 0 END)counts matches without a separate query. - Conditional sum: swap the
1for a real amount to total only qualifying rows. - Why it matters: it turns rows into columns - the foundation of pivot-style reports you will see again in b8.
Aliases, expressions, string and date functions 7 min live
Columns rarely arrive in exactly the shape a report wants. Functions reshape them in place: UPPER and LENGTH on text, substr to slice a date down to a month. An AS alias then gives the computed column a clean, human name. This is the polish layer of every query.
LiveString functions and aliases3 min▶
UPPER(name) shouts the product name; LENGTH(name) counts its characters. Neither touches the stored data - they compute a fresh value per row. The AS keyword renames each computed column so the output header reads cleanly instead of showing the raw expression.
SELECT UPPER(name) AS shout, LENGTH(name) AS len FROM products LIMIT 5;
Every expression can take an alias, not just plain columns. A calculation like price * 1.1 is far more useful labelled AS price_with_tax than shown as its raw formula.
LivePull the month out of a date3 min▶
Daybreak dates are stored as text like 2026-03-14. To group by month you only want the first seven characters, 2026-03. substr(order_date, 1, 7) takes a 7-character slice starting at position 1 - a clean year-month key. Run it and you have the building block for every monthly trend.
SELECT order_id, substr(order_date,1,7) AS month FROM orders LIMIT 8;
strftime('%Y-%m', order_date) for the same year-month, and it is the more portable idea. Date and time functions vary a lot between engines - Postgres uses to_char, BigQuery uses FORMAT_DATE. The substr trick works anywhere dates are stored as ISO text, which is why it is handy to know.
UNION and UNION ALL: stacking result sets 6 min live
A JOIN glues tables side by side, adding columns. UNION does the opposite - it stacks result sets on top of each other, adding rows. As long as both queries return the same number of columns in the same order, UNION pours the second result under the first into one combined list.
LiveStack customers and products into one list3 min▶
Each half selects a name plus a literal label - 'customer' or 'product' - so the combined list stays self-describing. Both halves return two columns in the same order, which is the one rule UNION enforces. Press ▶ Run to see the two tables poured into one column set.
SELECT name, 'customer' AS kind FROM customers UNION ALL SELECT name, 'product' AS kind FROM products LIMIT 12;
UNION removes duplicate rows - it does the extra work of de-duplicating. UNION ALL keeps every row, including duplicates, and is faster because it skips that check. When you know the halves cannot overlap (customers and products never share a row), reach for UNION ALL.
Self-studyWhen to stack vs when to join2 min read▶
The two combine operations answer different questions. Reach for a JOIN when you want to enrich each row with related columns from another table. Reach for UNION when two queries produce the same shape of row and you want them in one list.
- JOIN (wider): "attach each order's customer name" - more columns, matched by key.
- UNION (taller): "list this year's events and last year's events together" - more rows, same columns.
- Column rule: UNION needs identical column count and order in both halves; column names come from the first SELECT.
Monthly order counts ★ 12 min · everyone builds
Here is a query you will reuse in later sessions: how many orders did Daybreak get each month? It combines two things you just learned - slicing the date to a month with substr, then counting rows per group. This is the setup for the trend and growth work in b8.
Make the month key: substr(order_date,1,7) AS month turns a full date into 2026-03.
Count the rows: add COUNT(*) AS orders to count orders in each group.
Group by the key: GROUP BY month so counting happens once per month.
Order chronologically: ORDER BY month puts January first, so the trend reads left to right.
SELECT substr(order_date,1,7) AS month, COUNT(*) AS orders FROM orders GROUP BY month ORDER BY month;
This one query is the spine of most dashboards. A time bucket, a count, grouped and ordered - that is the "orders over time" line chart every founder wants on day one. Swap COUNT(*) for SUM(...) and it becomes monthly revenue; add a WHERE and it becomes a segment trend. You will lean on this shape all the way through b10.
Try it yourself - this week ◐ 20-30 min total
- Add a
CASEto the products query that labels each rowcoffeeornot coffeebased oncategory. - Write a query that shows each customer's name in
UPPERcase alongside the length of their name. - Get monthly order counts, then change
COUNT(*)to only countcompletedorders using aCASEinsideSUM. - Use
UNION ALLto build one list of all customer cities and all product categories, each tagged with its source. - Bring one "this number should be a label" question to session b6 - subqueries and CTEs let you stack these reshapes into bigger answers.
Official sources covered
This session teaches the working core of the major free SQL curricula's reshaping material, run on live Daybreak data instead of screenshots. Certificates, graded problem sets, and video lectures stay on the official sites (linked below). This page covers:
Three questions before you go 🎯 ◐ 90 seconds
1 · What does CASE do in a query?
CASE checks conditions top to bottom and returns the value for the first true one - SQL's if/then. It is how you turn raw values into labelled buckets like premium / standard / value.
2 · What is the difference between UNION and UNION ALL?
UNION does extra work to de-duplicate rows. UNION ALL keeps every row, duplicates included, and skips that check - so it is faster when you know the halves cannot overlap.
3 · How do you get the year-month (like 2026-03) from a date in SQLite?
substr(order_date,1,7) slices the first seven characters of an ISO date into a year-month key. strftime('%Y-%m', order_date) does the same and is more portable across engines.