Capstone: SplitEase, Interrogated

Your splitease-api computes balances by loading every expense into Java and looping — LedgerService.balances() works, and you were proud of it. Tonight you compute the same numbers in pure SQL, in one query, without a single object leaving the database — and discover that PostgreSQL could have been doing the heavy lifting all along. This is the assembly module: almost no new theory, seven real business questions, and every module in this track earning its keep.

The Goal

By the end of this module you can:

• Answer seven real SplitEase product questions in pure SQL against the full schema — balances, deadbeats, biggest expenses, monthly trends, shares, timelines, settle-up inputs
• Replace a Java loop over findAll() with one aggregation query, and say why that matters at 200,000 rows
• Use DISTINCT ON — the PostgreSQL idiom for “top row per group”
• Prove an index works with EXPLAIN ANALYZE before/after numbers, not faith
• Decide, query by query, what belongs in @Query, what in derived methods, and what stays in the Java service — and defend each call

The Lesson

Nothing new gets taught at length today. Module 03 gave you joins, 04 gave you GROUP BY and windows, 05 gave you this schema, 06 gave you the lie detector. Today you compose them. For each question below: read the business question first, decide which tools it needs, try to write it yourself, then compare.

The setup — two groups, seven expenses, one deterministic seed

You’re on the full module-05 schema in splitease_sql. Reset to a known state so your outputs match the ones printed here exactly:

TRUNCATE expense_share, expense, group_member, expense_group, friend
  RESTART IDENTITY CASCADE;

INSERT INTO friend (name) VALUES ('Asha'), ('Rohit'), ('Darshan'), ('Meera');
INSERT INTO expense_group (name) VALUES ('Goa Trip'), ('Flat 402');

INSERT INTO group_member VALUES
  (1, 1), (1, 2), (1, 3),        -- Goa Trip: Asha, Rohit, Darshan
  (2, 2), (2, 3), (2, 4);        -- Flat 402: Rohit, Darshan, Meera

INSERT INTO expense (group_id, description, amount_paise, paid_by, created_at) VALUES
  (1, 'dinner',      300000, 1, '2026-04-10'),
  (1, 'cab',          60000, 2, '2026-04-11'),
  (1, 'homestay',    840000, 1, '2026-04-12'),
  (1, 'parasailing', 450000, 3, '2026-05-02'),
  (2, 'groceries',   180000, 2, '2026-05-05'),
  (2, 'wifi',         99900, 3, '2026-05-07'),
  (2, 'electricity', 210000, 3, '2026-06-03');

INSERT INTO expense_share VALUES
  (1, 1, 100000), (1, 2, 100000), (1, 3, 100000),   -- dinner, 3 ways
  (2, 2,  30000), (2, 3,  30000),                    -- cab: Asha skipped it
  (3, 1, 280000), (3, 2, 280000), (3, 3, 280000),   -- homestay, 3 ways
  (4, 1, 150000), (4, 2, 150000), (4, 3, 150000),   -- parasailing, 3 ways
  (5, 2,  60000), (5, 3,  60000), (5, 4,  60000),   -- groceries, 3 ways
  (6, 2,  33300), (6, 3,  33300), (6, 4,  33300),   -- wifi, 3 ways
  (7, 2,  70000), (7, 3,  70000), (7, 4,  70000);   -- electricity, 3 ways

RESTART IDENTITY CASCADE rewinds the BIGSERIAL counters and truncates everything that references these tables — that’s why the ids in the inserts are predictable. Note Meera: a Flat 402 member who shares costs but has never paid for anything. She matters twice below.

Question 1 — who owes what, in each group

The business question: for every member of every group, paid minus owed. Positive means the group owes them; negative means they owe the group. This is the heart of SplitEase — and it is exactly what LedgerService.balances() does with a Java loop.

The tools: two separate aggregations — total paid per friend per group (from expense), total owed per friend per group (from expense_share joined up to expense for the group). You cannot do both in one GROUP BY over one join — joining shares to expenses and summing amount_paise would count the dinner three times, once per share row. Two CTEs, then a join. And it must be a LEFT JOIN from the membership list, with COALESCE — module 03’s lesson — or anyone who never paid silently vanishes.

