Circular Secret Santa Chain Ledger

A workplace gifting platform stores directed gift transfers between employees.

Table: SecretSanta

• giver_id: employee who gives a gift
• receiver_id: employee who receives a gift
• gift_value: integer value of that gift transfer

Business guarantees:

• (giver_id, receiver_id) is unique.
• Each employee appears at most once as giver_id.
• Each employee appears at most once as receiver_id.
• giver_id is never equal to receiver_id.

Task: Identify all distinct circular gift chains. A circular chain is a closed directed loop where each participant has exactly:

• one outgoing edge to the next participant in the loop, and
• one incoming edge from the previous participant in the loop.

For every detected chain, compute:

1. chain_length: number of participants in the loop
2. total_gift_value: sum of gift_value on loop edges

Output requirements:

• Return exactly these columns in this order: chain_id, chain_length, total_gift_value
• Define chain_id as a 1-based rank after sorting detected chains by:
  1. chain_length descending
  2. total_gift_value descending
  3. smallest participant id in the chain ascending

Supported submission environments:

• SQL (SQLite 3.27.2)
• Pandas (Python)