AI Elo

Analysis of Claude Opus 4.6 (High Think):

**CORRECTNESS:** Entry X is highly correct and handles all specified edge cases well. 1. **Active subscription logic:** The condition `s.started_at <= (m.month_start + INTERVAL '1 month' - INTERVAL '1 day')::date AND (s.cancelled_at IS NULL OR s.cancelled_at >= m.month_start)` correctly implements the "active for a given month" definition from the prompt. 2. **Overlapping subscriptions / cancel-resubscribe:** Uses `GROUP BY m.month_start, s.customer_id` in `month_active` to deduplicate customers with multiple active subscriptions in the same month. This correctly handles overlapping subscriptions and cancel-resubscribe scenarios. 3. **Churn calculation:** The anti-join pattern (LEFT JOIN + IS NULL) correctly identifies customers who were active in month M but not in month M+1. Critically, the `generate_series` starts from December 2023, which means January 2024's churn can be computed by looking at who was active in December 2023 but not January 2024. This is a thoughtful design choice. 4. **Churn rate:** The CASE expression correctly returns NULL for January 2024 (no prior month in scope) AND when previous month's active customers = 0 (avoiding division by zero). The `COALESCE(cc.churned_customers, 0)` ensures zero rather than NULL for months with no churn. 5. **Revenue:** Correctly filters to only 'success' and 'refunded' statuses, nets them, and uses COALESCE to handle months with no payments. The LEFT JOIN from months ensures all months appear even with no payments. 6. **Edge case - negative revenue:** Handled naturally since it's a subtraction. One minor observation: The generate_series goes to '2024-12-01', which means it generates 13 months (Dec 2023 through Dec 2024). The final WHERE clause filters to 2024 only. This is correct but generates one extra month of data (Dec 2023) solely for churn computation purposes — a smart approach. **EFFICIENCY:** - The approach is well-structured with clear CTE separation. Each CTE has a focused purpose. - The revenue CTE pre-filters with `WHERE m.month_start >= DATE '2024-01-01'` to avoid unnecessary computation. - The `month_active` CTE computes for all 13 months (including Dec 2023), which is necessary for churn but adds a small amount of extra work. - The anti-join pattern for churn is efficient and standard. - Overall, the query should perform well with appropriate indexes on `subscriptions(started_at, cancelled_at, customer_id)` and `payments(payment_date, status, amount)`. **READABILITY:** - Excellent formatting with clear indentation and alignment. - Meaningful CTE names (`month_active`, `active_counts`, `churned`, `churn_counts`, `revenue`). - Inline comments explain the purpose of each CTE. - The expert's note provides a thorough walkthrough of the design decisions. - The final SELECT is clean with clear column aliases. - The logic flow is easy to follow: generate months → find active customers → count them → find churned → count them → compute revenue → combine. **Potential Issues:** - None significant. The query is well-constructed and handles all edge cases as specified.

Analysis of Grok 4.1 Fast (High Think):