WITH paid AS (
  SELECT group_id, paid_by AS friend_id, SUM(amount_paise) AS paid_paise
  FROM expense
  GROUP BY group_id, paid_by
),
owed AS (
  SELECT e.group_id, es.friend_id, SUM(es.share_paise) AS owed_paise
  FROM expense_share es
  JOIN expense e ON e.id = es.expense_id
  GROUP BY e.group_id, es.friend_id
)
SELECT g.name AS group_name,
       f.name AS friend,
       COALESCE(p.paid_paise, 0) - COALESCE(o.owed_paise, 0) AS net_paise
FROM group_member gm
JOIN expense_group g ON g.id = gm.group_id
JOIN friend f        ON f.id = gm.friend_id
LEFT JOIN paid p ON p.group_id = gm.group_id AND p.friend_id = gm.friend_id
LEFT JOIN owed o ON o.group_id = gm.group_id AND o.friend_id = gm.friend_id
ORDER BY g.name, net_paise DESC;

 group_name | friend  | net_paise
------------+---------+-----------
 Flat 402   | Darshan |    146600
 Flat 402   | Rohit   |     16700
 Flat 402   | Meera   |   -163300
 Goa Trip   | Asha    |    610000
 Goa Trip   | Darshan |   -110000
 Goa Trip   | Rohit   |   -500000
(6 rows)

Each group sums to zero — the capstone invariant from Core Java, now enforced by arithmetic over BIGINT paise. Asha is owed Rs 6,100 from Goa; Meera owes Rs 1,633 to the flat. Your LedgerService.balances() loads every expense entity, every payer, every sharer set into the JVM heap to produce these six numbers. PostgreSQL just did it without anything leaving the database. Sit with that.

Question 2 — who has never paid for anything

The business question: the freeloader report. Which friends appear in zero expenses as payer?

The tools: module 03’s signature move — LEFT JOIN then keep the rows where the right side came back NULL.

SELECT f.name
FROM friend f
LEFT JOIN expense e ON e.paid_by = f.id
WHERE e.id IS NULL;

 name
-------
 Meera
(1 row)

The trap to remember: WHERE e.id IS NULL filters after the left join preserved her row. Write it as an INNER JOIN and Meera doesn’t show up as the answer — she doesn’t show up at all, and the query returns zero rows for a question whose answer is “Meera.”

Question 3 — each group’s most expensive expense, with payer name

The business question: the “biggest damage” card on each group’s screen — description, amount, and who fronted it.

The tools: “top row per group” — a classic. The portable answer is a subquery matching each group’s MAX(amount_paise). PostgreSQL has a cleaner idiom worth learning by name: DISTINCT ON (cols) keeps only the first row for each distinct value of cols — and “first” is decided entirely by the ORDER BY. Sort by group, then amount descending, and the survivor per group is the most expensive expense:

SELECT DISTINCT ON (e.group_id)
       g.name AS group_name, e.description, e.amount_paise, f.name AS paid_by
FROM expense e
JOIN expense_group g ON g.id = e.group_id
JOIN friend f        ON f.id = e.paid_by
ORDER BY e.group_id, e.amount_paise DESC;

 group_name | description | amount_paise | paid_by
------------+-------------+--------------+---------
 Goa Trip   | homestay    |       840000 | Asha
 Flat 402   | electricity |       210000 | Darshan
(2 rows)

Two rules: the ORDER BY must start with the DISTINCT ON columns, and whatever you sort by next decides who survives. It’s PostgreSQL-only — on MySQL you’d reach for a window function with ROW_NUMBER() — but on the dialect your project actually runs, this is the idiom reviewers expect.

Question 4 — monthly spend per group

The business question: the spending trend chart. How much did each group burn per month?

The tools: date_trunc collapses every timestamp in a month to that month’s first instant, which makes it groupable — then module 04’s plain GROUP BY.

SELECT g.name AS group_name,
       date_trunc('month', e.created_at)::date AS month,
       SUM(e.amount_paise) AS total_paise
FROM expense e
JOIN expense_group g ON g.id = e.group_id
GROUP BY g.name, date_trunc('month', e.created_at)
ORDER BY g.name, month;

 group_name |   month    | total_paise
