Lesson 4 Study Guide

Aggregation turns many rows into a few summary rows. CASE WHEN adds if-then logic anywhere. Knowing the order SQL runs keeps queries correct and cheap. It is the same idea as an Excel pivot table.

Aggregation Dimensions and metrics

A summary table has two kinds of columns: dimensions (the point of view: year, country) and metrics (the numbers: revenue, orders).

SELECT   dim_column, AGG(metric_column)
FROM     table_name
WHERE    row_condition
GROUP BY dim_column
HAVING   AGG(metric_column) > 0
ORDER BY dim_column

SUM totalCOUNT rowsCOUNT(DISTINCT x) uniqueAVGMIN / MAX

Filtering WHERE vs HAVING

WHERE

Filters rows before grouping. Cannot contain aggregate functions.

HAVING

Filters groups after grouping. Use it for conditions like COUNT(*) > 1000.

If a condition can go in WHERE, put it in WHERE: fewer rows reach the grouping step.

Execution order The database does not read top to bottom

1FROM / JOINpick and combine tables

2WHEREfilter rows

3GROUP BYform groups

4HAVINGfilter groups

5SELECTcompute columns, aliases

6ORDER BYsort

7TOP / LIMITcut the result

Consequences: WHERE cannot use a SELECT alias (it does not exist yet), but ORDER BY can. On large data, shrink tables before the JOIN. A common interview topic.

CASE WHEN Four uses

Categorise: score → grade, order value → Low / Mid / High.
Conditional aggregation: COUNT(CASE WHEN online = 1 THEN id END) turns a long table into a wide one and builds ratios.
Dynamic column value: adjust SubTotal depending on the sales channel.
Clean and transform: unify messy values, create a segment column.

CASE WHEN works in SELECT, WHERE, GROUP BY and inside aggregate functions. Without ELSE, unmatched rows become NULL.

Metrics Break revenue into parts

Revenue = Orders × AOV
= Customers × Frequency × AOV

AOV (average order value) = revenue ÷ orders.
Frequency = orders ÷ distinct customers.
Profit margin = profit ÷ revenue.
Ratio metrics = conditional count or sum ÷ total. Multiply by 1.00 to avoid integer division.

The parts multiply to the whole and do not overlap (MECE). That is how you explain why revenue moved.

Watch out Mistakes seen in this lesson's practice

Revenue has three definitions. TotalDue includes tax and freight, SubTotal is the order value, LineTotal is the line value after discount. Class practice uses TotalDue, HW 1–2 use LineTotal. State your choice, never mix them in one ratio.
HW 4 mixes two bases. The key's numerator uses TotalDue while its "Total_Revenue" column uses SubTotal. The percentage divides by TotalDue, so the percentage is right but the column next to it is not its denominator.
2014 is a half year. Orders stop on 2014-06-30. Never compare 2014 totals with a full year. Use ratios or monthly averages.
Day-of-week depends on settings. T-SQL DATEPART(weekday, ...) returns 1 = Sunday by default. If the server's first-day setting differs, weekends are wrong.
Integer division. COUNT(a) / COUNT(b) returns 0 or 1. Multiply by 1.00 first.
Class example 2 says "< 100 low" in the comment but the code uses <= 100. Boundaries need a clear rule.

Lab Run a GROUP BY one clause at a time

Twelve illustrative orders. Step through the clauses in the order the database runs them and watch the table shrink. Change the filters to see which step they affect.

WHERE year >= HAVING SUM(amount) >= TOP