**CORRECTNESS:** Entry Y is mostly correct but has a notable issue with churn calculation for January 2024. 1. **Active subscription logic:** The condition `s.started_at <= m.month_end AND (s.cancelled_at IS NULL OR s.cancelled_at >= m.month_start)` correctly implements the active subscription definition. The `month_end` is computed as `gs + INTERVAL '1 MONTH' - INTERVAL '1 DAY'`, which is correct. 2. **Overlapping subscriptions / cancel-resubscribe:** Uses `SELECT DISTINCT m.month, s.customer_id` which correctly deduplicates customers with multiple active subscriptions. 3. **Churn calculation — CRITICAL ISSUE:** The `generate_series` starts from '2024-01-01', meaning `active_customers` only contains data for January 2024 onwards. The `prev_month` for January is set to NULL via the CASE expression. This means the churn calculation for January 2024 will always produce 0 churned customers (since `ac.month = m.prev_month` where prev_month is NULL will never match). However, the prompt says churn_rate should be NULL for January 2024 "since there's no prior month in scope." Wait — let me re-examine. For January 2024, `prev_month` is NULL. In the `churn_per_month` CTE, `JOIN active_customers ac ON ac.month = m.prev_month` — when `prev_month` is NULL, this join condition `ac.month = NULL` will never be true (NULL != anything in SQL). So January won't appear in `churn_per_month` at all, and the COALESCE in the final SELECT will make it 0. The churn_rate CASE checks `prev_mt.total_active_customers` — since `prev_month` for January is NULL, `prev_mt` will be NULL, so `prev_mt.total_active_customers IS NULL` is true, and churn_rate will be NULL. This is actually correct behavior for January. BUT — there's a deeper issue. For **February 2024**, the churn should include customers who were active in January but not February. Since `active_customers` does include January data, this works. The real question is: should January's churn consider December 2023? The prompt says "churn_rate should be NULL for January 2024 since there's no prior month in scope" — this implies January's churn_rate is NULL, and churned_customers for January would be 0 (since we can't compute it). Entry Y handles this correctly by not having December 2023 data. Actually, wait — Entry X generates December 2023 data and could theoretically show non-zero churned_customers for January while showing NULL churn_rate. But the prompt says churn_rate is NULL for January because "there's no prior month in scope." This suggests we shouldn't even try to compute churn for January. Entry Y's approach of showing 0 churned_customers for January is arguably more consistent with the NULL churn_rate. Entry X could show non-zero churned_customers for January but NULL churn_rate, which is a bit inconsistent. However, the prompt only explicitly says churn_rate should be NULL — it doesn't say churned_customers should be 0 for January. This is ambiguous. 4. **Churn rate:** The CASE expression handles NULL and zero denominators: `WHEN prev_mt.total_active_customers = 0 OR prev_mt.total_active_customers IS NULL THEN NULL`. However, there's a subtle issue: when `cp.churned_customers` is NULL (no churn that month), the expression `cp.churned_customers * 100.0 / prev_mt.total_active_customers` would be NULL, not 0. The COALESCE is applied in the SELECT output but the churn_rate CASE uses `cp.churned_customers` directly without COALESCE. So if there are no churned customers for a month but there were active customers the previous month, the churn_rate would be NULL instead of 0.00%. This is a **bug**. It should use `COALESCE(cp.churned_customers, 0)` in the churn_rate calculation. 5. **Revenue:** Uses `to_char(payment_date, 'YYYY-MM')` for grouping, which works but is less precise than date range comparisons. The `WHERE payment_date <= '2024-12-31'` correctly bounds the range. However, this approach doesn't use a LEFT JOIN from months, so months with zero payments won't appear in the revenue CTE — but the LEFT JOIN in the final SELECT with COALESCE handles this. 6. **Edge case - negative revenue:** Handled naturally. **EFFICIENCY:** - The use of `DISTINCT` in `active_customers` is functionally equivalent to GROUP BY but may have slightly different query plan implications. - The revenue CTE groups by `to_char(payment_date, 'YYYY-MM')` which prevents index usage on payment_date for grouping. - The `churn_per_month` CTE joins `months` with `active_customers` and then LEFT JOINs `active_customers` again — this is correct but the join on string month values is less efficient than date comparisons. - Overall efficiency is reasonable but slightly less optimal than Entry X due to string-based joins. **READABILITY:** - Reasonably well-formatted but less commented than Entry X. - CTE names are clear (`active_customers`, `monthly_totals`, `churn_per_month`, `revenue`). - The `months` CTE pre-computes several useful columns (month_end, prev_month, month_num) which is a nice organizational choice. - The expert's note is brief and less detailed than Entry X's. - The use of string-based month identifiers for joins is slightly less clean than date-based joins. **Potential Issues:** - **Bug in churn_rate calculation:** When churned_customers is NULL (from LEFT JOIN), the churn_rate will be NULL instead of 0.00% for months with active customers but no churn. This is a correctness issue. - String-based month joins are less robust than date-based joins (though functionally correct here). - No December 2023 data means January churn is always 0 — this is arguably correct per the prompt's intent but differs from Entry X's approach.

