✔ PASS
Algorithmic Efficiency / Window Distinct
task_111_rolling_distinct_active_users
Scaled: 1M+ Records
Ordered: Yes
Rolling 7-Day Distinct Active Users without Triangular Join Explosion
Candidate Latency (1M Rows)
658.65 ms
(1.7x slower than golden)
Golden Latency (1M Rows)
386.88 ms
DuckDB in-memory engine
Underlying Data Volume
1,000,000 rows
7.6 MiB across 1 table
Evaluated Model
gemini-3.8-flash-low
Gen Latency: 4.8s
✔ 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 TrapBusiness Scenario & Problem
Calculates rolling 7-day distinct active users per calendar day. SQL window functions forbid COUNT(DISTINCT) over range frames, causing frontier models to generate catastrophic O(N^2) triangular self-joins that blow up memory.
⚡ The Frontier Trap (Why LLMs Fail)
Joining user_activity directly to itself on an inequality date range produces an O(N^2) Cartesian row explosion. For 1M rows, an unaggregated triangular join emits over 7M intermediate rows and triggers an IE_JOIN or Nested Loop join, overflowing RAM. The optimal pattern aggregates users into daily sets first before evaluating rolling intervals.
Prompt / User Request Given to Model
Calculate the rolling 7-day distinct active user count for each active date. For each date, count distinct users active on that date or within the preceding 6 days (total 7 calendar days). Required output columns: - report_date (DATE) - rolling_7d_active_users (BIGINT) Order results by report_date ASC.
2. Underlying Tables, Row Counts & Data Volume
1 Tables (1,000,000 rows • 7.6 MiB)Production-scale fixtures loaded into the isolated DuckDB sandbox with at least 1,000,000 records per table:
📊 user_activity
1,000,000 rows
7.6 MiB Data Volume
user_id (INTEGER) activity_date (DATE)Data Preview (showing sample rows from 1,000,000 total rows):
| # | user_id | activity_date |
|---|---|---|
| 1 | 101 | 2026-03-01 00:00:00 |
| 2 | 102 | 2026-03-01 00:00:00 |
| 3 | 101 | 2026-03-02 00:00:00 |
| 4 | 103 | 2026-03-02 00:00:00 |
| 5 | 104 | 2026-03-03 00:00:00 |
| 6 | 101 | 2026-03-05 00:00:00 |
| 7 | 102 | 2026-03-08 00:00:00 |
| 8 | 105 | 2026-03-08 00:00:00 |
| 9 | 105 | 2026-01-01 00:00:00 |
| 10 | 106 | 2026-01-02 00:00:00 |
| 11 | 107 | 2026-01-03 00:00:00 |
| 12 | 108 | 2026-01-04 00:00:00 |
| 13 | 109 | 2026-01-05 00:00:00 |
| 14 | 110 | 2026-01-06 00:00:00 |
| 15 | 111 | 2026-01-07 00:00:00 |
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:
0.0%
Latency: 0.09 ms
Rows In/Out: 90
Data Vol: 1,080 B
1.3%
Latency: 32.82 ms
Rows In/Out: 90
Data Vol: 1,080 B
0.0%
Latency: 0.45 ms
Rows In/Out: 6,766,659
Data Vol: 25.81 MB
📁 Projections: #0
🔥 34.9% Cost
Latency: 881.76 ms
Rows In/Out: 6,766,659
Data Vol: 51.63 MB
0.2%
Latency: 3.87 ms
Rows In/Out: 6,766,669
Data Vol: 51.63 MB
📁 Projections: ['report_date', 'us...
🔥 59.1% Cost
Latency: 1495.05 ms
Rows In/Out: 6,766,669
Data Vol: 77.44 MB
🔥 4.1% Cost
Latency: 103.59 ms
Rows In/Out: 1,000,000
Data Vol: 7.63 MB
0.0%
Latency: 0.12 ms
Rows In/Out: 1,000,000
Data Vol: 7.63 MB
📁 Projections: ['user_id', 'activi...
0.0%
Latency: 1.10 ms
Rows In/Out: 1,000,000
Data Vol: 7.63 MB
📁 memory.main.user_activity
0.4%
Latency: 9.01 ms
Rows In/Out: 90
Data Vol: 360 B
0.0%
Latency: 0.06 ms
Rows In/Out: 1,000,000
Data Vol: 3.81 MB
📁 Projections: report_date
0.0%
Latency: 0.46 ms
Rows In/Out: 1,000,000
Data Vol: 3.81 MB
📁 memory.main.user_activity
| Operator | Cost % | Latency | Processed Rows | Data Volume | Details / Table | Status |
|---|---|---|---|---|---|---|
| PIECEWISE_MERGE_JOIN | 59.1% | 1495.05 ms | 6,766,669 | 77.44 MB | - | 🔥 BOTTLENECK |
| HASH_GROUP_BY | 34.9% | 881.76 ms | 6,766,659 | 51.63 MB | - | 🔥 BOTTLENECK |
| HASH_GROUP_BY | 4.1% | 103.59 ms | 1,000,000 | 7.63 MB | - | 🔥 BOTTLENECK |
| PERFECT_HASH_GROUP_BY | 1.3% | 32.82 ms | 90 | 0.00 MB | - | Normal |
| HASH_GROUP_BY | 0.4% | 9.01 ms | 90 | 0.00 MB | - | Normal |
| PROJECTION | 0.2% | 3.87 ms | 6,766,669 | 51.63 MB | Projections: ['report_date', 'user_id'] | Normal |
| TABLE_SCAN | 0.0% | 1.10 ms | 1,000,000 | 7.63 MB | memory.main.user_activity | Normal |
| TABLE_SCAN | 0.0% | 0.46 ms | 1,000,000 | 3.81 MB | memory.main.user_activity | Normal |
| PROJECTION | 0.0% | 0.45 ms | 6,766,659 | 25.81 MB | Projections: #0 | Normal |
| PROJECTION | 0.0% | 0.12 ms | 1,000,000 | 7.63 MB | Projections: ['user_id', 'activity_date'] | Normal |
| ORDER_BY | 0.0% | 0.09 ms | 90 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.06 ms | 1,000,000 | 3.81 MB | Projections: report_date | Normal |
4. Queries Executed (Candidate vs Golden Ground Truth)
Beautified & Syntax HighlightedWITH distinct_user_days AS
(SELECT DISTINCT user_id,
activity_date
FROM user_activity),
active_dates AS
(SELECT DISTINCT activity_date AS report_date
FROM user_activity)
SELECT d.report_date,
count(DISTINCT u.user_id) AS rolling_7d_active_users
FROM active_dates d
JOIN distinct_user_days u ON u.activity_date BETWEEN d.report_date - INTERVAL 6 DAY AND d.report_date
GROUP BY d.report_date
ORDER BY d.report_date ASC;
WITH daily_users AS
(SELECT activity_date,
list(DISTINCT user_id) AS user_list
FROM user_activity
GROUP BY activity_date),
distinct_dates AS
(SELECT DISTINCT activity_date AS report_date
FROM user_activity)
SELECT d.report_date,
(SELECT count(DISTINCT u)
FROM
(SELECT unnest(user_list) AS u
FROM daily_users sub
WHERE sub.activity_date BETWEEN d.report_date - INTERVAL 6 days AND d.report_date)) AS rolling_7d_active_users
FROM distinct_dates d
ORDER BY d.report_date ASC;
CREATE TABLE user_activity (user_id INT, activity_date DATE);
INSERT INTO user_activity
VALUES (101, '2026-03-01'),
(102, '2026-03-01'),
(101, '2026-03-02'),
(103, '2026-03-02'),
(104, '2026-03-03'),
(101, '2026-03-05'),
(102, '2026-03-08'),
(105, '2026-03-08');
5. Query Results & Expected Ground Truth
Tabular Comparison
Actual Candidate Output
(90 rows × 2 cols)
| # | report_date | rolling_7d_active_users |
|---|---|---|
| 1 | 2026-01-01 00:00:00 | 11,112 |
| 2 | 2026-01-02 00:00:00 | 22,224 |
| 3 | 2026-01-03 00:00:00 | 33,335 |
| 4 | 2026-01-04 00:00:00 | 44,446 |
| 5 | 2026-01-05 00:00:00 | 55,557 |
| 6 | 2026-01-06 00:00:00 | 66,668 |
| 7 | 2026-01-07 00:00:00 | 77,779 |
| 8 | 2026-01-08 00:00:00 | 77,778 |
| 9 | 2026-01-09 00:00:00 | 77,777 |
| 10 | 2026-01-10 00:00:00 | 77,777 |
| 11 | 2026-01-11 00:00:00 | 77,777 |
| 12 | 2026-01-12 00:00:00 | 77,777 |
| 13 | 2026-01-13 00:00:00 | 77,777 |
| 14 | 2026-01-14 00:00:00 | 77,777 |
| 15 | 2026-01-15 00:00:00 | 77,777 |
| 16 | 2026-01-16 00:00:00 | 77,777 |
| 17 | 2026-01-17 00:00:00 | 77,777 |
| 18 | 2026-01-18 00:00:00 | 77,777 |
| 19 | 2026-01-19 00:00:00 | 77,777 |
| 20 | 2026-01-20 00:00:00 | 77,777 |
| 21 | 2026-01-21 00:00:00 | 77,777 |
| 22 | 2026-01-22 00:00:00 | 77,777 |
| 23 | 2026-01-23 00:00:00 | 77,777 |
| 24 | 2026-01-24 00:00:00 | 77,777 |
| 25 | 2026-01-25 00:00:00 | 77,777 |
Showing first 25 of 90 total rows.
Expected Golden Output
(90 rows × 2 cols)
| # | report_date | rolling_7d_active_users |
|---|---|---|
| 1 | 2026-01-01 00:00:00 | 11,112 |
| 2 | 2026-01-02 00:00:00 | 22,224 |
| 3 | 2026-01-03 00:00:00 | 33,335 |
| 4 | 2026-01-04 00:00:00 | 44,446 |
| 5 | 2026-01-05 00:00:00 | 55,557 |
| 6 | 2026-01-06 00:00:00 | 66,668 |
| 7 | 2026-01-07 00:00:00 | 77,779 |
| 8 | 2026-01-08 00:00:00 | 77,778 |
| 9 | 2026-01-09 00:00:00 | 77,777 |
| 10 | 2026-01-10 00:00:00 | 77,777 |
| 11 | 2026-01-11 00:00:00 | 77,777 |
| 12 | 2026-01-12 00:00:00 | 77,777 |
| 13 | 2026-01-13 00:00:00 | 77,777 |
| 14 | 2026-01-14 00:00:00 | 77,777 |
| 15 | 2026-01-15 00:00:00 | 77,777 |
| 16 | 2026-01-16 00:00:00 | 77,777 |
| 17 | 2026-01-17 00:00:00 | 77,777 |
| 18 | 2026-01-18 00:00:00 | 77,777 |
| 19 | 2026-01-19 00:00:00 | 77,777 |
| 20 | 2026-01-20 00:00:00 | 77,777 |
| 21 | 2026-01-21 00:00:00 | 77,777 |
| 22 | 2026-01-22 00:00:00 | 77,777 |
| 23 | 2026-01-23 00:00:00 | 77,777 |
| 24 | 2026-01-24 00:00:00 | 77,777 |
| 25 | 2026-01-25 00:00:00 | 77,777 |
Showing first 25 of 90 total rows.