SQL-FRONTIER Benchmark Deep-Dive / Temporal & Time-Series / task_019_Q19_intermittent_inactivity_anomaly
← Prev: Exponential Time-Decayed ... ⊞ All 120 Queries Next: E-Commerce Financial Aggr... →
✔ PASS Temporal & Time-Series task_019_Q19_intermittent_inactivity_anomaly Scaled: 1M+ Records Ordered: Yes

Customer Churn Anomaly Detection vs Historical Purchase Rhythm

Candidate Latency (1M Rows)
323.25 ms
(1.0x slower than golden)
Golden Latency (1M Rows)
314.75 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: 10.2s
✔ 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

A customer who normally buys every 3 days is at risk if they haven't bought in 10 days. But a customer who buys quarterly is not. Churn detection must be personalized to each user's cadence.

⚡ The Frontier Trap (Why LLMs Fail)

Static 30-day churn thresholds misflag seasonal or low-frequency high-value customers.

Prompt / User Request Given to Model
A customer who normally buys every 3 days is at risk if they haven't bought in 10 days. But a customer who buys quarterly is not. Churn detection must be personalized to each user's cadence.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: customer_id, last_order_date, median_purchase_interval_days, days_since_last_order, churn_risk_status.
Results should be ordered as specified.

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:

📊 orders_history 1,000,000 rows 7.6 MiB Data Volume
customer_id (INTEGER) order_date (DATE)
Data Preview (showing sample rows from 1,000,000 total rows):
# customer_id order_date
1 1 2026-01-01 00:00:00
2 1 2026-01-04 00:00:00
3 1 2026-01-07 00:00:00
4 2 2026-01-01 00:00:00
5 2 2026-02-01 00:00:00
6 2 2026-03-01 00:00:00
7 2 2026-01-01 00:00:00
8 3 2026-01-02 00:00:00
9 4 2026-01-03 00:00:00
10 5 2026-01-04 00:00:00
11 6 2026-01-05 00:00:00
12 7 2026-01-06 00:00:00
13 8 2026-01-07 00:00:00
14 9 2026-01-08 00:00:00
15 10 2026-01-09 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:

Pipeline Latency Distribution (Total Plan Time: 584.27 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
📐 PROJECTION 1.5%
Latency: 8.67 ms
Rows In/Out: 999,995
Data Vol: 38.27 MB
📁 Projections: ['#0', '#1', '#2', ...
⏳ ORDER_BY 🔥 16.0% Cost
Latency: 93.62 ms
Rows In/Out: 999,995
Data Vol: 31.59 MB
⚠️ Heavy Execution Bottleneck (16.0% of query runtime)
📐 PROJECTION 0.2%
Latency: 1.42 ms
Rows In/Out: 999,995
Data Vol: 31.60 MB
📁 Projections: ['#0', '#1', '#2', ...
📐 PROJECTION 0.8%
Latency: 4.90 ms
Rows In/Out: 999,995
Data Vol: 38.27 MB
📁 Projections: ['customer_id', 'la...
📐 PROJECTION 0.7%
Latency: 4.10 ms
Rows In/Out: 999,995
Data Vol: 23.01 MB
📁 Projections: ['customer_id', 'la...
📊 HASH_GROUP_BY 🔥 52.5% Cost
Latency: 306.52 ms
Rows In/Out: 999,995
Data Vol: 15.38 MB
⚠️ Heavy Execution Bottleneck (52.5% of query runtime)
📐 PROJECTION 0.0%
Latency: 0.15 ms
Rows In/Out: 1,000,000
Data Vol: 15.38 MB
📁 Projections: ['customer_id', 'or...
📐 PROJECTION 0.1%
Latency: 0.49 ms
Rows In/Out: 1,000,000
Data Vol: 15.38 MB
📁 Projections: ['customer_id', 'or...
📐 PROJECTION 0.0%
Latency: 0.22 ms
Rows In/Out: 1,000,000
Data Vol: 11.57 MB
📁 Projections: ['#0', '#1', '#2']
🪟 WINDOW 🔥 27.9% Cost
Latency: 163.27 ms
Rows In/Out: 1,000,000
Data Vol: 11.57 MB
📁 Projections: lag(order_date, 1, ...
⚠️ Heavy Execution Bottleneck (27.9% of query runtime)
🔍 TABLE_SCAN 0.2%
Latency: 0.92 ms
Rows In/Out: 1,000,000
Data Vol: 7.63 MB
📁 memory.main.orders_history
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
HASH_GROUP_BY 52.5% 306.52 ms 999,995 15.38 MB - 🔥 BOTTLENECK
WINDOW 27.9% 163.27 ms 1,000,000 11.57 MB Projections: lag(order_date, 1, NULL::DATE) OVER 🔥 BOTTLENECK
ORDER_BY 16.0% 93.62 ms 999,995 31.59 MB - 🔥 BOTTLENECK
PROJECTION 1.5% 8.67 ms 999,995 38.27 MB Projections: ['#0', '#1', '#2', 'CAST(#3 AS BIGI Normal
PROJECTION 0.8% 4.90 ms 999,995 38.27 MB Projections: ['customer_id', 'last_order_date', Normal
PROJECTION 0.7% 4.10 ms 999,995 23.01 MB Projections: ['customer_id', 'last_order_date', Normal
PROJECTION 0.2% 1.42 ms 999,995 31.60 MB Projections: ['#0', '#1', '#2', 'CAST(#3 AS TINY Normal
TABLE_SCAN 0.2% 0.92 ms 1,000,000 7.63 MB memory.main.orders_history Normal
PROJECTION 0.1% 0.49 ms 1,000,000 15.38 MB Projections: ['customer_id', 'order_date', 'days Normal
PROJECTION 0.0% 0.22 ms 1,000,000 11.57 MB Projections: ['#0', '#1', '#2'] Normal
PROJECTION 0.0% 0.15 ms 1,000,000 15.38 MB Projections: ['customer_id', 'order_date', 'days Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted
Candidate SQL Executed by gemini-3.8-flash-low (Runtime on 1M Rows: 323.25 ms) 
WITH purchase_intervals AS
  (SELECT customer_id,
          order_date,
          order_date - lag(order_date) OVER (PARTITION BY customer_id
                                             ORDER BY order_date) AS days_between
   FROM orders_history),
     user_cadence AS
  (SELECT customer_id,
          max(order_date) AS last_order_date,
          median(days_between) AS median_purchase_interval_days
   FROM purchase_intervals
   GROUP BY customer_id)
SELECT customer_id,
       last_order_date,
       median_purchase_interval_days,
       (DATE '2026-03-10' - last_order_date) AS days_since_last_order,
       CASE
           WHEN (DATE '2026-03-10' - last_order_date) > (2.5 * median_purchase_interval_days) THEN 'HIGH_CHURN_RISK'
           ELSE 'NORMAL'
       END AS churn_risk_status
FROM user_cadence
ORDER BY customer_id;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 314.75 ms) 
-- Calculate median inter-purchase days and compare against days elapsed since latest order
 WITH purchase_intervals AS
  (SELECT customer_id,
          order_date,
          order_date - lag(order_date) OVER (PARTITION BY customer_id
                                             ORDER BY order_date) AS days_between
   FROM orders_history),
      user_cadence AS
  (SELECT customer_id,
          max(order_date) AS last_order_date,
          median(days_between) AS median_purchase_interval_days
   FROM purchase_intervals
   GROUP BY customer_id)
SELECT customer_id,
       last_order_date,
       median_purchase_interval_days,
       (DATE '2026-03-10' - last_order_date) AS days_since_last_order,
       CASE
           WHEN (DATE '2026-03-10' - last_order_date) > (2.5 * median_purchase_interval_days) THEN 'HIGH_CHURN_RISK'
           ELSE 'NORMAL'
       END AS churn_risk_status
FROM user_cadence
ORDER BY customer_id;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE orders_history (customer_id INT, order_date DATE);


INSERT INTO orders_history
VALUES (1, '2026-01-01'),
       (1, '2026-01-04'),
       (1, '2026-01-07'),
       -- Rhythm: 3 days
 (2, '2026-01-01'),
 (2, '2026-02-01'),
 (2, '2026-03-01'); -- Rhythm: 30 days

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (999,995 rows × 5 cols)
# customer_id last_order_date median_purchase_interval_days days_since_last_order churn_risk_status
1 1 2026-01-07 00:00:00 3 62 HIGH_CHURN_RISK
2 2 2026-03-01 00:00:00 28 9 NORMAL
3 3 2026-01-02 00:00:00 NULL 67 NORMAL
4 4 2026-01-03 00:00:00 NULL 66 NORMAL
5 5 2026-01-04 00:00:00 NULL 65 NORMAL
6 6 2026-01-05 00:00:00 NULL 64 NORMAL
7 7 2026-01-06 00:00:00 NULL 63 NORMAL
8 8 2026-01-07 00:00:00 NULL 62 NORMAL
9 9 2026-01-08 00:00:00 NULL 61 NORMAL
10 10 2026-01-09 00:00:00 NULL 60 NORMAL
11 11 2026-01-10 00:00:00 NULL 59 NORMAL
12 12 2026-01-11 00:00:00 NULL 58 NORMAL
13 13 2026-01-12 00:00:00 NULL 57 NORMAL
14 14 2026-01-13 00:00:00 NULL 56 NORMAL
15 15 2026-01-14 00:00:00 NULL 55 NORMAL
16 16 2026-01-15 00:00:00 NULL 54 NORMAL
17 17 2026-01-16 00:00:00 NULL 53 NORMAL
18 18 2026-01-17 00:00:00 NULL 52 NORMAL
19 19 2026-01-18 00:00:00 NULL 51 NORMAL
20 20 2026-01-19 00:00:00 NULL 50 NORMAL
21 21 2026-01-20 00:00:00 NULL 49 NORMAL
22 22 2026-01-21 00:00:00 NULL 48 NORMAL
23 23 2026-01-22 00:00:00 NULL 47 NORMAL
24 24 2026-01-23 00:00:00 NULL 46 NORMAL
25 25 2026-01-24 00:00:00 NULL 45 NORMAL
Showing first 25 of 999,995 total rows.
Expected Golden Output (999,995 rows × 5 cols)
# customer_id last_order_date median_purchase_interval_days days_since_last_order churn_risk_status
1 1 2026-01-07 00:00:00 3 62 HIGH_CHURN_RISK
2 2 2026-03-01 00:00:00 28 9 NORMAL
3 3 2026-01-02 00:00:00 NULL 67 NORMAL
4 4 2026-01-03 00:00:00 NULL 66 NORMAL
5 5 2026-01-04 00:00:00 NULL 65 NORMAL
6 6 2026-01-05 00:00:00 NULL 64 NORMAL
7 7 2026-01-06 00:00:00 NULL 63 NORMAL
8 8 2026-01-07 00:00:00 NULL 62 NORMAL
9 9 2026-01-08 00:00:00 NULL 61 NORMAL
10 10 2026-01-09 00:00:00 NULL 60 NORMAL
11 11 2026-01-10 00:00:00 NULL 59 NORMAL
12 12 2026-01-11 00:00:00 NULL 58 NORMAL
13 13 2026-01-12 00:00:00 NULL 57 NORMAL
14 14 2026-01-13 00:00:00 NULL 56 NORMAL
15 15 2026-01-14 00:00:00 NULL 55 NORMAL
16 16 2026-01-15 00:00:00 NULL 54 NORMAL
17 17 2026-01-16 00:00:00 NULL 53 NORMAL
18 18 2026-01-17 00:00:00 NULL 52 NORMAL
19 19 2026-01-18 00:00:00 NULL 51 NORMAL
20 20 2026-01-19 00:00:00 NULL 50 NORMAL
21 21 2026-01-20 00:00:00 NULL 49 NORMAL
22 22 2026-01-21 00:00:00 NULL 48 NORMAL
23 23 2026-01-22 00:00:00 NULL 47 NORMAL
24 24 2026-01-23 00:00:00 NULL 46 NORMAL
25 25 2026-01-24 00:00:00 NULL 45 NORMAL
Showing first 25 of 999,995 total rows.
← Prev: Exponential Time-Decayed ... ⊞ Back to Benchmark Dashboard Next: E-Commerce Financial Aggr... →