✔ PASS Warehouse & Statistical task_101_Q101_kaplan_meier_churn_survival Scaled: 1M+ Records Ordered: Yes

Kaplan-Meier Non-Parametric Customer Survival Analysis in SQL

Candidate Latency (1M Rows)

5.10 ms

(1.6x faster than golden)

Golden Latency (1M Rows)

8.01 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

8.6 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 14.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

Actuarial and SaaS subscription models estimate the probability of customer survival past Day T, accounting for right-censored users who haven't churned yet.

⚡ The Frontier Trap (Why LLMs Fail)

Naively dividing churned users by total signups underestimates customer lifetime value.

Prompt / User Request Given to Model

Actuarial and SaaS subscription models estimate the probability of customer survival past Day T, accounting for right-censored users who haven't churned yet.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: tenure_months, number_at_risk, churn_count, interval_survival_rate.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 subscription_cohort 1,000,000 rows 8.6 MiB Data Volume

user_id (INTEGER) tenure_months (INTEGER) has_churned (BOOLEAN)

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

#	user_id	tenure_months	has_churned
1	1	1	1
2	2	1	0
3	3	2	1
4	4	2	0
5	5	3	0
6	5	2	1
7	6	1	0
8	7	2	1
9	8	1	0
10	9	2	1
11	10	1	0
12	11	3	1
13	12	2	0
14	13	2	1
15	14	1	0

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

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 6

Data Vol: 216 B

