Ledger + Graph Analytics

• Correctness over convenience
• Deterministic outputs
• Exact business-rule encoding

Problem 1: As-Of Ledger Balance

Target grain:

• one row per account_id
• balance as of 2025-04-30

Rules:

• include only status = 'posted'
• include only entry_ts <= DATE '2025-04-30'
• include all accounts, even zero-balance accounts
• order by account_id

Ledger Query Shape

1. Start from accounts
2. LEFT JOIN ledger_entries
3. Conditional SUM(CASE ...)
4. COALESCE(..., 0)
5. GROUP BY a.id

Why this shape:

• preserves accounts without qualifying entries
• prevents accidental row loss from WHERE filters

Problem 2: Fraud Hop Network

Target grain:

• one row per reachable account
• minimum hop depth from any flagged account

Rules:

• depth 0 = flagged accounts
• follow outgoing edges only (from_account -> to_account)
• cap traversal at 2 hops
• dedup with smallest hop depth
• order by hop_depth, then account_id

Recursive CTE Shape

hops CTE:

• base: flagged accounts at depth 0
• recursive: expand to neighbors with depth + 1
• stop expansion with hop_depth < 2

Final projection:

• GROUP BY account_id
• MIN(hop_depth)

Failure Modes

Ledger:

• counting pending / reversed
• applying cutoff after aggregation
• using inner join and losing zero-balance accounts

Graph:

• traversing incoming edges by mistake
• no depth cap
• duplicates without MIN(hop_depth)

Verification Quick Checks

• SELECT * FROM result ORDER BY ... is deterministic.
• No ledger account missing from output.
• No graph row has hop_depth > 2.
• Known sample account balances and hop depths match hand calculation.