Analysis of Claude Opus 4.6 (High Think):

CORRECTNESS: Strong. Builds month series from 2023-12-01 through 2024-12-01, enabling prior-month comparisons for Jan churn-rate logic (though churn_rate is explicitly NULL for Jan per spec). Active definition matches prompt: started_at <= last_day_of_month and (cancelled_at IS NULL OR cancelled_at >= first_day_of_month). Dedupes overlapping subscriptions via GROUP BY month_start, customer_id. Churn computation: takes customers active in prev month and missing in next month, marking churn_month = prev+1; filtered to churn months in 2024, which aligns with definition (no active this month but active immediately preceding). Handles cancel+resubscribe in same month correctly because month_active is per-customer per-month; if they have any active sub they appear. Zero churn months handled with COALESCE in final select. Churn_rate: NULL for Jan; NULL when prev active =0 avoids div-by-zero. Revenue: nets success minus refunded within payment month; failed excluded by status filter; left join to months ensures 0 for empty months. Refunds exceeding success yields negative net_rev OK. Minor nits: month_active uses JOIN not LEFT, so months with no active customers absent until left-joined later—fine. The GROUP BY instead of DISTINCT is slightly odd but correct. Uses ::date casts; fine in Postgres. EFFICIENCY: Reasonable. Potentially heavy: month_active joins months (13 rows) to subscriptions (N) with non-sargable expression on month_end computed from month_start; still likely OK given small months table. Would benefit from indexes on subscriptions(started_at), subscriptions(cancelled_at), subscriptions(customer_id), payments(payment_date, status). Revenue CTE scans payments once with range predicate per month via join to months, which can still leverage payment_date index though join multiplies predicate evaluation; alternative GROUP BY date_trunc could be faster, but acceptable. READABILITY: Clear structure with CTEs, comments, consistent naming; final select straightforward. Overall production-approvable.

Analysis of Grok 4.1 Fast (High Think):

CORRECTNESS: Mostly correct for 2024-month reporting, but has a key risk around churn definition vs available scope. months CTE only includes 2024-01..2024-12. churn_per_month computes churn by taking customers active in prev_month (string YYYY-MM) and absent in current. For January, prev_month is NULL, so no rows; churned_customers becomes NULL then COALESCE to 0, and churn_rate NULL due to prev_mt NULL/0—matches spec (NULL churn_rate for Jan; churned_customers should arguably be 0, so OK). However, spec defines churned month based on immediately preceding month even if that month is Dec 2023. Entry Y cannot detect customers who were active in Dec 2023 but not Jan 2024; it will count 0 churn for Jan regardless. While churn_rate is NULL in Jan, churned_customers for Jan per definition would include those customers; prompt asks churned_customers for each month in 2024, so this is a correctness gap. Active definition is correct using month_end and month_start. Deduping via DISTINCT is good. Cancel+resubscribe same month is handled. Zero churn months are COALESCE’d to 0. Revenue: groups payments by to_char(payment_date,'YYYY-MM'); includes 2024 only. Uses payment_date <= '2024-12-31' which is fine for DATE type but would be risky for timestamp; given schema is DATE it’s OK. Failed excluded because CASE returns NULL for failed and SUM ignores NULL; COALESCE to 0 works. One subtlety: churn_per_month uses COUNT(DISTINCT ac.customer_id) but ac is already distinct in active_customers; harmless. EFFICIENCY: Similar. active_customers joins subscriptions to months (12 rows) with computed month_end; acceptable. churn_per_month joins active_customers twice; might be heavier but still manageable. Revenue aggregation is efficient (single scan of payments) and likely better than Entry X’s month join. READABILITY: Clean and well-formatted. Some unnecessary columns (month_num, prev_month strings) but understandable. Biggest issue is missing Dec 2023 month to compute Jan churned_customers per strict definition.

