The Analyst’s Course · Quant SQL
GROUP BY — From Rows to Intelligence
The aggregate leap
Row-level queries describe companies; aggregate queries describe markets. SELECT sector, COUNT(*) AS n, ROUND(AVG(momentum_3m)*100,1) AS avg_m3 FROM stocks GROUP BY sector ORDER BY avg_m3 DESC collapses the table into one line per sector: how many names, and where the 3-month momentum is concentrating. This is the actual structure of every sector-rotation note you have ever read — a grouped average, dressed in prose.
AVG lies when distributions skew
Sector average P/E is corrupted by one loss-maker's NULL (dropped silently) or one 400× outlier (dragging the mean). Two professional habits: (1) always pair COUNT(*) with every average so you know the sample size — an "average" of 2 names is an anecdote; (2) prefer robust aggregates when available: a rough median via ORDER BY + LIMIT/OFFSET, or MIN/MAX to spot the skew. The deeper skill is distributional thinking: momentum concentrated in 3 of 7 sectors means something different from momentum spread evenly, even at the same average.
WHERE vs HAVING
WHERE filters rows before grouping; HAVING filters groups after aggregation. "Technology and Energy, but only sectors averaging ROE above 15%": WHERE sector IN ('Technology','Energy') GROUP BY sector HAVING AVG(roe) > 0.15. Beginners conflate them constantly; the mental model is simple — WHERE is about the raw rows, HAVING is about the group summaries. The panel's result cap (500 rows) makes this mostly academic here, but the distinction is core SQL fluency you will carry to every dataset you ever touch.
The rotation query that front-ran a narrative
Throughout 2022, grouped momentum reads kept showing one thing the narrative missed: while "AI" and "growth" dominated headlines, the persistent positive-momentum groups were energy, fertilizers, and defense — the inflation/geo-risk complex. A weekly GROUP BY momentum by sector was the cheapest macro note available, and it preceded the big rotation months. The lesson is not that aggregates predict; it is that aggregation converts a pile of tickers into the signal everyone else was too busy narrating to compute.
What you'll practise
WHERE vs HAVING: which correctly filters groups after aggregation?
3 graded checkpoints · certification exam at the end of the track
Sources
Fama & French (1992, 2015); Daniel & Moskowitz (2016); screener traps per standard practice
Learn content is for education only — not individualized financial advice, a recommendation, or a solicitation to buy or sell any security. Options involve substantial risk. Examples are simplified and historical patterns never guarantee future results.