✔ PASS Temporal & Time-Series task_018_Q18_time_decay_user_affinity Scaled: 1M+ Records Ordered: Yes

Exponential Time-Decayed User Affinity Scoring

Candidate Latency (1M Rows)

209.34 ms

(1.0x faster than golden)

Golden Latency (1M Rows)

216.02 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

30.5 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 11.7s

✔ 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

Recommendation systems score user interest where recent clicks count significantly more than clicks from 30 days ago, modeled via exponential decay: score * e^(-lambda * delta_days).

⚡ The Frontier Trap (Why LLMs Fail)

Equal-weight counts overemphasize obsolete user interests from months ago.

Prompt / User Request Given to Model

Recommendation systems score user interest where recent clicks count significantly more than clicks from 30 days ago, modeled via exponential decay: score * e^(-lambda * delta_days).

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: user_id, category, decayed_affinity_score.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

1 Tables (1,000,000 rows • 30.5 MiB)

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

📊 item_clicks 1,000,000 rows 30.5 MiB Data Volume

user_id (INTEGER) category (VARCHAR) click_time (TIMESTAMP)

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

#	user_id	category	click_time
1	99	laptops	2026-03-01 10:00:00
2	99	laptops	2026-02-20 10:00:00
3	99	shoes	2026-01-01 10:00:00
4	99	laptops	2026-03-01 09:30:00.000001
5	100	shoes	2026-03-01 09:30:00.000020
6	101	laptops	2026-03-01 09:30:00.000052
7	102	shoes	2026-03-01 09:30:00.000070
8	103	laptops	2026-03-01 09:30:00.000088
9	104	shoes	2026-03-01 09:30:00.000100
10	105	laptops	2026-03-01 09:30:00.000132
11	106	shoes	2026-03-01 09:30:00.000150
12	107	laptops	2026-03-01 09:30:00.000165
13	108	shoes	2026-03-01 09:30:00.000181
14	109	laptops	2026-03-01 09:30:00.000200
15	110	shoes	2026-03-01 09:30:00.000222

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: 269.38 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

📐 PROJECTION 0.4%

Latency: 1.11 ms

Rows In/Out: 999,998

Data Vol: 26.70 MB

