Ledger and Graph Analytics: Preserve the Right Rows

The first module selected events and combined them into sessions. Now an investigation needs every account's balance and the accounts connected to a flagged source. A filter can remove a required account; multiple paths can duplicate a graph node. Correctness matters in event and ledger analytics alike.

Preparation and outcomes

Complete the event module first. You need joins, grouped aggregates, and CTEs. You will preserve empty accounts, trace a recursive working set, and distinguish termination from deduplication. Practice Ledger Balance, then Fraud Hop Network, then the checkpoint.

1. Start from the required population

One ledger output row represents every account as of 2025-04-30. entry_ts is a DATE here. Only posted entries on or before the date contribute; amounts are already signed integer cents.

account	date	amount	status	contribution
10	Apr 29	+900	posted	+900
10	Apr 30	-250	posted	-250
10	May 1	+400	posted	0
20	Apr 28	+700	pending	0
30	no entries	—	—	0

Expected rows: (10,650), (20,0), (30,0). Starting from accounts and left joining entries preserves 30 with a null-extended row. Preserve 20 too: it has entries, but none qualify.

Diagnose this wrong query fragment

FROM accounts AS a
LEFT JOIN ledger_entries AS e ON e.account_id = a.id
WHERE e.status = 'posted' AND e.entry_ts <= DATE '2025-04-30'

Account 20's only joined row fails the predicate. Account 30's null status also fails to make it true. COALESCE cannot restore a group already removed. Adding OR e.account_id IS NULL still fails for 20: it had a matching entry, so no null-extended row was created for it.

Two valid designs preserve the population:

1. Put eligibility in the join's ON clause, then use COALESCE(SUM(e.amount_cents), 0).
2. Join all entries and use a conditional aggregate:

COALESCE(SUM(CASE
  WHEN e.status = 'posted' AND e.entry_ts <= DATE '2025-04-30'
  THEN e.amount_cents
  ELSE 0
END), 0)

Choose one design and group by account ID. Do not sum distinct amounts: two separate posted entries with the same amount both count.

This simplified schema stores one status per entry, not a history of status changes. For a timestamp column, a date literal would normally mean midnight, not the entire day. An end-of-day timestamp report needs an explicit timezone and next-day exclusive boundary; that is separate from this DATE contract.

2. Trace graph reachability

One output row represents an account reachable in zero, one, or two outgoing transfers from any flagged seed, with its minimum hop depth. Reachability is a relationship to investigate, not proof that an account committed fraud.

With flagged A and edges A→B, A→C, B→C, C→A, C→D, D→E:

iteration	newly generated rows
base	(A,0)
expand depth 0	(B,1), (C,1)
expand depth 1	(C,2), (A,2), (D,2)
expand depth 2	none: expansion is forbidden

The final answer is A at 0, B and C at 1, D at 2. E needs three hops and is absent. A's cycle does not erase its depth 0. C's indirect discovery does not erase its depth 1. With multiple seeds, take the minimum over all their paths.

3. Reconstruct the recursive CTE

The base emits flagged IDs and integer depth 0. The recursive part joins the previous iteration's working rows to outgoing transfers:

SELECT t.to_account AS account_id, h.hop_depth + 1 AS hop_depth
FROM hops AS h
JOIN transfers AS t ON t.from_account = h.account_id
WHERE h.hop_depth < 2

Connect base and recursive queries using WITH RECURSIVE ... UNION ALL .... After recursion, group by account and take MIN(hop_depth).

The depth guard stops expansion and guarantees termination here. The final minimum combines discoveries; it does not stop the recursive computation. Replacing UNION ALL with UNION alone cannot terminate an unbounded cyclic walk: (A,0), (A,2), (A,4) remain distinct rows. An outer depth filter also cannot replace the guard inside recursion.

Even bounded recursion can generate many intermediate rows through duplicate transfers and branching paths. This small two-hop query is not a general scalable shortest-path implementation; deeper searches need a deliberate visited-state or cycle strategy.

4. Test variations before submitting

1. Add a second posted +900 entry to account 10 on Apr 29. The balance is 1550; equal amounts are separate facts.
2. Flag D as well as A. D becomes depth 0 and E becomes depth 1.
3. Remove every flagged seed. The graph result is empty; a row in accounts alone does not establish reachability.

Build each exercise from these rules without a complete answer to copy. Inspect intermediate rows before aggregating. Sort the final ledger by account ID and the graph by depth then account ID. A consumer of a view should also specify ORDER BY when it needs a particular presentation order.

Further reading

PostgreSQL table expressions explains join conditions versus later filters. Recursive CTE evaluation documents working sets and cycles involving depth.