Analysis of Claude Opus 4.6 (High Think):

Correctness: Strong. Builds months from 2023-12 through 2024-12, enabling prior-month denominator for Jan 2024 totals and churn detection (though spec says churn_rate NULL for Jan; still useful for churned_customers in Jan, which is in-scope per definition). Active logic matches definition: started_at <= last day of month and cancelled_at NULL or >= first day. month_active dedupes by customer/month via GROUP BY, handling overlapping subs and cancel+resubscribe same month. Churned logic correctly finds customers active in prev month but not in current month (implemented as prev month_active rows anti-joined to next month); counts distinct customers. Zero churn months handled via COALESCE to 0. churn_rate: NULL for Jan 2024 as required; also NULL if prev active=0 avoids div0; otherwise uses prev_ac.total_active_customers (not COALESCE) which is fine because CASE gates it. Revenue: sums success minus refunded, excludes failed; joins on payment_date range [month_start, month_start+1 month) which is correct; months CTE includes 2023-12, but revenue CTE filters months >= 2024-01 so output months ok. One minor: revenue CTE has WHERE m.month_start >= 2024-01, but months already includes 2023-12 so that WHERE turns LEFT JOIN into effectively filtering months, but it's within revenue CTE so ok. Another small nit: active_counts for 2023-12 computed; final select filters months>=2024-01. All requirements met. Efficiency: Reasonable. Uses generate_series small (13 rows). month_active joins months x subscriptions; with indexes on subscriptions(started_at, cancelled_at, customer_id) could be ok; might still scan subscriptions 13 times logically but planner can optimize; still acceptable given limited months. Churn uses self-join on month_active; month_active could be large (customers*months active). Could be improved with window functions over active flags, but fine. Revenue aggregates payments with date range join to months; good because months small; index on payments(payment_date,status) helps. Readability: Clear CTE breakdown with comments. Uses month_start dates consistently; TO_CHAR at end. Slight complexity around intervals but understandable. Overall production-acceptable.

Analysis of Grok 4.1 Fast (High Think):

Correctness: Mostly correct but has a key spec mismatch for churn_rate in January. months CTE starts at 2024-01 only, and churn_rate CASE only checks denominator null/0; for Jan, prev_month is NULL so prev_mt is NULL, so churn_rate becomes NULL (good). However churned_customers for Jan: churn_per_month joins active_customers ac ON ac.month = m.prev_month; for Jan prev_month is NULL so join yields no rows -> churned_customers NULL/0. Depending on interpretation, Jan churned_customers should represent customers active in Dec 2023 and not active in Jan 2024, but Dec 2023 is outside generated months so it cannot detect January churn. The prompt defines churned month using immediately preceding month, and output is for each month in 2024. That implies Jan churn is based on Dec 2023; even though churn_rate must be NULL for Jan (no prior month in scope), churned_customers could reasonably still be expected for Jan. Entry X explicitly includes Dec 2023 to compute that; Entry Y cannot. If judge expects churned_customers=0 for Jan regardless, then fine, but that’s a semantic risk. Active logic: uses month_end = gs + 1 month - 1 day; compares started_at <= month_end and cancelled_at >= month_start, matches definition. DISTINCT handles overlapping subs and cancel/resub in same month. Revenue: aggregates payments by to_char(payment_date,'YYYY-MM') with WHERE payment_date >= 2024-01-01 AND <= 2024-12-31. Using <= 12-31 is okay for DATE type; if timestamp it could miss later times, but schema says DATE. Excludes failed by omission (CASE WHEN success/refunded THEN amount END); COALESCE(SUM(...),0) fine. Net can be negative. Edge cases: months with zero churn show 0 via COALESCE(cp.churned_customers,0). Division by zero avoided. One more subtlety: churn_per_month counts DISTINCT customer_id but active_customers already distinct; fine. However uses string month keys ('YYYY-MM'); consistent but less robust than dates. Efficiency: generate_series 12 rows; active_customers is months x subscriptions. churn_per_month does joins on active_customers twice; might be heavy but acceptable. Use of to_char for joining/grouping can prevent index use and adds function calls; better to keep dates. Revenue groups by formatted month; again function on payment_date may bypass index unless expression index. Readability: Quite readable, with month_start/month_end fields and month_num ordering. Slightly overengineered prev_month as string and extra columns. Joins on string months are less clear/safer than date joins.

