Tests analytical SQL: grouping and HAVING, CTEs, window functions and their frames, joins that silently change row counts, and keeping queries cheap at warehouse scale. Most answers were checked by running the query in DuckDB.
Based on chapter 8. Every answer comes with an explanation and a link back to the section.
/Q8
Quiz
Question 1 of 26
GROUP BY, HAVING and aggregate NULL handling Level 1
Progress
You want only the departments whose average salary is above 80,000. Where does the condition AVG(salary) > 80000 belong, and why?
/02
练习From the book
8 exercises
The chapter's own exercises are open questions. Try one, then compare with the model answer.
Exercise 8.1 [W]
In one or two sentences, explain why HAVING SUM(revenue) > 1000 works but HAVING total_revenue > 1000 (referencing an alias defined as SUM(revenue) AS total_revenue in the same query) fails in many engines.
Show the model answer
Standard SQL evaluates HAVING before column aliases defined in the SELECT list are resolved. The aggregate expression SUM(revenue) exists as soon as the grouping happens, but the alias total_revenue is only attached to the output column afterwards, so HAVING can't yet "see" it. PostgreSQL, SQL Server and Oracle follow the standard and reject the alias. DuckDB, MySQL and SQLite accept it as an extension. Repeating the full aggregate expression (HAVING SUM(revenue) > 1000) works because it doesn't depend on the alias having been assigned.
Exercise 8.2 [W]
Explain the difference in output between RANK(), DENSE_RANK(), and ROW_NUMBER() when three rows are tied for first place, using Listing 8.9's rank_in_region column as a starting point.
Show the model answer
With three rows tied for first place: ROW_NUMBER() breaks the tie arbitrarily (by whatever order the engine processes ties in) and assigns 1, 2, 3. Every row gets a distinct number even though they're tied on the ordering column. RANK() assigns all three rows 1, 1, 1 and then the next row after them jumps to 4, a gap appears that skips one fewer than the number of tied rows. DENSE_RANK() also assigns all three rows 1, 1, 1, but the next row gets 2, no gap. The choice matters operationally. ROW_NUMBER() is right for "give me exactly N rows": e.g. top 3, no ties allowed. RANK() is right when ties should be visible as ties, with the gap reflecting how many rows share that rank. DENSE_RANK() is right for "how many distinct rank levels are there": e.g. medal tables, ignoring how many people share a level.
Exercise 8.3 [A]
Adapt Listing 8.12 to group by region instead of category (joining orders to customers rather than products). Which region has the highest total completed revenue, and how close is it to the second-place region?
Show the model answer
Adapting Listing 8.12 to GROUP BY c.region (joining orders to customers instead of products) gives, on the seed-42 dataset: East leads with $564,538.19 across 715 orders, West is a very close second at $564,314.41 across 666 orders, a difference of only $223.78. That's well within noise for a synthetic dataset of this size, and effectively a tie. North ($502,071.81, 557 orders) and South ($486,219.92, 591 orders) trail more noticeably. Unlike the category breakdown, where Sports led Clothing by a clear $76,509.76 margin, the regional split shows no real leader. East and West are statistically indistinguishable here, a reminder that "which group is #1" can be a fragile question when the top two are this close.
Exercise 8.4 [A]
Using Listing 8.14 as a base, change the retention window from 90 days to 60 days. Does the retention rate change substantially, and does that change your reading of how meaningful the original 90-day figure was?
Show the model answer
Changing the retention window from 90 to 60 days in Listing 8.14 (same Q1 2022 cohort of 156 customers) drops the retained count from 128 (82.1%, the chapter's reported 90-day figure) to 99 (63.5%). That's an 18.6 percentage point difference. That sensitivity to the window length is evidence for the chapter's caution about the original 90-day figure. A retention rate that swings by nearly 19 points when the window shrinks by a third is a symptom of orders scattered close to uniformly across the observation period. As the Worked Example explains, orders were assigned to customers uniformly at random over 730 days. It isn't reflecting genuine, sustained re-engagement behaviour. A retention metric this sensitive to an arbitrary window choice needs the underlying data-generating assumptions checked before it's trusted at face value.
Exercise 8.5 [A]
Using Listing 8.13's output, determine how many customers are needed to reach 50% of total revenue, and express that as a percentage of the full 200-customer base.
Show the model answer
Re-running Listing 8.13 over the full (unlimited) customer ranking shows the cumulative percentage crosses 50% at the 74th customer (49.68% at rank 73, 50.21% at rank 74). So 74 of the 200 customers, or 37.0% of the customer base, are needed to reach half of total completed revenue. That's close to, but slightly past, the "half the revenue from roughly a third of customers" pattern the chapter's Worked Example already flags as flatter than a genuine 80/20 Pareto split would produce.
Exercise 8.6 [E]
The Worked Example argues the dataset's Pareto curve is flatter than real business data because revenue was generated from independent uniform draws rather than a heavy-tailed distribution. Propose a concrete change to the data-generating code in the tutorial notebook (a specific distribution or dependency between variables) that would produce a sharper, more realistic 80/20 split. Explain why your change would have that effect.
Show the model answer
One concrete change: instead of drawing quantity uniformly from randint(1, 6) and price uniformly from uniform(5, 500), draw per-customer purchase frequency from a power-law / Zipf distribution, e.g. np.random.zipf(a=2, size=n_customers), to set how many orders each customer places. Combine that with a log-normal price distribution, np.random.lognormal(mean, sigma) instead of uniform prices, for products. Both changes matter for the same underlying reason. A Pareto (80/20) pattern emerges specifically from multiplicative, heavy-tailed processes: a small number of customers who both order far more frequently and skew towards higher-priced products. Independent uniform draws for quantity, price, and order assignment guarantee every customer's expected revenue converges towards the same value as sample size grows. That mechanically flattens the concentration curve, regardless of how many orders are simulated.
Exercise 8.7 [E]
Write a query skeleton (not necessarily runnable against this chapter's schema) that uses conditional aggregation (SUM(CASE WHEN ... THEN ... ELSE 0 END)) to compute, in a single GROUP BY category query, both completed revenue and returned revenue side by side as two columns. Explain why this is preferable to running two separate queries and joining the results.
Show the model answer
SELECT category, SUM(CASE WHEN status = 'completed' THEN revenue ELSE 0 END) AS completed_revenue, SUM(CASE WHEN status = 'returned' THEN revenue ELSE 0 END) AS returned_revenueFROM orders oJOIN products p ON o.product_id = p.product_idGROUP BY category;
This is preferable to two separate queries joined afterwards. It scans the orders/products join exactly once instead of twice. The database only needs to plan and execute a single query. The result arrives as one row per category, with both figures already aligned. No separate join step is needed in pandas afterwards to line the two result sets up by category. It also guarantees consistency: both numbers are computed from the exact same snapshot of the underlying tables. Two independently-run queries executed moments apart are not strictly guaranteed to be, if the underlying data is being concurrently modified.
Exercise 8.8 [R]
Read DuckDB's documentation on the logical order of SQL clause evaluation (FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → ORDER BY). Explain why window functions are evaluated after WHERE/GROUP BY/HAVING but before the final SELECT list is resolved. Explain what that ordering implies about why a window function can't appear directly inside a WHERE clause.
Show the model answer
Discussion only, no single answer. The logical evaluation order (FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → ORDER BY) places window functions afterWHERE and HAVING. By then, the row set is down to whatever rows and groups survive filtering. Window functions still run before the final SELECT list decides which computed columns to display. That ordering is why a window function can't appear inside a WHERE clause directly. At the point WHERE is evaluated, no window function has been computed yet. There's nothing for the filter to reference. It's also why filtering on a window function's result requires wrapping the whole query in a CTE or subquery: compute the window function inside, then filter with an outer WHERE. That's exactly the shape Listing 8.8 and procedures like Procedure 8.5 and the "top-N per group" skeleton in this chapter's Quick Reference use. Worth connecting this to why HAVING (Exercise 8.1) has the same "can't reference something not yet computed" character: both restrictions come from the same clause-ordering rule, just applied to aggregates versus window functions respectively.