Find Circular Gift Exchange Chains - Problem
Given a SecretSanta table that represents gift exchanges between employees, find all circular chains of gift exchanges.
A circular chain is defined as a series of exchanges where:
- Each employee gives a gift to exactly one other employee
- Each employee receives a gift from exactly one other employee
- The exchanges form a continuous loop (e.g., employee A gives to B, B gives to C, and C gives back to A)
Return the chain_id, chain_length, and total_gift_value for each circular chain, ordered by chain length and total gift value in descending order.
Table Schema
SecretSanta
| Column Name | Type | Description |
|---|---|---|
giver_id
PK
|
int | ID of the employee giving the gift |
receiver_id
PK
|
int | ID of the employee receiving the gift |
gift_value
|
int | Value of the gift being exchanged |
Primary Key: (giver_id, receiver_id)
Note: Each row represents a gift exchange. The combination of giver_id and receiver_id is unique.
Input & Output
Example 1 — Multiple Circular Chains
Input Table:
| giver_id | receiver_id | gift_value |
|---|---|---|
| 1 | 2 | 20 |
| 2 | 3 | 30 |
| 3 | 1 | 40 |
| 4 | 5 | 25 |
| 5 | 4 | 35 |
Output:
| chain_id | chain_length | total_gift_value |
|---|---|---|
| 1 | 3 | 90 |
| 2 | 2 | 60 |
💡 Note:
Two circular chains are formed:
- Chain 1: Employees 1→2→3→1 with length 3 and total value 20+30+40=90
- Chain 2: Employees 4→5→4 with length 2 and total value 25+35=60
Results ordered by chain length (3,2) then total value (90,60) in descending order.
Example 2 — Single Large Chain
Input Table:
| giver_id | receiver_id | gift_value |
|---|---|---|
| 1 | 2 | 100 |
| 2 | 3 | 150 |
| 3 | 4 | 200 |
| 4 | 1 | 250 |
Output:
| chain_id | chain_length | total_gift_value |
|---|---|---|
| 1 | 4 | 700 |
💡 Note:
One circular chain is formed with all 4 employees: 1→2→3→4→1
Chain length is 4 and total gift value is 100+150+200+250=700
Example 3 — Self-Exchange
Input Table:
| giver_id | receiver_id | gift_value |
|---|---|---|
| 1 | 1 | 50 |
Output:
| chain_id | chain_length | total_gift_value |
|---|---|---|
| 1 | 1 | 50 |
💡 Note:
Employee 1 gives a gift to themselves, forming a circular chain of length 1 with total value 50
Constraints
-
1 ≤ giver_id, receiver_id ≤ 1000 -
1 ≤ gift_value ≤ 10000 -
(giver_id, receiver_id)is unique for each row - Each employee in a valid circular chain gives exactly one gift and receives exactly one gift
Visualization
Tap to expand
Understanding the Visualization
1
Input
Gift exchange records
2
Trace Chains
Follow giver→receiver links
3
Detect Circles
Find chains that return to start
Key Takeaway
🎯 Key Insight: Use recursive CTEs to trace linked relationships and detect when they form complete cycles
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code