Lesson 09 intermediate

Aggregates, CTEs and Window Functions

Summarise data with count, sum, avg, min and max; group with GROUP BY and filter groups with HAVING; count several things at once with FILTER; structure long queries with WITH and walk trees with WITH RECURSIVE; and rank, number, compare and accumulate rows with window functions.

So far every query returned individual rows. Reports, dashboards and APIs mostly need summaries: how many notes per user, activity per month, the newest note of each user. This lesson covers the three tools for that: aggregates with GROUP BY, common table expressions, and window functions.

Aggregate functions

An aggregate turns many rows into one value:

SELECT count(*) AS notes,
       count(*) FILTER (WHERE pinned) AS pinned,
       min(created_at)::date AS first,
       max(created_at)::date AS last
FROM notes;
 notes | pinned |   first    |    last
-------+--------+------------+------------
     5 |      2 | 2026-10-01 | 2026-11-02
(1 row)
FunctionReturns
count(*)Number of rows
count(col)Number of non-NULL values
count(DISTINCT col)Number of different non-NULL values
sum, avg, min, maxWhat the name says; they ignore NULLs
string_agg(col, ', ')Values joined into one string
array_agg(col) / jsonb_agg(col)Values collected into an array or JSON array
bool_and, bool_orWhether all, or any, values are true

FILTER (WHERE ...) restricts one aggregate to some rows. It replaces the old trick of sum(CASE WHEN ... THEN 1 ELSE 0 END) and lets one query compute many counts in a single pass over the table.

GROUP BY

GROUP BY splits the rows into groups and computes the aggregates once per group:

SELECT plan, count(*) AS users
FROM users
GROUP BY plan
ORDER BY plan;
 plan | users
------+-------
 free |     1
 pro  |     2
(2 rows)

Enum values sort in the order they were declared, so free comes before pro. Every column in the SELECT list must either be in GROUP BY or inside an aggregate; grouping by a table’s primary key lets you select any other column of that table.

Grouping by an expression works too. Notes per month:

SELECT date_trunc('month', created_at)::date AS month, count(*) AS notes
FROM notes
GROUP BY 1
ORDER BY 1;
   month    | notes
------------+-------
 2026-10-01 |     4
 2026-11-01 |     1
(2 rows)

GROUP BY 1 refers to the first column of the select list, a convenient shorthand for long expressions.

HAVING

WHERE filters rows before grouping; HAVING filters groups after it:

SELECT u.name, count(*) AS notes
FROM notes n
JOIN users u ON u.id = n.user_id
GROUP BY u.id, u.name
HAVING count(*) > 2;
   name    | notes
-----------+-------
 Ana Silva |     3
(1 row)

ROLLUP, CUBE and GROUPING SETS add subtotal and total rows in the same query, for example GROUP BY ROLLUP (plan) adds a grand total row with plan NULL.

Common table expressions (WITH)

A long query is easier to read as a sequence of named steps. WITH defines temporary result sets, called CTEs, that the rest of the query uses like tables:

WITH per_user AS (
  SELECT u.id, count(n.id) AS notes
  FROM users u
  LEFT JOIN notes n ON n.user_id = u.id
  GROUP BY u.id
)
SELECT round(avg(notes), 2) AS avg_notes_per_user,
       max(notes) AS most_notes
FROM per_user;
 avg_notes_per_user | most_notes
--------------------+------------
               1.67 |          3
(1 row)

Postgres usually folds simple CTEs into the main query, so they cost nothing extra. Add MATERIALIZED (WITH per_user AS MATERIALIZED (...)) when you want a CTE computed exactly once.

CTEs can also change data. This moves old notes into an archive table in one atomic statement:

WITH moved AS (
  DELETE FROM notes WHERE created_at < '2025-01-01' RETURNING *
)
INSERT INTO notes_archive SELECT * FROM moved;

Recursive CTEs

WITH RECURSIVE repeats a query, feeding each step’s result into the next, until no new rows appear. It walks trees such as folders, categories or org charts. A simple example that generates the numbers 1 to 5 and adds them up:

WITH RECURSIVE n(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 5
)
SELECT sum(i) FROM n;
 sum
-----
  15
(1 row)

For a folder tree, the first part selects the root folders (WHERE parent_id IS NULL) and the recursive part joins folders to the CTE on folders.parent_id = tree.id, carrying a depth or path column along.

Window functions

Aggregates collapse rows. Window functions compute a value across a set of related rows but keep every row. The set is defined with OVER (...):

  • PARTITION BY splits rows into independent groups (like GROUP BY, but without collapsing).
  • ORDER BY inside OVER orders rows within each partition.

Number each user’s notes from newest to oldest:

SELECT user_id, id, title,
       row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS nth
FROM notes
ORDER BY user_id, nth;
 user_id | id |      title       | nth
---------+----+------------------+-----
       1 |  5 | Meeting notes    |   1
       1 |  2 | Deploy checklist |   2
       1 |  1 | Shopping list    |   3
       2 |  4 | Postgres tips    |   1
       2 |  3 | Book ideas       |   2