Analysis of Claude Opus 4.6 (High Think):

Entry X demonstrates a thorough and well-structured approach to this complex problem. CORRECTNESS: - Generates months from Dec 2023 to Dec 2024, which is crucial for computing January 2024's churn (needs December 2023 data). This is a smart design decision. - The active subscription logic correctly implements: started_at <= last day of month AND (cancelled_at IS NULL OR cancelled_at >= first day of month) - Uses GROUP BY to deduplicate overlapping subscriptions - handles the upgrade mid-month case correctly - The churned CTE correctly identifies customers who were active in month M but not in M+1, using an anti-join pattern - Revenue calculation properly nets success minus refunded, excludes failed payments - Handles all edge cases: COALESCE for zero churned customers, NULL for January churn_rate, NULL for division by zero - The date arithmetic is correct throughout EFFICIENCY: - Uses generate_series efficiently for month generation - The anti-join pattern (LEFT JOIN + IS NULL) is a standard efficient approach - Revenue CTE filters by status IN ('success', 'refunded') which could help with index usage - Multiple CTEs may result in multiple passes through the data, but this is necessary for the complexity of the problem - Could benefit from indexes on subscriptions(started_at, cancelled_at, customer_id) and payments(payment_date, status) READABILITY: - Well-organized with clear CTE names that describe their purpose - Good inline comments explaining the logic - The expert's note provides excellent documentation of the design decisions - Consistent formatting and indentation - The logic flow is easy to follow: months → active customers → counts → churn → revenue → final assembly POTENTIAL ISSUES: - None significant. The query is comprehensive and handles all stated requirements.

Analysis of Grok 4.1 Fast (High Think):

Entry Y presents a more compact solution but has some notable differences and potential issues. CORRECTNESS: - Generates months only for 2024 (Jan-Dec), which means it cannot compute churn for January 2024 properly since it has no December 2023 data to compare against. The prev_month for January is NULL, so the churn calculation would fail to identify customers who were active in Dec 2023 but not in Jan 2024. - Wait - looking more carefully: the prev_month for January is set to NULL, and the churn_per_month CTE joins on ac.month = m.prev_month. For January, prev_month is NULL, so no rows would match, resulting in 0 churned customers for January. This is actually correct behavior since we can't compute churn without prior month data. - Active subscription logic is correct: started_at <= month_end AND (cancelled_at IS NULL OR cancelled_at >= month_start) - Uses DISTINCT to handle overlapping subscriptions - correct approach - Churn calculation uses anti-join pattern correctly - Revenue calculation is correct but uses a different approach - grouping by to_char(payment_date, 'YYYY-MM') directly from payments table EFFICIENCY: - Slightly more compact with fewer CTEs - The revenue CTE groups directly from payments without joining to months first, which could be more efficient - Uses row_number() for ordering which is a nice touch but adds minor overhead - The churn_per_month CTE uses COUNT(DISTINCT) which may be slightly less efficient than Entry X's approach READABILITY: - More compact but slightly harder to follow - Less inline documentation - The expert's note is brief and doesn't explain design decisions as thoroughly - The months CTE packs a lot of logic (month_end, prev_month, month_num) which is efficient but dense POTENTIAL ISSUES: - The churn_rate CASE statement doesn't explicitly handle January 2024 - it relies on prev_mt being NULL. This works but is less explicit than Entry X's approach. - For January 2024, cp.churned_customers would be NULL (no rows in churn_per_month for January since prev_month is NULL), so the COALESCE handles it, but the churn_rate calculation would use NULL churned_customers which would result in NULL anyway. Actually, looking again - if there are no rows for January in churn_per_month, then cp.churned_customers is NULL, and the CASE would return NULL because prev_mt is also NULL. This is correct behavior. - The revenue calculation doesn't explicitly handle months with no payments - relies on COALESCE in the final SELECT, which is fine.

