Aggregation & Window Functions

Every dashboard number you have ever seen — total spend, monthly revenue, top customers — is an aggregate query. This is also the exact place where JPA stops being convenient and real Spring codebases drop to raw SQL, which makes this module the single biggest payoff of the whole track. And it contains the most expensive silent bug in reporting: a join that quietly doubles your totals.

The Goal

By the end of this module you can:

• Explain GROUP BY as folding rows — and recite the iron rule about what SELECT may mention
• Choose between COUNT(*) and COUNT(col) on purpose, knowing exactly where NULL changes the answer
• Place a filter in WHERE vs HAVING by where it sits in the evaluation order
• Spot and fix the join fan-out that corrupts SUMs, using a CTE to aggregate first
• Write window functions — totals, ranks, running sums — without collapsing the rows
• Say precisely why aggregation is where JPA taps out and native SQL takes over

The Lesson

Everything below runs on the simplified schema from module 01 — friend and expense. So your numbers match mine exactly, run Build This step 1 first: it resets both tables with a known seed (4 friends, 5 expenses, ₹13,650 total spend).

GROUP BY is a fold

SELECT so far has answered “show me rows.” Aggregation answers a different species of question: “show me one number about many rows.” Five expenses, three payers — “how much did each person pay?” has three answers, not five.

SELECT paid_by, SUM(amount_paise) AS paid_paise, COUNT(*) AS expenses
FROM expense
GROUP BY paid_by;

 paid_by | paid_paise | expenses
---------+------------+----------
       1 |    1140000 |        2
       2 |     105000 |        2
       3 |     120000 |        1

Picture it physically: PostgreSQL deals the 5 rows into piles by paid_by — Asha’s pile (2 rows), Rohit’s pile (2 rows), Darshan’s pile (1 row) — then each pile collapses into exactly one result row. Five rows in, three rows out. One row per distinct group key, always.

That collapse creates the iron rule: once rows are folded, a column like description has two values inside Asha’s pile — which one would PostgreSQL print? It refuses to guess:

SELECT paid_by, description, SUM(amount_paise) FROM expense GROUP BY paid_by;

ERROR:  column "expense.description" must appear in the GROUP BY clause
        or be used in an aggregate function

The iron rule: every column in SELECT is either in the GROUP BY, or wrapped in an aggregate. No third option. (Famous trap: old MySQL silently picked an arbitrary value instead of erroring — code “worked” and reported garbage. PostgreSQL’s refusal is a feature.)

The aggregates that matter

Two of those rows deserve a closer look.

COUNT(*) vs COUNT(col). Module 02 taught you NULL is “unknown,” and aggregates skip NULLs. That’s invisible until a LEFT JOIN (module 03) manufactures NULLs. Ask “how many expenses has each friend paid for?” — including Meera, who has paid for nothing:

SELECT f.name, COUNT(*) AS with_star, COUNT(e.id) AS with_col
FROM friend f
LEFT JOIN expense e ON e.paid_by = f.id
GROUP BY f.name
ORDER BY f.name;

  name   | with_star | with_col
---------+-----------+----------
 Asha    |         2 |        2
 Darshan |         1 |        1
 Meera   |         1 |        0
 Rohit   |         2 |        2

Meera’s LEFT JOIN row survives with every expense column NULL — that row is real, so COUNT(*) says 1. COUNT(e.id) skips the NULL and says 0, the true answer. A report that says everyone has at least one expense, built on COUNT(*), is wrong in a way nobody notices for months.

Types under aggregation. Your amount_paise is BIGINT — exact, the money rule since Core Java. SUM(bigint) returns numeric precisely so a year of UPI transactions can’t overflow mid-sum. AVG also returns numeric: AVG(amount_paise) over our five expenses is 273000.000000… — fine — but average a different set and you get fractional paise. Paise were the trick that kept money exact; AVG reintroduces fractions, so you choose the rounding (ROUND(AVG(amount_paise))) instead of letting a display layer truncate it differently on every screen.

WHERE filters rows, HAVING filters groups

Module 02 gave you the evaluation order. Aggregation adds two stops:

flowchart LR
    A["FROM and JOINs"] --> B["WHERE filters rows"] --> C["GROUP BY folds rows"] --> D["HAVING filters groups"] --> E["SELECT computes output"] --> F["ORDER BY then LIMIT"]

WHERE runs before the fold — it sees individual rows, so aggregates don’t exist yet. HAVING runs after — it sees one row per group and can use aggregates. Try to cheat and PostgreSQL tells you exactly which side of the fold you’re standing on:

SELECT paid_by FROM expense WHERE SUM(amount_paise) > 110000 GROUP BY paid_by;

ERROR:  aggregate functions are not allowed in WHERE

The legal version — “payers whose total crosses ₹1,100”:

SELECT f.name, SUM(e.amount_paise) AS paid_paise
FROM friend f
JOIN expense e ON e.paid_by = f.id
GROUP BY f.name
HAVING SUM(e.amount_paise) > 110000;

  name   | paid_paise
---------+------------
 Asha    |    1140000
 Darshan |     120000

Both clauses earn their place in one query: WHERE e.created_at >= '2026-06-02' throws out rows before the piles form (cheaper — fewer rows to fold), HAVING judges the finished piles. WHERE shrinks the input; HAVING prunes the output.

The trap: joins multiply, SUM amplifies

Module 03’s row-multiplication was cosmetic — duplicate rows on screen. Put an aggregate on top and it becomes wrong money.

Say expense shares exist (a junction table — expense_share(expense_id, friend_id, share_paise); module 05 designs it properly, Build This creates it inline). Someone “improves” the totals-per-payer query by joining the shares in:

SELECT f.name, SUM(e.amount_paise) AS paid_paise
FROM friend f
JOIN expense e ON e.paid_by = f.id
JOIN expense_share s ON s.expense_id = e.id
GROUP BY f.name;

  name   | paid_paise
---------+------------
 Asha    |    3420000
 Rohit   |     210000
 Darshan |     240000

Asha paid ₹11,400. The query says ₹34,200 — exactly 3×. Walk it: her dinner row matched 3 share rows, so the join emitted the dinner three times, each copy carrying the full 300000. Same for homestay. SUM happily added every copy: (3 × 300000) + (3 × 840000) = 3,420,000. The join fanned out, the aggregate amplified. No error, no warning — just a confident wrong number on a dashboard.

The fix is a rule worth tattooing: aggregate each table at its own grain first, then join the already-folded results.

CTEs — name your steps

A CTE (WITH) is a named subquery: build an intermediate result, give it a name, query it like a table. It’s how the fix above stays readable:

WITH paid AS (
  SELECT paid_by AS friend_id, SUM(amount_paise) AS paid_paise
  FROM expense
  GROUP BY paid_by
),
owed AS (
  SELECT friend_id, SUM(share_paise) AS owed_paise
  FROM expense_share
  GROUP BY friend_id
)
SELECT f.name,
       COALESCE(p.paid_paise, 0)                            AS paid_paise,
       COALESCE(o.owed_paise, 0)                            AS owed_paise,
       COALESCE(p.paid_paise, 0) - COALESCE(o.owed_paise, 0) AS net_paise
FROM friend f
LEFT JOIN paid p ON p.friend_id = f.id
LEFT JOIN owed o ON o.friend_id = f.id
ORDER BY net_paise DESC;

paid folds expenses to one row per payer. owed folds shares to one row per friend. Only then do they meet — every join key now matches at most one row on each side, so nothing multiplies. (LEFT JOIN + COALESCE so Meera, who paid nothing, still appears with 0 — modules 02 and 03 shaking hands.)

One performance footnote: since PostgreSQL 12, CTEs are inlined into the main query unless you say MATERIALIZED — use them freely for readability, they’re not a hidden cost.

Window functions — aggregate without collapsing

GROUP BY’s collapse is sometimes exactly wrong. “Show every expense and what fraction of total spend it was” needs all 5 rows plus a number computed across all of them. That’s a window function: an aggregate that looks over a “window” of related rows but keeps every row.

SELECT description, amount_paise,
       SUM(amount_paise) OVER ()                                   AS all_paise,
       ROUND(100.0 * amount_paise / SUM(amount_paise) OVER (), 1)  AS pct
FROM expense
ORDER BY pct DESC;

 description | amount_paise | all_paise | pct
-------------+--------------+-----------+------
 homestay    |       840000 |   1365000 | 61.5
 dinner      |       300000 |   1365000 | 22.0
 groceries   |       120000 |   1365000 |  8.8
 cab         |        60000 |   1365000 |  4.4
 snacks      |        45000 |   1365000 |  3.3

Five rows in, five rows out — and each one carries the grand total alongside. Empty OVER () means “window = everything.” PARTITION BY shrinks the window to a slice — GROUP BY’s grouping, minus the collapse:

SELECT description, amount_paise,
       SUM(amount_paise) OVER (PARTITION BY paid_by) AS payer_total
FROM expense;

Each row now shows its own amount next to its payer’s total — dinner shows 300000 | 1140000. (On the full schema, the same shape gives every expense its share of the group’s total: PARTITION BY group_id.)

Ranking — friends ordered by total paid, with an explicit rank. Windows run after GROUP BY in the evaluation order, so you can window over aggregates:

SELECT f.name, SUM(e.amount_paise) AS paid_paise,
       RANK() OVER (ORDER BY SUM(e.amount_paise) DESC) AS spend_rank
FROM friend f
JOIN expense e ON e.paid_by = f.id
GROUP BY f.name;

  name   | paid_paise | spend_rank