📁 Projections: ['CAST(#0 AS INTEGE...

⏳ ORDER_BY 0.7%

Latency: 0.05 ms

Rows In/Out: 6

Data Vol: 126 B

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 6

Data Vol: 126 B

📁 Projections: ['CAST(#0 AS TINYIN...

📐 PROJECTION 0.1%

Latency: 0.01 ms

Rows In/Out: 6

Data Vol: 216 B

📁 Projections: ['tenure_months', '...

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 6

Data Vol: 168 B

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

🪟 WINDOW 1.9%

Latency: 0.15 ms

Rows In/Out: 6

Data Vol: 168 B

📁 Projections: count() OVER (ORDER...

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 6

Data Vol: 120 B

📁 Projections: ['CAST(#0 AS INTEGE...

📊 PERFECT_HASH_GROUP_BY 🔥 65.5% Cost

Latency: 5.19 ms

Rows In/Out: 6

Data Vol: 102 B

📐 PROJECTION 18.4%

Latency: 1.46 ms

Rows In/Out: 1,000,000

Data Vol: 4.77 MB

📁 Projections: ['tenure_months', '...

📐 PROJECTION 5.7%

Latency: 0.45 ms

Rows In/Out: 1,000,000

Data Vol: 1.91 MB

📁 Projections: ['CAST(#0 AS TINYIN...

🔍 TABLE_SCAN 7.7%

Latency: 0.61 ms

Rows In/Out: 1,000,000

Data Vol: 4.77 MB

📁 memory.main.subscription_cohort

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
PERFECT_HASH_GROUP_BY	65.5%	5.19 ms	6	0.00 MB	-	🔥 BOTTLENECK
PROJECTION	18.4%	1.46 ms	1,000,000	4.77 MB	Projections: ['tenure_months', 'CASE WHEN (has_	Normal
TABLE_SCAN	7.7%	0.61 ms	1,000,000	4.77 MB	memory.main.subscription_cohort	Normal
PROJECTION	5.7%	0.45 ms	1,000,000	1.91 MB	Projections: ['CAST(#0 AS TINYINT)', '#1']	Normal
WINDOW	1.9%	0.15 ms	6	0.00 MB	Projections: count() OVER (ORDER BY tenure_month	Normal
ORDER_BY	0.7%	0.05 ms	6	0.00 MB	-	Normal
PROJECTION	0.1%	0.01 ms	6	0.00 MB	Projections: ['tenure_months', 'number_at_risk',	Normal
PROJECTION	0.0%	0.00 ms	6	0.00 MB	Projections: ['CAST(#0 AS INTEGER)', '#1', 'CAST	Normal
PROJECTION	0.0%	0.00 ms	6	0.00 MB	Projections: ['CAST(#0 AS INTEGER)', '#1']	Normal
PROJECTION	0.0%	0.00 ms	6	0.00 MB	Projections: ['CAST(#0 AS TINYINT)', '#1', 'CAST	Normal
PROJECTION	0.0%	0.00 ms	6	0.00 MB	Projections: ['#0', '#1', '#2']	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

WITH monthly_risk AS
  (SELECT tenure_months,
          count(*) AS cohort_events,
          sum(CASE
                  WHEN has_churned THEN 1
                  ELSE 0
              END) AS churn_count,
          count(*) OVER (
                         ORDER BY tenure_months DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS number_at_risk
   FROM subscription_cohort
   GROUP BY tenure_months)
SELECT tenure_months,
       number_at_risk,
       churn_count,
       round(1.0 - (churn_count * 1.0 / number_at_risk), 4) AS interval_survival_rate
FROM monthly_risk
ORDER BY tenure_months;

-- Kaplan-Meier survival probability estimation by tenure month
 WITH monthly_risk AS
  (SELECT tenure_months,
          count(*) AS cohort_events,
          sum(CASE
                  WHEN has_churned THEN 1
                  ELSE 0
              END) AS churn_count,
          count(*) OVER (
                         ORDER BY tenure_months DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS number_at_risk
   FROM subscription_cohort
   GROUP BY tenure_months)
SELECT tenure_months,
       number_at_risk,
       churn_count,
       round(1.0 - (churn_count * 1.0 / number_at_risk), 4) AS interval_survival_rate
FROM monthly_risk
ORDER BY tenure_months;

CREATE TABLE subscription_cohort (user_id INT, tenure_months INT, has_churned BOOLEAN);


INSERT INTO subscription_cohort
VALUES (1, 1, TRUE),
       -- Churned at month 1
 (2, 1, FALSE),
 -- Still active at month 1
 (3, 2, TRUE),
 -- Churned at month 2
 (4, 2, FALSE),
 (5, 3, FALSE);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (6 rows × 4 cols)

#	tenure_months	number_at_risk	churn_count	interval_survival_rate
1	1	6	177,362	-29,559.3333
2	2	5	227,096	-45,418.2
3	3	4	87,144	-21,785
4	4	3	8,219	-2,738.6667
5	5	2	178	-88
6	6	1	1	0

Expected Golden Output (6 rows × 4 cols)

#	tenure_months	number_at_risk	churn_count	interval_survival_rate
1	1	6	177,362	-29,559.3333
2	2	5	227,096	-45,418.2
3	3	4	87,144	-21,785
4	4	3	8,219	-2,738.6667
5	5	2	178	-88
6	6	1	1	0

← Prev: Market Basket Association... ⊞ Back to Benchmark Dashboard Next: Dynamic Equi-Depth Histog... →

#	user_id	tenure_months	has_churned
1	1	1	1
2	2	1	0
3	3	2	1
4	4	2	0
5	5	3	0
6	5	2	1
7	6	1	0
8	7	2	1
9	8	1	0
10	9	2	1
11	10	1	0
12	11	3	1
13	12	2	0
14	13	2	1
15	14	1	0

#	user_id	tenure_months	has_churned
1	1	1	1
2	2	1	0
3	3	2	1
4	4	2	0
5	5	3	0
6	5	2	1
7	6	1	0
8	7	2	1
9	8	1	0
10	9	2	1
11	10	1	0
12	11	3	1
13	12	2	0
14	13	2	1
15	14	1	0

#	user_id	tenure_months	has_churned
1	1	1	1
2	2	1	0
3	3	2	1
4	4	2	0
5	5	3	0
6	5	2	1
7	6	1	0
8	7	2	1
9	8	1	0
10	9	2	1
11	10	1	0
12	11	3	1
13	12	2	0
14	13	2	1
15	14	1	0