Analysis of Claude Opus 4.6 (High Think):

Entry X demonstrates a well-structured approach with clear CTEs that logically separate concerns: CORRECTNESS: + Correctly generates months from Dec 2023 to Dec 2024, which is essential for computing January 2024's churn (needs Dec 2023 active customers) + The active subscription logic is correct: started_at <= last day of month AND (cancelled_at IS NULL OR cancelled_at >= first day of month) + Uses GROUP BY to deduplicate overlapping subscriptions - handles the upgrade mid-month case correctly + Churn calculation uses proper anti-join pattern (LEFT JOIN + IS NULL) + Revenue calculation correctly excludes 'failed' payments and nets success minus refunded + Handles all edge cases: COALESCE for zero churn, NULL for Jan 2024 churn_rate, NULL for division by zero + The date arithmetic is correct using interval operations EFFICIENCY: + Uses generate_series efficiently for month generation + The anti-join pattern for churn is efficient + Revenue CTE filters early with WHERE clause + Could potentially benefit from indexing on (started_at, cancelled_at, customer_id) and (payment_date, status) - Multiple passes through month_active for active_counts and churned calculations READABILITY: + Excellent structure with well-named CTEs + Clear comments explaining the logic + Consistent formatting + The expert note provides good documentation of design decisions + Easy to follow the logical flow from months → active → churn → revenue → final output POTENTIAL ISSUES: - The generate_series includes Dec 2024 which is beyond the required scope, but it's filtered out in the final WHERE clause - Minor: could use DATE_TRUNC for cleaner date handling in some places

Analysis of Grok 4.1 Fast (High Think):

Entry Y takes a more compact approach but has some notable differences: CORRECTNESS: + Uses generate_series for 2024 months only + Active subscription logic appears correct with proper date comparisons + Uses DISTINCT to handle overlapping subscriptions + Churn calculation uses anti-join pattern - CRITICAL ISSUE: The months CTE only generates 2024 months, so there's no December 2023 data for active_customers. This means the churn calculation for January 2024 will be incorrect - it will show 0 churned customers because there's no prev_month data to compare against (prev_month for Jan is NULL, so the JOIN in churn_per_month won't find any ac.month = NULL matches) - Actually, looking more carefully: prev_month for January is set to NULL, so the JOIN `ac ON ac.month = m.prev_month` will never match for January, resulting in 0 churned customers. This is arguably correct behavior since we can't calculate churn without prior month data, but it differs from Entry X's approach which actually computes Dec 2023 active customers. + Revenue calculation correctly handles success minus refunded + Uses COALESCE appropriately for NULL handling EFFICIENCY: + Compact query with fewer CTEs + Uses DISTINCT which may be less efficient than GROUP BY in some cases + Revenue CTE groups by formatted string which is slightly less efficient than date comparison - The churn_per_month CTE does a COUNT(DISTINCT) which may be redundant given the anti-join already produces distinct results READABILITY: + Reasonably well-structured + Uses meaningful CTE names - Less detailed comments/documentation - The prev_month calculation in the months CTE adds complexity - The month_num column is only used for ordering, which is fine but adds slight overhead POTENTIAL ISSUES: - The churn_rate CASE doesn't explicitly check for January 2024 - it relies on prev_mt being NULL. This works but is less explicit than Entry X - The revenue WHERE clause uses <= '2024-12-31' which could miss payments on Dec 31 depending on time component (though DATE type should be fine)