Fraud Hop Network (2 Hops)

An investigation starts with flagged accounts and follows outgoing transfers. Create a view named result listing accounts reachable in at most two hops. Reachability is a lead for investigation, not a finding of fraud.

Tables:

• accounts(id, name)
• transfers(transfer_id, from_account, to_account, amount_cents, transfer_ts)
• flagged_accounts(account_id)

IDs are integers; transfer endpoints and flagged IDs reference existing accounts. Different transfers may connect the same pair. Self-loops and cycles are allowed. No amount or date filter is required.

Contract

Return account_id, hop_depth, ordered by hop_depth, then account_id.

• Include each flagged account at depth 0.
• Follow from_account -> to_account, never the reverse direction.
• Include only nodes reached within 2 edges from any flagged seed.
• Return one row per reachable account, using its minimum depth across all paths and all seeds.
• With no flagged accounts, return no rows.

Example

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

Output: (A,0), (B,1), (C,1), (D,2). E is three hops away and is absent. A and C have repeated discoveries; retain their smaller depths. Letters stand in for integer IDs in this example.

Hints, in increasing detail

1. Trace the depth-0, depth-1, and depth-2 working sets on paper.
2. Use a recursive CTE whose base is the flagged set. Join the previous working set to outgoing transfers and increment depth.
3. Stop expansion inside the recursive term when the current depth is 2.
4. Aggregate all discoveries by account with the minimum depth.

Before submitting, add another seed, a duplicate transfer, and a cycle. Explain why final deduplication does not itself make an unbounded recursion terminate.