---------+------------+------------
 Asha    |    1140000 |          1
 Darshan |     120000 |          2
 Rohit   |     105000 |          3

(RANK leaves gaps after ties — 1, 1, 3; DENSE_RANK doesn’t — 1, 1, 2; ROW_NUMBER breaks ties arbitrarily. Interviewers ask this trio by name.)

Running totals — ORDER BY inside OVER makes the window grow row by row: “everything up to and including me”:

SELECT created_at::date AS day, description, amount_paise,
       SUM(amount_paise) OVER (ORDER BY created_at) AS running_paise
FROM expense
ORDER BY created_at;

    day     | description | amount_paise | running_paise
------------+-------------+--------------+---------------
 2026-06-01 | dinner      |       300000 |        300000
 2026-06-02 | cab         |        60000 |        360000
 2026-06-03 | homestay    |       840000 |       1200000
 2026-06-04 | groceries   |       120000 |       1320000
 2026-06-05 | snacks      |        45000 |       1365000

That’s the “spend over time” chart in every fintech app, in one query.

Which tool when:

Where JPA taps out

Everything before this module, JPA handles politely: derived queries, findBy…, fetch joins. Aggregation is where it gets painful. Method names can’t express GROUP BY at all. JPQL has SUM and GROUP BY, but the results come back as Object[] or force DTO-projection ceremony. And window functions do not exist in JPQL — there is no @ManyToOne for a running total.

So real Spring codebases do what you’d see in any mature repo: reporting queries become @Query(nativeQuery = true) or JdbcTemplate with plain SQL — exactly the SQL on this page. This module is the single biggest reason backend engineers must know SQL beyond what Spring Boot 04 generates: Hibernate writes your CRUD, but you write the dashboard. Week 5 exists for this.

Check The Concept

How This Shows Up At Work

• The inflated-revenue incident. A finance dashboard shows revenue 2× reality; the company nearly reports the wrong number upward. Root cause: someone joined an order-items table into the per-order revenue query — fan-out, then SUM. The engineer who has personally watched a total triple finds this in minutes; everyone else audits the payment gateway for a week.
• The COUNT(*) report nobody questioned. “Active users per plan” counted LEFT-JOIN NULL rows as activity for months. The fix was three characters — COUNT(u.id) — finding it required knowing the NULL rule cold.
• The interview staples. “WHERE vs HAVING” and “second-highest / rank within department” are in practically every Indian backend interview loop. The second one is unanswerable without window functions — RANK() OVER (PARTITION BY …) is the expected vocabulary.
• The code-review comment that marks seniority. “You’re loading all expenses into Java and summing in a loop — push this to SQL with GROUP BY, it’s one round trip instead of ten thousand rows.” Being the person who writes that comment, with the query attached, is a signal.
• The reporting endpoint. Sooner or later every Spring service grows a /stats endpoint, and that PR is native SQL. Reviewing it is impossible on JPA knowledge alone.

Build This

You’re building SplitEase’s settlement report in psql, then breaking it the two ways it breaks in production. Type everything.

1. Connect (psql -U postgres -d splitease_sql) and reset to a known seed:

DROP TABLE IF EXISTS expense_share;
DROP TABLE IF EXISTS expense;
DROP TABLE IF EXISTS friend;