(5 rows)

Filter on nth = 1 in an outer query and you have the latest note per user, another solution to the problem LATERAL solved in lesson 8.

Comparing with the previous row

lag() and lead() read a value from the previous or next row. How much time passed between consecutive notes?

SELECT id, created_at - lag(created_at) OVER (ORDER BY created_at) AS since_previous
FROM notes
ORDER BY created_at;
 id |  since_previous
----+------------------
  1 | (null)
  2 | 2 days 05:30:00
  3 | 2 days 05:45:00
  4 | 6 days 12:30:00
  5 | 21 days 01:15:00
(5 rows)

Running totals and shares

With ORDER BY inside OVER, an aggregate becomes cumulative. Without anything inside OVER (), it covers all rows, which is perfect for percentages:

SELECT user_id,
       count(*) AS notes,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM notes
GROUP BY user_id
ORDER BY user_id;
 user_id | notes | pct
---------+-------+------
       1 |     3 | 60.0
       2 |     2 | 40.0
(2 rows)

Here the window function runs after grouping: sum(count(*)) OVER () adds up the per-user counts.

Window functionUse
row_number()1, 2, 3 within the partition
rank() / dense_rank()Rankings with ties (with or without gaps)
lag(col) / lead(col)Previous or next row’s value
first_value(col) / last_value(col)First or last value in the window
sum(col) OVER (ORDER BY ...)Running total
avg(col) OVER (ORDER BY ... ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)Moving average over 7 rows
ntile(4)Split into quartiles

Quick reference

TaskSQL
TotalsSELECT count(*), sum(x) FROM t
Conditional countcount(*) FILTER (WHERE cond)
Per groupGROUP BY col
Filter groupsHAVING count(*) > n
SubtotalsGROUP BY ROLLUP (col)
Named stepsWITH a AS (...), b AS (...) SELECT ...
TreesWITH RECURSIVE
Number rows per grouprow_number() OVER (PARTITION BY g ORDER BY t DESC)
Previous rowlag(col) OVER (ORDER BY t)
Share of totalx / sum(x) OVER ()

Exercise

Which query counts each user’s notes and the pinned ones in a single pass?

  1. Two queries joined with UNION
  2. SELECT user_id, count(*), count(pinned) FROM notes GROUP BY user_id
  3. SELECT user_id, count(*) FROM notes WHERE pinned GROUP BY user_id
  4. SELECT user_id, count(), count() FILTER (WHERE pinned) FROM notes GROUP BY user_id
Show answer

Answer: D. FILTER limits one aggregate to matching rows while the others still see the whole group. count(pinned) counts non-NULL values, which includes false.

Exercise

What is the difference between WHERE and HAVING?

  1. HAVING is the old name for WHERE
  2. WHERE filters rows before grouping; HAVING filters groups after the aggregates are computed
  3. WHERE works only with text columns
  4. HAVING is evaluated before WHERE
Show answer

Answer: B. Aggregates such as count(*) do not exist yet when WHERE runs, so conditions on them must go in HAVING.

Exercise

You need each note together with the total number of notes its author has written, without losing any rows. What fits best?

  1. count(*) OVER (PARTITION BY user_id)
  2. GROUP BY user_id
  3. DISTINCT ON (user_id)
  4. A recursive CTE
Show answer

Answer: A. A window function computes the per-user count but keeps every note row. GROUP BY would collapse the notes into one row per user.

Key takeaways

  • Aggregates turn many rows into one; FILTER computes several conditional aggregates in one pass.
  • GROUP BY makes groups, HAVING filters them; every selected column must be grouped or aggregated.
  • WITH names intermediate steps; WITH RECURSIVE walks hierarchies.
  • Window functions (OVER (...)) compute across related rows without collapsing them: rankings, previous values, running totals and shares.

FAQ

Is count(*) slow on big tables?

It reads the whole table (or a whole index) because Postgres keeps no stored row count, since different transactions may see different numbers of rows. For an estimate, read reltuples from pg_class; for exact counts on huge tables, keep a counter table.

What is the difference between count(*) and count(1)?

None in practice. Both count rows. count(col) is different: it skips NULLs.

Are CTEs slower than subqueries?

Not anymore. Since PostgreSQL 12, a CTE that is referenced once and has no side effects is inlined into the main query and optimised the same way as a subquery.

Can I use a window function in WHERE?

No, window functions are computed after WHERE and GROUP BY. Compute it in a subquery or CTE and filter in the outer query, for example SELECT * FROM (SELECT ..., row_number() OVER (...) AS nth FROM notes) s WHERE nth = 1.

How do I get the median?

Use the ordered-set aggregate percentile_cont(0.5) WITHIN GROUP (ORDER BY col). percentile_disc returns an actual value from the data instead of an interpolated one.

↑ ↓ navigate Enter open Esc close