Course 2 — Reliable Aggregation | The Art of PostgreSQL

Module 01

Foundations of Aggregation

Core · 4 topics

GROUP BY collapses rows into groups and the evaluation order — WHERE → GROUP BY → HAVING → SELECT — determines which clauses can reference aggregate results. This module builds that mental model against F1 race data.

Book reference: Chapter 16 — Group By, Having, With, Union All

GROUP BY semantics and evaluation orderUnderstand how GROUP BY organizes query results into distinct groups.

Aggregate functions: SUM, COUNT, AVG, MIN, MAXApply COUNT, SUM, AVG, MIN, and MAX to summarize data within groups.

NULL handling and boolean aggregatesSpot how NULL values silently change aggregate totals and boolean results.

Row vs group contextKnow which columns must be listed in GROUP BY and why.

Free preview: GROUP BY and HAVING · Common Aggregate Functions

SQL clause evaluation order — How the SQL evaluation order determines which clauses can reference aggregate results.

Module 01 · GROUP BY decade

select extract(
         'year' from
         date_trunc('decade', date)
       ) as decade,
       count(*) as races
  from races
 group by decade
 order by decade;

Race count grouped by decade across all of F1 history.

Module 02

Correctness in Aggregates

Core · 4 topics

COUNT(col) excludes NULLs while COUNT(*) does not. COUNT DISTINCT carries hidden performance costs. This module traces every correctness trap with working examples on real datasets.

Book reference: Chapter 16 — Group By, Having, With, Union All

Duplicate rows and COUNT DISTINCTAvoid inflated counts caused by joining before aggregating.

WHERE vs HAVINGChoose the right filter clause for conditions before or after grouping.

Filtered aggregates with FILTER clauseCompute multiple conditional aggregates in a single pass over the data.

DISTINCT vs GROUP BYKnow when to reach for DISTINCT versus GROUP BY to remove duplicates.

Free preview: GROUP BY and HAVING · Common Aggregate Functions · The FILTER Clause

SQL evaluation order — Where each filter clause sits in evaluation and what values it can see.

Module 02 · FILTER for accident rate

select extract(year from races.date)
         as season,
       count(distinct raceid) as nb_races,
       count(*) as nb_status,
       count(*) filter(
         where status = 'Accident'
       ) as accidents,
       round(100.0
         * count(*) filter(
             where status = 'Accident'
           )
         / count(*), 2) as pct
  from results
       join status using(statusid)
       join races  using(raceid)
 group by season
 order by accidents desc
 limit 5;

Top 5 seasons by accident count, with total results and accident percentage per season.

Module 03

Multi-Level Aggregation Patterns

Core · 3 topics

Window functions combined with GROUP BY enable multi-level summaries without running the query twice. Nested subqueries let you aggregate over aggregates cleanly.

Book reference: Chapter 16 — Group By, Having, With, Union All

CTEs for multi-step aggregationStructure complex queries by naming and reusing intermediate aggregation results.

Chaining CTEsAggregate over aggregated values by chaining query steps together.

Nested aggregation with multiple CTEs and GROUPING SETSBuild multi-level summaries without repeating expensive table scans.

Free preview: Common Aggregate Functions

CTE pipeline for multi-step aggregation — How chained CTEs pass aggregated results between steps in a single query.

Module 03 · Per-decade top scorer

with decade_points as (
  select extract('year' from
           date_trunc('decade', races.date)
         )::int as decade,
         drivers.driverid,
         drivers.surname,
         sum(results.points) as points
    from results
         join races   using(raceid)
         join drivers using(driverid)
   group by decade,
            drivers.driverid,
            drivers.surname
),
decade_max as (
  select decade, max(points) as top
    from decade_points
   group by decade
)
select dp.decade,
       dp.surname as top_scorer,
       dp.points
  from decade_points dp
       join decade_max dm using(decade)
 where dp.points = dm.top
 order by dp.decade;

The highest-scoring F1 driver in each decade, with their total points for that decade.

Module 04

Advanced GROUP BY Features

Core · 4 topics

GROUPING SETS computes multiple GROUP BY combinations in one scan. ROLLUP adds hierarchical subtotals; CUBE adds all cross-tabulations. GROUPING() distinguishes a NULL result from a missing row.

Book reference: Chapter 16 — Group By, Having, With, Union All

GROUPING SETSGenerate several grouping combinations in one query without repeating the scan.

ROLLUPAdd hierarchical subtotals and a grand total alongside detail rows.

CUBECross-tabulate totals across all combinations of grouping columns at once.

GROUPING() functionDistinguish subtotal rows from genuine NULL values in aggregation results.

Grouping sets lattice — How GROUPING SETS, ROLLUP, and CUBE differ in the combinations they produce.

Module 04 · ROLLUP subtotals

select drivers.surname     as driver,
       constructors.name   as constructor,
       sum(points)         as points
  from results
       join races        using(raceid)
       join drivers      using(driverid)
       join constructors using(constructorid)
 where drivers.surname in ('Prost', 'Senna')
 group by rollup(
   drivers.surname,
   constructors.name
 );

Points for Prost and Senna by constructor, with per-driver subtotals and a grand total.

Module 05

Combining Aggregates with Other SQL Features

Core · 4 topics

The FILTER clause computes conditional counts and sums in a single pass — far faster than multiple CASE expressions. Ordered-set aggregates (percentile_cont, mode) extend what GROUP BY can express.

Book reference: Chapter 16 — Group By, Having, With, Union All

Aggregation with joinsPrevent inflated counts by aggregating before joining instead of after.