CREATE TABLE friend (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE expense (
  id BIGSERIAL PRIMARY KEY,
  description TEXT NOT NULL,
  amount_paise BIGINT NOT NULL CHECK (amount_paise > 0),
  paid_by BIGINT NOT NULL REFERENCES friend(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO friend (name) VALUES ('Asha'), ('Rohit'), ('Darshan'), ('Meera');

INSERT INTO expense (description, amount_paise, paid_by, created_at) VALUES
  ('dinner',    300000, 1, '2026-06-01'),
  ('cab',        60000, 2, '2026-06-02'),
  ('homestay',  840000, 1, '2026-06-03'),
  ('groceries', 120000, 3, '2026-06-04'),
  ('snacks',     45000, 2, '2026-06-05');

2. Totals per payer — your first fold. Run the JOIN + GROUP BY query from the lesson (f.name, SUM(e.amount_paise), COUNT(*)). Confirm: Asha 1140000, Rohit 105000, Darshan 120000 — and Meera absent (INNER JOIN; you know why from module 03).

3. Add HAVING SUM(e.amount_paise) > 110000. Rohit disappears. Now try moving that condition into WHERE and read the error — you’ll hit it again in step 8.

4. Create the shares junction (a preview of module 05, which designs it from requirements):

CREATE TABLE expense_share (
  expense_id BIGINT NOT NULL REFERENCES expense(id),
  friend_id  BIGINT NOT NULL REFERENCES friend(id),
  share_paise BIGINT NOT NULL,
  PRIMARY KEY (expense_id, friend_id)
);

INSERT INTO expense_share VALUES
  (1, 1, 100000), (1, 2, 100000), (1, 3, 100000),   -- dinner, 3 ways
  (2, 1,  30000), (2, 2,  30000),                   -- cab, 2 ways
  (3, 1, 280000), (3, 2, 280000), (3, 3, 280000),   -- homestay, 3 ways
  (4, 3,  60000), (4, 4,  60000),                   -- groceries, 2 ways
  (5, 2,  22500), (5, 3,  22500);                   -- snacks, 2 ways

5. The deliberately wrong query. Run the lesson’s triple-join SUM (friend → expense → expense_share, SUM(e.amount_paise) grouped by name). Compare with step 2: Asha 3420000 vs 1140000. Count her share rows (3 for dinner, 3 for homestay) and confirm the multiplier by hand. This is the moment to remember at work.

6. Fix it with the CTE. Type the full WITH paid … owed … settlement query from the lesson. Expected:

  name   | paid_paise | owed_paise | net_paise
---------+------------+------------+-----------
 Asha    |    1140000 |     410000 |    730000
 Darshan |     120000 |     462500 |   -342500
 Rohit   |     105000 |     432500 |   -327500
 Meera   |          0 |      60000 |    -60000

Sanity check the way a fintech engineer would: the four nets sum to zero — every rupee owed is a rupee someone is owed. If your nets don’t cancel, your aggregation is broken somewhere.

7. One window report. Run the running-total query (SUM(amount_paise) OVER (ORDER BY created_at)). Confirm the last row reads 1365000 — and that all five rows survived. That’s the difference from every GROUP BY you ran above.

8. Break it on purpose — the iron rule. Run SELECT paid_by, description, SUM(amount_paise) FROM expense GROUP BY paid_by;. Read the error: PostgreSQL names the exact column and offers the only two legal fates — must appear in the GROUP BY clause or be used in an aggregate function. Fix it both ways: add description to GROUP BY (the grain changes — now it’s per payer per description) or wrap it (MAX(description) — legal, but you’re choosing an arbitrary representative; be honest about that).

9. Break it on purpose — the NULL gap. Run the COUNT(*) vs COUNT(e.id) LEFT JOIN query from the lesson. Stare at Meera’s row: 1 vs 0. Say out loud which number is true and why the other exists — the LEFT JOIN manufactured a row whose expense columns are NULL; COUNT(*) counts the row, COUNT(e.id) skips the NULL. If you can explain that gap, modules 02, 03, and 04 just clicked together.

Where to Practice

Check Yourself

1. Five rows, GROUP BY produces three piles — how many result rows, and what’s the rule?
2. The iron rule: what are the only two legal fates for a column in the SELECT of a grouped query?
3. COUNT(*) vs COUNT(col) — when do they differ, and which join makes the difference appear?
4. Which clause runs before the fold and which after — and which one is allowed to use aggregates?
5. A SUM came back exactly 3× too big after a join was added. What happened, and what’s the shape of the fix?
6. SUM(amount_paise) over BIGINT paise returns what type, and why is that deliberate?
7. What does PARTITION BY give you that GROUP BY can’t?
8. Name the three ranking functions and how they treat ties.

Explain it out loud: Explain to an empty chair why joining expense_share into the totals-per-payer query tripled Asha’s total — walk the dinner row through the join, count the copies, show where SUM went wrong, and state the aggregate-first-then-join fix. If you can’t reproduce the 3,420,000 by hand, redo Build This step 5.

Still Unclear?

Here is a PostgreSQL query with WHERE, GROUP BY, HAVING, a window function,
and ORDER BY: [paste one of yours]. Walk me through the evaluation order
stop by stop, showing what the intermediate row set looks like at each stage.
Then give me a new query and make me narrate the stages myself.

Invent a small two-table scenario (orders and order_items) where joining
before aggregating silently doubles a revenue total. Show me the wrong query
and its output — then make ME write the CTE fix and check it. Don't fix it
for me.

Quiz me with 5 reporting questions on a friends/expenses schema where I must
choose: GROUP BY, window function, or both. After each answer, tell me what
breaks if I picked the other tool.

Why AI Can’t Do This For You

AI will generate a plausible aggregation query for any prompt — and “plausible” is the problem. The fan-out bug produces no error, no warning, just a confident wrong number; the only check is a human who knows the data’s grain and asks “should these nets sum to zero?” AI doesn’t know your tables’ cardinalities or that Asha’s total smells 3× too big. You do — if you’ve watched it happen in psql.

And when the dashboard number is challenged in a meeting, “the AI wrote the query” is not an answer. The person who can walk the dinner row through the join, copy by copy, owns the number — and owning numbers is most of what senior backend engineers are paid for.

Module done? Add it to today’s tracker