📁 Projections: ['#0', '__internal_...

⏳ ORDER_BY 🔥 21.6% Cost

Latency: 58.06 ms

Rows In/Out: 999,998

Data Vol: 19.07 MB

📐 PROJECTION 1.2%

Latency: 3.17 ms

Rows In/Out: 999,998

Data Vol: 19.07 MB

📁 Projections: ['#0', '__internal_...

📐 PROJECTION 9.7%

Latency: 26.04 ms

Rows In/Out: 999,998

Data Vol: 26.70 MB

📁 Projections: ['user_id', 'catego...

📐 PROJECTION 0.7%

Latency: 1.95 ms

Rows In/Out: 999,998

Data Vol: 26.70 MB

📁 Projections: ['#0', '__internal_...

📊 HASH_GROUP_BY 🔥 47.3% Cost

Latency: 127.35 ms

Rows In/Out: 999,998

Data Vol: 19.07 MB

📐 PROJECTION 10.7%

Latency: 28.83 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 Projections: ['user_id', 'catego...

📐 PROJECTION 1.2%

Latency: 3.31 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 Projections: ['#0', '__internal_...

🔍 TABLE_SCAN 7.3%

Latency: 19.55 ms

Rows In/Out: 1,000,000

Data Vol: 26.70 MB

📁 memory.main.item_clicks

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
HASH_GROUP_BY	47.3%	127.35 ms	999,998	19.07 MB	-	🔥 BOTTLENECK
ORDER_BY	21.6%	58.06 ms	999,998	19.07 MB	-	🔥 BOTTLENECK
PROJECTION	10.7%	28.83 ms	1,000,000	19.07 MB	Projections: ['user_id', 'category', "exp(CAST((	Normal
PROJECTION	9.7%	26.04 ms	999,998	26.70 MB	Projections: ['user_id', 'category', 'decayed_af	Normal
TABLE_SCAN	7.3%	19.55 ms	1,000,000	26.70 MB	memory.main.item_clicks	Normal
PROJECTION	1.2%	3.31 ms	1,000,000	19.07 MB	Projections: ['#0', '__internal_compress_string_	Normal
PROJECTION	1.2%	3.17 ms	999,998	19.07 MB	Projections: ['#0', '__internal_compress_string_	Normal
PROJECTION	0.7%	1.95 ms	999,998	26.70 MB	Projections: ['#0', '__internal_decompress_strin	Normal
PROJECTION	0.4%	1.11 ms	999,998	26.70 MB	Projections: ['#0', '__internal_decompress_strin	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

-- Exponential decay affinity score with 7-day half-life (lambda = ln(2)/7 = ~0.099)

SELECT user_id,
       category,
       round(sum(exp(-0.099 * date_diff('day', click_time, TIMESTAMP '2026-03-01 12:00:00'))), 3) AS decayed_affinity_score
FROM item_clicks
GROUP BY user_id,
         category
ORDER BY decayed_affinity_score DESC;

-- Exponential decay affinity score with 7-day half-life (lambda = ln(2)/7 = ~0.099)

SELECT user_id,
       category,
       round(sum(exp(-0.099 * date_diff('day', click_time, TIMESTAMP '2026-03-01 12:00:00'))), 3) AS decayed_affinity_score
FROM item_clicks
GROUP BY user_id,
         category
ORDER BY decayed_affinity_score DESC;

CREATE TABLE item_clicks (user_id INT, category VARCHAR, click_time TIMESTAMP);


INSERT INTO item_clicks
VALUES (99, 'laptops', '2026-03-01 10:00:00'),
       -- Today
 (99, 'laptops', '2026-02-20 10:00:00'),
 -- 9 days ago
 (99, 'shoes', '2026-01-01 10:00:00'); -- 60 days ago

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (999,998 rows × 3 cols)

#	user_id	category	decayed_affinity_score
1	99	laptops	2.41
2	111	laptops	1
3	143	laptops	1
4	167	laptops	1
5	176	shoes	1
6	177	laptops	1
7	220	shoes	1
8	236	shoes	1
9	274	shoes	1
10	290	shoes	1
11	340	shoes	1
12	367	laptops	1
13	391	laptops	1
14	519	laptops	1
15	545	laptops	1
16	558	shoes	1
17	560	shoes	1
18	597	laptops	1
19	628	shoes	1
20	657	laptops	1
21	697	laptops	1
22	711	laptops	1
23	721	laptops	1
24	727	laptops	1
25	790	shoes	1

Showing first 25 of 999,998 total rows.

Expected Golden Output (999,998 rows × 3 cols)

#	user_id	category	decayed_affinity_score
1	99	laptops	2.41
2	368,797	laptops	1
3	368,832	shoes	1
4	368,849	laptops	1
5	368,871	laptops	1
6	368,928	shoes	1
7	368,992	shoes	1
8	369,041	laptops	1
9	369,044	shoes	1
10	369,049	laptops	1
11	369,106	shoes	1
12	369,149	laptops	1
13	369,211	laptops	1
14	369,270	shoes	1
15	369,286	shoes	1
16	369,340	shoes	1
17	369,343	laptops	1
18	369,350	shoes	1
19	369,357	laptops	1
20	369,375	laptops	1
21	369,382	shoes	1
22	369,399	laptops	1
23	369,441	laptops	1
24	369,519	laptops	1
25	369,527	laptops	1

Showing first 25 of 999,998 total rows.

← Prev: Dynamic User Retention Co... ⊞ Back to Benchmark Dashboard Next: Customer Churn Anomaly De... →