Ledger + Graph Analytics

This module covers two production patterns that look simple but fail easily without strict query structure:

1. As-of ledger balances with correctness rules.
2. Reachability over transfer graphs with bounded recursion.

The two practice problems in this module are:

• ledger-balance-asof
• fraud-hop-network

Why this module matters

In event analytics, "close enough" SQL is usually fine. In finance and fraud workflows, "close enough" is wrong.

Common failure modes:

• counting pending or reversed ledger rows
• letting rows after the cutoff leak into balances
• exploring graph paths too deep
• returning duplicates for nodes reached by multiple paths

Your goal is to produce deterministic, auditable SQL that matches business rules exactly.

Learning goals

By the end, you should be able to:

• define output grain before writing SQL
• encode an inclusive as-of cutoff (<=) with status filters
• return zero-balance accounts safely using left joins + coalesce
• model bounded graph traversal with recursive CTEs
• deduplicate reachability by minimum hop depth
• verify edge cases with small audit queries

Mental model 1: as-of ledger balances

Say this sentence first:

One output row represents one account balance as of a specific cutoff date.

From that sentence, the query shape follows:

1. Start from accounts (because all accounts must be returned).
2. Left join ledger_entries (to keep accounts with no qualifying rows).
3. Sum only qualifying entries:
   • status = 'posted'
   • entry_ts <= cutoff
4. Convert null sums to zero.

Why filter-in-aggregate is safe here

Because we must include all accounts, filtering in where would drop accounts without qualifying entries. Instead, keep all joined rows and conditionally count eligible rows in sum(case ...).

This is the core pattern:

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

Worked micro-example

Given account 10 entries:

• 2025-01-10, +10000, posted -> include
• 2025-02-05, -2500, posted -> include
• 2025-03-01, -1200, pending -> exclude
• 2025-03-15, +700, reversed -> exclude

As-of balance:

• 10000 - 2500 = 7500

If account 30 has only non-posted rows, output must still include:

• (30, 0)

Mental model 2: bounded graph reachability

Say this sentence first:

One output row represents one reachable account with its minimum hop depth from any flagged source.

This gives the recursive plan:

1. Base case (hop_depth = 0):
   • all rows from flagged_accounts
2. Recursive step:
   • from each current account, follow outgoing edges
   • transfers.from_account -> transfers.to_account
   • increment depth by 1
3. Stop expansion once depth reaches 2.
4. Group by account and keep min(hop_depth).

Why min(hop_depth) is required

A node may be reached by multiple paths:

• direct path (1 hop)
• indirect path (2 hops)

We must keep the smallest depth for deterministic risk interpretation.

Cycle safety

Graphs can contain cycles (A -> B -> A). This module avoids infinite loops by:

• capping recursion (hop_depth < 2)
• aggregating to minimum depth after recursion

For deeper traversals in real systems, you typically also track visited sets.

End-to-end blueprint: as-of ledger

CREATE OR REPLACE VIEW result AS
SELECT
  a.id AS account_id,
  COALESCE(SUM(
    CASE
      WHEN e.status = 'posted'
       AND e.entry_ts <= DATE '2025-04-30'
      THEN e.amount_cents
      ELSE 0
    END
  ), 0) AS balance_cents
FROM accounts AS a
LEFT JOIN ledger_entries AS e
  ON e.account_id = a.id
GROUP BY a.id
ORDER BY a.id;

End-to-end blueprint: 2-hop fraud network

CREATE OR REPLACE VIEW result AS
WITH RECURSIVE hops AS (
  SELECT
    account_id,
    0 AS hop_depth
  FROM flagged_accounts

  UNION ALL

  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
)
SELECT
  account_id,
  MIN(hop_depth) AS hop_depth
FROM hops
GROUP BY account_id
ORDER BY hop_depth, account_id;

Determinism checklist

Before finalizing either query, verify:

• output grain is explicit and matches statement
• cutoff is inclusive where required (<=)
• ordering is explicit in final output
• dedup behavior is explicit (min(hop_depth) for graph)
• accounts with no qualifying ledger rows still appear with zero

Debugging checklist

If ledger results are wrong:

• Check whether status filter excludes pending/reversed rows.
• Check whether cutoff filter is inside aggregation logic.
• Check for accidental inner join that drops empty accounts.
• Validate one account by hand with a small where account_id = ... query.

If graph results are wrong:

• Confirm traversal follows outgoing edges only (from -> to).
• Confirm recursion expands only when hop_depth < 2.
• Confirm output deduplicates with min(hop_depth).
• Check if any row has hop_depth > 2 (should be none).

Production habits to keep

These patterns generalize:

• As-of finance snapshots: start from entity table, left join facts, conditional aggregate.
• Bounded graph investigations: recursive CTE with explicit depth cap + canonical dedup metric.

When correctness matters, favor explicitness over compact SQL.