Conditional aggregation with CASE inside SUMSplit totals into multiple conditional categories within a single query.

Window functions vs GROUP BYDecide when a window function is a better fit than GROUP BY.

A complete reporting query: FILTER, HAVING, COUNT DISTINCT combinedCombine multiple aggregation techniques into one complete, accurate report.

Free preview: Common Aggregate Functions · The FILTER Clause

Module 05 · Constructor report

with season_results as (
  select constructors.constructorid,
         constructors.name as constructor,
         results.points,
         results.position,
         results.milliseconds,
         results.raceid
    from results
         join races        using(raceid)
         join constructors using(constructorid)
   where races.year = 2017
)
select constructor,
       count(*) as entries,
       count(*) filter(
         where position = 1) as wins,
       count(*) filter(
         where milliseconds is null
           and position    is null) as dnfs,
       sum(points) as total_points
  from season_results
 group by constructorid, constructor
having count(*) >= 20
 order by total_points desc;

2017 F1 constructor standings with race entries, wins, DNFs, and total points for teams with 20+ entries.

Module 06

Performance & Scalability of Aggregations

Core · 4 topics

HashAgg vs SortedAgg, spill to disk, and partial aggregation in parallel plans. This module explains when to pre-aggregate into a materialized view versus aggregating live at query time.

Book reference: Chapter 16 — Group By, Having, With, Union All

Hash vs sort aggregationPredict which aggregation strategy PostgreSQL will choose for a given query.

Reading aggregation plansRead an EXPLAIN plan to identify which aggregation node your query uses.

Pre-aggregation strategies with materialized viewsDecide when pre-aggregating into a stored view pays off over live aggregation.

Profiling with EXPLAIN (ANALYZE, BUFFERS)Profile a slow aggregation query to locate and fix the bottleneck.

Aggregation plan node types — When and why aggregation spills to disk, and how to see it in EXPLAIN.

Module 06 · EXPLAIN HashAggregate

explain
  select extract(
           'year' from
           date_trunc('decade', date)
         ) as decade,
         count(*) as races
    from races
   group by decade;

EXPLAIN output for a decade-level race count grouped by a computed decade expression.

Module 07 · Advanced

Designing Reliable Reporting Queries

Advanced · 6 topics

Reliable reporting queries stay correct as data grows — no implicit column dependencies, no unstable GROUP BY expressions, behavior verified with realistic data volumes before going to production.

Book reference: Chapter 16 — Group By, Having, With, Union All

Idempotent aggregationWrite reporting queries that remain correct no matter how many times they run.

Ordered-set aggregates: percentile_cont, percentile_disc, mode()Compute median, percentile, and mode summaries over grouped data.

Gap filling with generate_seriesEnsure every time period appears in a report even when source rows are missing.

Datetime fence-post trapAvoid accidentally dropping events at the boundary of a date-range filter.

Drift detection with LAG()Detect unexpected changes in a metric by comparing each period to the previous one.

Bi-temporal modelingModel data with two independent timelines: when it was true and when it was stored.

Free preview: Percentiles & Ordered-Set Aggregates

Time bands for gap filling — How time bands surface missing periods and bi-temporal record boundaries.

Module 07 · Gap filling

with seasons as (
  select generate_series(2007, 2017)
         as season
),
mclaren_wins as (
  select races.year as season,
         count(*)   as wins
    from results
         join races        using(raceid)
         join constructors using(constructorid)
   where constructors.name = 'McLaren'
     and races.year between 2007 and 2017
     and position = 1
   group by races.year
)
select s.season,
       coalesce(w.wins, 0) as wins
  from seasons       s
       left join mclaren_wins w
         using(season)
 order by s.season;

McLaren's win count for each year from 2007 to 2017, including seasons with zero wins.

Module 08 · Architect

Aggregation in Data Architectures

Architect · 5 topics

MERGE, upsert patterns, and incremental aggregation maintain summary tables at scale. Trade-offs between query-time aggregation, periodic rollups, and streaming pre-aggregation.

Book reference: Chapter 16 — Group By, Having, With, Union All

Materialized viewsRefresh pre-computed summaries without blocking concurrent read queries.

Incremental aggregation with upsert (INSERT … ON CONFLICT)Keep a summary table current by safely overwriting stale rows on reload.

ETL/ELT patterns and MERGE (PostgreSQL 15+)Apply inserts, updates, and deletes atomically in a single data-load statement.

OLAP vs OLTP trade-offsChoose the right database design based on your query's selectivity pattern.

Distributed aggregation with CitusScale aggregation to more data than fits on one server, staying within PostgreSQL.

Citus distributed architecture: coordinator, workers, shards — How a distributed query splits aggregation work across coordinator and worker nodes.

Module 08 · Idempotent upsert

insert into constructor_season_summary
       (season, constructorid, name,
        points, races)
  select races.year,
         constructors.constructorid,
         constructors.name,
         sum(results.points),
         count(distinct results.raceid)
    from results
         join races        using(raceid)
         join constructors using(constructorid)
   where races.year between 2010 and 2017
   group by races.year,
            constructors.constructorid,
            constructors.name
on conflict (season, constructorid)
   do update set
     points = excluded.points,
     races  = excluded.races;

Constructor season totals upserted into a summary table, safe to re-run for 2010–2017.

Reliable Aggregation

Foundations of Aggregation

Correctness in Aggregates

Multi-Level Aggregation Patterns

Advanced GROUP BY Features

Combining Aggregates with Other SQL Features

Performance & Scalability of Aggregations

Designing Reliable Reporting Queries

Aggregation in Data Architectures

All 6 courses. One package.