Ledger and graph analytics

Two row-count hazards:

• Required accounts disappear after filtering.
• Graph nodes appear several times through different paths.

State the required output population before writing SQL.

Predict the balance rows

• Account 10: posted +900 and -250 before/on cutoff; +400 afterward.
• Account 20: pending +700 only.
• Account 30: no entries.

Which accounts appear, and with what balances?

Preserve every account

Answers: (10,650), (20,0), (30,0).

• Start from accounts and left join entries.
• Put eligibility in ON, or use conditional aggregation.
• Use COALESCE for a null sum.

A later WHERE e.status = 'posted' removes accounts 20 and 30. COALESCE cannot recover a group already removed.

Trace two hops

Seed A; edges A→B, A→C, B→C, C→A, C→D, D→E.

Predict the final minimum depth of each account. Is E included?

Separate stopping from deduplication

Final depths: A=0, B=1, C=1, D=2. E is absent.

• Recursive WHERE hop_depth < 2 stops expansion.
• Final GROUP BY account_id with MIN(hop_depth) combines paths.
• Final aggregation does not prevent infinite recursion.
• UNION alone does not merge (A,0) with (A,2).

Transfer checks

• Duplicate a posted amount: it must count twice.
• Flag D too: D becomes depth 0 and E depth 1.
• Remove all seeds: graph output becomes empty.
• Ledger dates are DATE values; a timestamp day-end boundary needs another contract.

Complete both exercises, then explain your choices in the checkpoint.