Break a hard question into steps. Use a subquery for a quick one-off, a CTE when the logic has several stages, and a window function when you need a calculation across rows without losing them.
The answer key's tip: plan reusable CTEs first.
Subquery A query inside a query
It sits in parentheses and can appear in:
- SELECT: returns one value for every row (for example the overall average).
- FROM: acts as a temporary table. Needs an alias.
- WHERE: a single value (
> (SELECT AVG ...)) or a list (IN (SELECT ...)). - HAVING: compare a group aggregate with a value from another query.
CTE Name a step, reuse it
WITH step1 AS (SELECT ...),
step2 AS (SELECT ... FROM step1)
SELECT * FROM step2
- Declare before use. Valid only for the statement that follows (up to the
;). - Run the whole script, CTE and main query together.
- Each CTE can read the ones declared above it.
Choose Subquery vs CTE
| Subquery | CTE | |
|---|---|---|
| Best for | Short, used once | Several steps, logic reused, easier to review |
| Readability | Gets hard when nested | Reads top to bottom like a recipe |
| Reuse | Repeat the text each time | Name once, use many times |
| Debugging | Pull the inner part out | Select from any single step |
Both give the same result. The CTE version is longer but far easier to check, which matters in the homework.
Window functions Calculate across rows, keep every row
GROUP BY collapses rows into one. A window function adds the calculated value to each row and keeps all rows.
function(...) OVER (
PARTITION BY col -- optional: restart per group
ORDER BY col -- optional: order inside the group
ROWS BETWEEN ... -- optional: window frame
)
It replaces long self-joins and is usually faster.
Four families Pick by the question
| Question | Use |
|---|---|
| Running total, moving average | SUM / AVG ... OVER with ORDER BY or a frame |
| Top N per group, rank | ROW_NUMBER, RANK, DENSE_RANK |
| Compare with previous or next row | LAG, LEAD (also FIRST_VALUE, LAST_VALUE) |
| Percentile position | PERCENT_RANK = (rank − 1) ÷ (rows − 1) |
Ranking Ties
For values 200, 150, 150, 90:
ROW_NUMBER 1 2 3 4RANK 1 2 2 4DENSE_RANK 1 2 2 3
RANK leaves a gap after a tie, DENSE_RANK does not, ROW_NUMBER never repeats. Ranking functions need ORDER BY and take no frame. Try it in the Window lab.
Method Four steps for any hard SQL
- Write the expected output: metrics and dimensions.
- Find the data sources and combine them in a base CTE.
- Calculate the metrics, then add the window or subquery logic.
- Validate against a number you can check by hand.
Watch out Mistakes seen in this homework key
- 2014 is a half year (orders end 2014-06-30). The YoY growth for 2014 in Q3 compares six months with twelve, so every country looks like it collapsed.
- Gaps break LAG and LEAD. They look at the neighbouring row, not the neighbouring year or month. If a year is missing for a group, you compare the wrong pair. Q4 removes zero-promotion years, so check the years are consecutive.
- The default frame matters.
SUM() OVER (ORDER BY ...)is a running total because the default frame ends at the current row. The key writes "frame: all", which would give the grand total. - First period growth is NULL, not 0. Q7 sets it to 0, which reads as "no growth".
- Ties in Top N. RANK or DENSE_RANK keeps every tie, so "top 5" can return more than 5 rows (Q10 returns 53 rows for 10 territories).
- Revenue and AOV have more than one base. The key's variants use SubTotal, TotalDue or LineTotal. Pick one and say so.
Lab What does the window do to each row?
Eight illustrative rows. Choose a function, a partition and an ordering. The new column result is computed per row. Note how rows keep their identity, unlike GROUP BY.