------------+------------+-------------
 Flat 402   | 2026-05-01 |      279900
 Flat 402   | 2026-06-01 |      210000
 Goa Trip   | 2026-04-01 |     1200000
 Goa Trip   | 2026-05-01 |      450000
(4 rows)

April in Goa: Rs 12,000. The ::date cast just trims the time-of-day noise for display. One honest note: months with zero expenses produce no row at all — GROUP BY can’t group rows that don’t exist. Charts that need gap-filling join against generate_series of months; park that thought, you’ll meet generate_series again in Make It Fast.

Question 5 — each friend’s share of their group’s total, as a percentage

The business question: “you’re carrying 34% of this trip” — each member’s owed total as a percentage of the whole group’s spend.

The tools: module 04’s closing move — a window function over an aggregate. SUM(es.share_paise) is each friend’s slice; SUM(SUM(es.share_paise)) OVER (PARTITION BY g.name) is the group’s total computed across the already-grouped rows, without collapsing them.

SELECT g.name AS group_name,
       f.name AS friend,
       SUM(es.share_paise) AS owed_paise,
       ROUND(100.0 * SUM(es.share_paise)
             / SUM(SUM(es.share_paise)) OVER (PARTITION BY g.name), 1) AS pct
FROM expense_share es
JOIN expense e       ON e.id = es.expense_id
JOIN expense_group g ON g.id = e.group_id
JOIN friend f        ON f.id = es.friend_id
GROUP BY g.name, f.name
ORDER BY g.name, pct DESC, f.name;

 group_name | friend  | owed_paise | pct
------------+---------+------------+------
 Flat 402   | Darshan |     163300 | 33.3
 Flat 402   | Meera   |     163300 | 33.3
 Flat 402   | Rohit   |     163300 | 33.3
 Goa Trip   | Darshan |     560000 | 33.9
 Goa Trip   | Rohit   |     560000 | 33.9
 Goa Trip   | Asha    |     530000 | 32.1
(6 rows)

Asha’s 32.1% is the cab she skipped, made visible. The nested SUM(SUM(...)) looks illegal the first time — inner SUM is the group aggregate, outer SUM ... OVER is the window running across those aggregated rows. The 100.0 (not 100) forces numeric division; integer division would round everything to garbage before ROUND ever ran.

Question 6 — one friend’s running balance over time

The business question: Darshan’s balance graph — after every expense he touched, where did he stand overall?

The tools: per-event deltas (paid amount if he paid, minus his share if he had one), then module 04’s SUM(...) OVER (ORDER BY ...) to accumulate them chronologically.

WITH my_moves AS (
  SELECT e.created_at, e.description,
         CASE WHEN e.paid_by = f.id THEN e.amount_paise ELSE 0 END
           - COALESCE(es.share_paise, 0) AS delta_paise
  FROM expense e
  JOIN friend f ON f.name = 'Darshan'      -- one-row join: pins f to one friend
  LEFT JOIN expense_share es
         ON es.expense_id = e.id AND es.friend_id = f.id
  WHERE e.paid_by = f.id OR es.friend_id IS NOT NULL
)
SELECT created_at::date AS day, description, delta_paise,
       SUM(delta_paise) OVER (ORDER BY created_at) AS running_paise
FROM my_moves;

    day     | description | delta_paise | running_paise
------------+-------------+-------------+---------------
 2026-04-10 | dinner      |     -100000 |       -100000
 2026-04-11 | cab         |      -30000 |       -130000
 2026-04-12 | homestay    |     -280000 |       -410000
 2026-05-02 | parasailing |      300000 |       -110000
 2026-05-05 | groceries   |      -60000 |       -170000
 2026-05-07 | wifi        |       66600 |       -103400
 2026-06-03 | electricity |      140000 |         36600
(7 rows)

Goa dug him Rs 4,100 deep; paying for parasailing clawed most of it back; flat bills pulled him positive by June. Cross-check the last row against question 1: his Goa net (-110000) plus his Flat 402 net (+146600) is exactly 36600. When two different queries agree on the same number, your mental model of the data is probably right — make this kind of cross-check a habit.

Question 7 — the settle-up input

The business question: Goa Trip is over. Who pays whom to zero everything out? SQL’s job is the input: the net balances, ordered.

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

 friend  | net_paise
---------+-----------
 Asha    |    610000
 Darshan |   -110000
 Rohit   |   -500000
