Aggregates, GROUP BY, and Window Functions

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Aggregates, GROUP BY, and Window Functions
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

Aggregate functions collapse many rows into summaries, and `GROUP BY` calculates one summary per group. `WHERE` filters before grouping; `HAVING` filters groups afterward. Window functions calculate ranks and running totals without collapsing detail rows.

Connect it to a real scenario

Calculate lesson count and total duration per tutorial, then rank tutorials by duration. When row multiplication is possible, aggregate the child table first and only then join other relationships.

WHERE filters rows
HAVING filters groups
Window functions retain detail rows

Try the working example

sql

WITH totals AS (
  SELECT t.tutorial_id, t.title,
         count(l.lesson_id) AS lesson_count,
         coalesce(sum(l.duration_minutes), 0) AS total_minutes
  FROM app.tutorials t
  LEFT JOIN app.lessons l USING (tutorial_id)
  GROUP BY t.tutorial_id, t.title
)
SELECT *, dense_rank() OVER (ORDER BY total_minutes DESC) AS duration_rank
FROM totals
ORDER BY duration_rank, title;

You should see

The query returns one summary row per tutorial plus its duration rank.

5-minute try-it

Count enrollments per user and use HAVING to keep only users with at least three.

One important caution

You cannot freely select columns outside the grouping. Establish a key dependency or aggregate the value.

PostgreSQL — Aggregate Functions — PostgreSQL Global Development Group

Easy traps

You cannot freely select columns outside the grouping. Establish a key dependency or aggregate the value.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Count enrollments per user and use HAVING to keep only users with at least three.

You'll know it worked when: The query returns one summary row per tutorial plus its duration rank.