✔ PASS Graph & Hierarchy task_085_Q85_multilevel_referral_commissions Scaled: 1M+ Records Ordered: Yes

Multi-Level Affiliate Referral Tree Commission Rollup (Tier 1-3)

Candidate Latency (1M Rows)

0.95 ms

(1.1x slower than golden)

Golden Latency (1M Rows)

0.90 ms

DuckDB in-memory engine

Underlying Data Volume

4 rows

48 B across 2 tables

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 11.3s

✔ Correctness Verified on 1M+ Records

Candidate SQL output matches ground truth golden query across all multiset rows and numeric tolerances (tol=1e-3). Zero data skew or anti-patterns detected on full dataset scale.

1. Analytical Significance & What It Means

Scenario & Frontier Trap

Business Scenario & Problem

Affiliate programs reward referrals up to 3 tiers deep (Direct referrer gets 10%, Tier 2 gets 5%, Tier 3 gets 2.5% of purchase amount).

⚡ The Frontier Trap (Why LLMs Fail)

Hardcoded 3-way self-joins fail to handle users whose upline is only 1 or 2 tiers deep without complex coalesce logic.

Prompt / User Request Given to Model

Affiliate programs reward referrals up to 3 tiers deep (Direct referrer gets 10%, Tier 2 gets 5%, Tier 3 gets 2.5% of purchase amount).

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: buyer_id, beneficiary_id, tier, purchase_amount, commission_earned.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

2 Tables (4 rows • 48 B)

Production-scale fixtures loaded into the isolated DuckDB sandbox with at least 1,000,000 records per table:

📊 referral_links 3 rows 24 B Data Volume

user_id (INTEGER) referrer_id (INTEGER)

Data Preview (showing sample rows from 3 total rows):

#	user_id	referrer_id
1	4	3
2	3	2
3	2	1

📊 user_purchases 1 rows 24 B Data Volume

purchase_id (INTEGER) user_id (INTEGER) amount (DECIMAL(10,2))

Data Preview (showing sample rows from 1 total rows):

#	purchase_id	user_id	amount
1	101	4	1,000

3. Graphical Query Execution Plan & Cost Bottlenecks

Physical Operator Tree (DuckDB 2.0 Profiler)

Visual flowchart of the physical execution pipeline. Operators with high relative costs (≥ 20% of query runtime or heavy latency) are visually highlighted:

Pipeline Latency Distribution (Total Plan Time: 0.95 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

⏳ ORDER_BY 9.6%

Latency: 0.09 ms

Rows In/Out: 3

Data Vol: 84 B

📐 PROJECTION 2.0%

Latency: 0.02 ms

Rows In/Out: 3

Data Vol: 84 B

📁 Projections: ['buyer_id', 'benef...

⚡ HASH_JOIN 13.7%

Latency: 0.13 ms

Rows In/Out: 3

Data Vol: 66 B

🔄 RECURSIVE_CTE 🔥 57.8% Cost

Latency: 0.55 ms

Rows In/Out: 3

Data Vol: 42 B

📐 PROJECTION 0.1%

Latency: 0.00 ms

Rows In/Out: 1

Data Vol: 14 B

📁 Projections: ['buyer_id', 'benef...

🔍 TABLE_SCAN 1.3%

Latency: 0.01 ms

Rows In/Out: 1

Data Vol: 8 B

📁 memory.main.referral_links

📐 PROJECTION 1.9%

Latency: 0.02 ms

Rows In/Out: 2

Data Vol: 28 B

📁 Projections: ['buyer_id', 'benef...

⚡ HASH_JOIN 8.5%

Latency: 0.08 ms

Rows In/Out: 2

Data Vol: 24 B

📐 PROJECTION 0.1%

Latency: 0.00 ms

Rows In/Out: 2

Data Vol: 24 B

📁 Projections: ['#0', '#1', '#2']

🎯 FILTER 3.0%

Latency: 0.03 ms

Rows In/Out: 2

Data Vol: 28 B

🔍 RECURSIVE_CTE_SCAN 0.4%

Latency: 0.00 ms

Rows In/Out: 3

Data Vol: 42 B

🔍 TABLE_SCAN 0.6%

Latency: 0.01 ms

Rows In/Out: 3

Data Vol: 24 B

📁 memory.main.referral_links

🔍 TABLE_SCAN 1.0%

Latency: 0.01 ms

Rows In/Out: 1

Data Vol: 12 B

📁 memory.main.user_purchases

Operator	Cost %	Latency	Processed Rows	Details / Table	Status
RECURSIVE_CTE	57.8%	0.55 ms	3	-	🔥 BOTTLENECK
HASH_JOIN	13.7%	0.13 ms	3	-	Normal
ORDER_BY	9.6%	0.09 ms	3	-	Normal
HASH_JOIN	8.5%	0.08 ms	2	-	Normal
FILTER	3.0%	0.03 ms	2	-	Normal
PROJECTION	2.0%	0.02 ms	3	Projections: ['buyer_id', 'beneficiary_id', 'tie	Normal
PROJECTION	1.9%	0.02 ms	2	Projections: ['buyer_id', 'beneficiary_id', '(ti	Normal
TABLE_SCAN	1.3%	0.01 ms	1	memory.main.referral_links	Normal
TABLE_SCAN	1.0%	0.01 ms	1	memory.main.user_purchases	Normal
TABLE_SCAN	0.6%	0.01 ms	3	memory.main.referral_links	Normal
RECURSIVE_CTE_SCAN	0.4%	0.00 ms	3	-	Normal
PROJECTION	0.1%	0.00 ms	1	Projections: ['buyer_id', 'beneficiary_id', 'tie	Normal
PROJECTION	0.1%	0.00 ms	2	Projections: ['#0', '#1', '#2']	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

WITH RECURSIVE upline AS
  (-- Level 1: Direct referrer
 SELECT r.user_id AS buyer_id,
        r.referrer_id AS beneficiary_id,
        1 AS tier,
        0.10 AS commission_rate
   FROM referral_links r
   WHERE r.user_id = 4
   UNION ALL -- Level 2 and 3 upline
 SELECT u.buyer_id,
        r.referrer_id AS beneficiary_id,
        u.tier + 1,
        CASE
            WHEN u.tier + 1 = 2 THEN 0.05
            ELSE 0.025
        END AS commission_rate
   FROM upline u
   JOIN referral_links r ON u.beneficiary_id = r.user_id
   WHERE u.tier < 3)
SELECT u.buyer_id,
       u.beneficiary_id,
       u.tier,
       p.amount AS purchase_amount,
       round(p.amount * u.commission_rate, 2) AS commission_earned
FROM upline u
JOIN user_purchases p ON u.buyer_id = p.user_id
ORDER BY u.tier;

-- Calculate multi-tier referral payout for purchase 101
 WITH RECURSIVE upline AS
  (-- Level 1: Direct referrer
 SELECT r.user_id AS buyer_id,
        r.referrer_id AS beneficiary_id,
        1 AS tier,
        0.10 AS commission_rate
   FROM referral_links r
   WHERE r.user_id = 4
   UNION ALL -- Level 2 and 3 upline
 SELECT u.buyer_id,
        r.referrer_id AS beneficiary_id,
        u.tier + 1,
        CASE
            WHEN u.tier + 1 = 2 THEN 0.05
            ELSE 0.025
        END AS commission_rate
   FROM upline u
   JOIN referral_links r ON u.beneficiary_id = r.user_id
   WHERE u.tier < 3)
SELECT u.buyer_id,
       u.beneficiary_id,
       u.tier,
       p.amount AS purchase_amount,
       round(p.amount * u.commission_rate, 2) AS commission_earned
FROM upline u
JOIN user_purchases p ON u.buyer_id = p.user_id
ORDER BY u.tier;

CREATE TABLE referral_links (user_id INT, referrer_id INT);


CREATE TABLE user_purchases (purchase_id INT, user_id INT, amount decimal(10, 2));


INSERT INTO referral_links
VALUES (4, 3),
       -- 4 referred by 3
 (3, 2),
 -- 3 referred by 2
 (2, 1); -- 2 referred by 1 (Grandparent)


INSERT INTO user_purchases
VALUES (101, 4, 1000.00); -- Purchase by user 4

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (3 rows × 5 cols)

#	buyer_id	beneficiary_id	tier	purchase_amount	commission_earned
1	4	3	1	1,000	100
2	4	2	2	1,000	50
3	4	1	3	1,000	30

Expected Golden Output (3 rows × 5 cols)

#	buyer_id	beneficiary_id	tier	purchase_amount	commission_earned
1	4	3	1	1,000	100
2	4	2	2	1,000	50
3	4	1	3	1,000	30

← Prev: Task Scheduling Critical ... ⊞ Back to Benchmark Dashboard Next: Multi-Parent Shared Infra... →