(3 rows)

Now the honest part: the greedy matching itself — biggest debtor pays biggest creditor, repeat until everyone is at zero — stays in Java. You built that algorithm twice already: with a heap in DSA and in the Core Java capstone, and LedgerService.settleUp() runs it today. Could SQL do it? Technically, with a recursive CTE that mutates state across iterations — and it would be unreadable, untestable, and impossible to unit-test against your hand-worked example. The dividing line: SQL is unbeatable at set-shaped work (filter, join, aggregate whole tables), and miserable at decision-shaped work (loop, mutate, branch until a condition holds). Settle-up is a loop with intermediate state. So the contract is:

flowchart LR
    A["PostgreSQL aggregates the ledger into net balances"] --> B["LedgerService runs greedy matching in Java"]
    B --> C["API returns the payment plan"]

SQL prepares, the service decides. For this data the service emits two payments: Rohit pays Asha Rs 5,000, Darshan pays Asha Rs 1,100 — same numbers your capstone produced, now fed by one query instead of findAll().

Make It Fast

Seven rows prove correctness, not speed. Build a hostile dataset with generate_series — 1,000 synthetic friends in a third group, 200,000 expenses, two shares each:

INSERT INTO friend (name)
SELECT 'loadtest_' || i FROM generate_series(1, 1000) i;          -- ids 5..1004

INSERT INTO expense_group (name) VALUES ('Load Test');             -- id 3

INSERT INTO group_member (group_id, friend_id)
SELECT 3, id FROM friend WHERE name LIKE 'loadtest_%';

INSERT INTO expense (group_id, description, amount_paise, paid_by, created_at)
SELECT 3, 'synthetic ' || i,
       100 + (random() * 100000)::bigint,
       5 + (i % 1000),
       now() - (i || ' minutes')::interval
FROM generate_series(1, 200000) i;

INSERT INTO expense_share (expense_id, friend_id, share_paise)
SELECT id, paid_by, amount_paise / 2
FROM expense WHERE group_id = 3;

INSERT INTO expense_share (expense_id, friend_id, share_paise)
SELECT id, 5 + ((paid_by - 4) % 1000), amount_paise - amount_paise / 2
FROM expense WHERE group_id = 3;

(Note the shares: amount / 2 plus amount - amount / 2 — the two halves always sum exactly to the amount, even when it’s odd. Integer paise discipline, even in fake data.)

Now run question 7 — Goa Trip’s balances, four real expenses — with the lie detector on:

EXPLAIN ANALYZE <the question 7 query>;

Buried in the plan, twice (once per CTE):

->  Seq Scan on expense  (actual time=0.014..21.882 rows=4 loops=1)
      Filter: (group_id = 1)
      Rows Removed by Filter: 200003
...
Execution Time: 47.310 ms

Read it like module 06 taught you: PostgreSQL touched all 200,007 expense rows and threw away 200,003 of them — to find four. Goa Trip’s screen is now paying rent for the load-test group’s data. And here is the famous trap, worth its own sentence: PostgreSQL does not automatically index foreign key columns. REFERENCES expense_group(id) created a constraint, not an index. Add the two indexes module 06 would prescribe:

CREATE INDEX idx_expense_group ON expense (group_id);
CREATE INDEX idx_share_friend  ON expense_share (friend_id);

Rerun the EXPLAIN ANALYZE:

->  Index Scan using idx_expense_group on expense
      (actual time=0.031..0.038 rows=4 loops=1)
      Index Cond: (group_id = 1)
...
Execution Time: 0.493 ms

Roughly 100x, from one line of DDL (your exact numbers will differ — what must match is the plan shape: Seq Scan with a six-digit Rows Removed by Filter becoming an Index Scan that touches four rows). The second index does the same job for question 6 — rerun Darshan’s timeline with EXPLAIN ANALYZE before and after and watch the scan on expense_share flip. One caveat for honesty: question 1 without a group filter still seq-scans, correctly — it genuinely needs every row, and no index fixes a query that reads the whole table. Indexes reward selectivity, and module 06 already told you so.

The JPA Reckoning

You now hold both ends: the Java service from Spring Boot 06 and the SQL that outclasses parts of it. So where does each query live in splitease-api? The JPA module gave you three tools — derived methods, @Query, and plain Java in the service. Here is the judgment call, query by query:

The pattern behind the table: if the answer is a set computed from sets, push it down to the database. If the answer requires iterating with intermediate decisions, keep it in the service. And one rule from the Spring track still binds: native aggregation queries bypass entities entirely, so they return projections (interfaces or records), never half-hydrated @Entity objects. The day you replace balances()’s loop with the native query, keep the old Java version in a test as the oracle — two implementations agreeing on your hand-worked Goa numbers is the cheapest correctness proof you will ever get.

Check The Concept

The Definition of Done

This capstone is done when every box is true — not skimmed, true:

• All seven ladder queries run against your seeded splitease_sql and produce the outputs shown (the seed is deterministic; differences mean a bug, find it)
• Question 1’s six balances sum to zero within each group, and you checked
• Question 6’s final running balance equals Darshan’s two group nets added together, and you checked
• The load-test seed is in, and you have the EXPLAIN ANALYZE output from before AND after the two indexes — Rows Removed by Filter: 200003 becoming an Index Cond
• You can explain every query line by line out loud — every join’s direction, every COALESCE, why 100.0 not 100, what the OVER clause partitions
• You have decided, for each row of the JPA Reckoning table, whether you agree — and written the native balance @Query into ExpenseRepository as proof

Check Yourself

1. Why is the group table called expense_group, and what happens if you try CREATE TABLE group (...)?
2. Why can’t question 1 be a single GROUP BY over expense joined to expense_share — what gets double-counted?
3. The freeloader query uses LEFT JOIN ... WHERE e.id IS NULL. What does that NULL actually mean, mechanically?
4. SUM(SUM(share_paise)) OVER (PARTITION BY g.name) — why is an aggregate inside a window function legal here?
5. What is the MySQL-friendly alternative to DISTINCT ON for “top row per group”?
6. PostgreSQL created no index when you declared group_id BIGINT REFERENCES expense_group(id). What DID it create, and why is the distinction expensive?
7. Two users hit settle-up for Goa Trip at the same moment, and a third inserts an expense between the balance query and the response. Which module 07 tool guarantees both settle-up readers compute from a consistent snapshot?
8. Why does the load-test share seeding use amount / 2 and amount - amount / 2 instead of amount / 2 twice?

Explain it out loud: Walk an empty chair through what happens when SplitEase shows the Goa Trip balance screen — twice. Version one: the current LedgerService.balances() (which entities load, how many queries Hibernate fires, where the math runs). Version two: the native query (what PostgreSQL does, which index it touches, what crosses the wire). Then say which you’d ship and why. If the two versions sound the same coming out of your mouth, redo the module.

Still Unclear?

Here is a SQL query I wrote against my SplitEase schema: [paste one of the seven].
Do not rewrite it. Interrogate me: ask me what each join does, why each
COALESCE exists, and what breaks if I remove each clause. Correct me only
after I commit to an answer.

Explain the boundary between SQL and application code using my settle-up
feature: net balances computed in PostgreSQL, greedy matching in Java.
Give me three more real-world features and make me classify each side of
them as database work or service work, then grade my reasoning.

Here are my EXPLAIN ANALYZE outputs before and after adding an index:
[paste both]. Walk me through every line that changed and quiz me on what
each number means. Then describe a query where adding this same index
would change nothing, and ask me why.

Why AI Can’t Do This For You

AI writes any single query in this ladder flawlessly — ask for “monthly spend per group in PostgreSQL” and the date_trunc appears in two seconds. That was never the skill. The skill is the boundary decision: knowing that balances belong in one aggregation query but settle-up matching belongs in the service, that DISTINCT ON is fine because your project is married to PostgreSQL, that the all-groups query seq-scans correctly while the one-group query seq-scanning is a fire. Those calls need the whole system in your head — schema, service, traffic shape — and AI holds only the sentence you typed.

And when the balance endpoint takes nine seconds at 2am because the load-test group’s data is bleeding into everyone’s queries, nobody is prompting anything. Someone opens psql, runs EXPLAIN ANALYZE, reads Rows Removed by Filter: 200003, and knows. You just became that someone — across seven queries, one index, and a judgment table you can defend line by line.

Module done? Add it to today’s tracker