AI Elo

Analysis of Claude Opus 4.6 (High Think):

**CORRECTNESS:** 1. **90-day window**: Uses `>= '2024-04-02'` and `< '2024-07-01'`. A trailing 90-day window ending on 2024-06-30 should include dates from 2024-04-02 through 2024-06-30 (inclusive). April has 30 days, May 31, June 30 = 91 days from April 1. From April 2 to June 30 inclusive = 90 days. This is correct. 2. **Session attribution**: Uses `DISTINCT ON (session_id) ... ORDER BY session_id, event_timestamp` to get the first event's referral_source per session, with COALESCE to 'direct'. This is a clean, correct PostgreSQL idiom. 3. **Funnel step timestamps**: Uses MIN with CASE for each event_type per session. Correct approach. 4. **Strict chronological order**: Checks `pv_ts < atc_ts AND atc_ts < cs_ts AND cs_ts < pur_ts`. This enforces strict ordering. Also checks all are NOT NULL. Correct. 5. **Net revenue**: Pre-aggregates refunds per order_id, then joins to orders, then aggregates per session_id. This correctly avoids fan-out from multiple refunds per order. The formula `SUM(o.order_total - COALESCE(r.refund_sum, 0))` is correct. 6. **Revenue attribution**: Only includes revenue for funnel-complete sessions via `CASE WHEN sc.is_complete`. Correct. 7. **Time to purchase**: Uses `EXTRACT(EPOCH FROM (pur_ts - pv_ts)) / 60.0` for funnel-complete sessions only. Correct. 8. **Edge cases**: NULL referral_source → 'direct' ✓, partial/multiple refunds ✓, no refunds → 0 ✓, minimum 5 sessions filter ✓. 9. **Potential issue**: The `order_net` CTE doesn't filter orders to the 90-day window or to funnel-complete sessions. It joins ALL orders. However, since it's LEFT JOINed to `session_classified` which is already filtered, and revenue is only summed for `is_complete` sessions, this is functionally correct — just potentially less efficient. There could be a subtle issue: if a session has orders that are outside the 90-day window, those would still be included in net_rev. The prompt says "purchases attributed to fully-completed-funnel sessions" — it doesn't explicitly say orders must be within the window, but the session itself is within the window. This is a minor ambiguity but the approach is reasonable. 10. **Another potential issue**: The `order_net` CTE groups by `session_id`, summing all orders for that session. If a session has multiple orders, all their net revenues are summed. This seems correct per the requirement. **EFFICIENCY:** - Events are filtered early in the first CTE, reducing data volume for subsequent operations. - DISTINCT ON is efficient for getting the first row per group. - Refunds are pre-aggregated before joining to orders — good practice to avoid fan-out. - The query structure is well-organized with clear separation of concerns. **READABILITY:** - Excellent use of CTEs with descriptive names. - Good inline comments explaining each CTE's purpose. - Clean formatting and indentation. - The `is_complete` boolean flag makes the final SELECT cleaner. - The expert note accurately describes the approach.

Analysis of Gemini 3 Flash Preview (High Think):

**CORRECTNESS:** 1. **90-day window**: Uses `session_start >= '2024-06-30'::timestamp - INTERVAL '89 days'` which equals `2024-04-02`. Combined with `< '2024-07-01'`, this gives April 2 through June 30 inclusive. This is correct and equivalent to Entry X's approach. 2. **Session attribution**: Uses `FIRST_VALUE(COALESCE(referral_source, 'direct')) OVER (PARTITION BY session_id ORDER BY event_timestamp)` — this correctly gets the referral_source of the first event per session. However, there's a subtle issue: the window functions are computed BEFORE the date filter. The `session_metrics` CTE processes ALL events in `user_events` (no WHERE clause), then `distinct_sessions` filters by date. This means window functions like MIN(...) OVER (PARTITION BY session_id) are computed across ALL events for a session, not just those within the 90-day window. If a session spans beyond the window boundaries (unlikely but possible), the earliest timestamps could be from outside the window. More importantly, this is a **significant performance concern** — all rows in user_events are processed by window functions before filtering. 3. **Window function approach**: Using window functions instead of GROUP BY means every row gets all the computed columns, then DISTINCT is used to deduplicate. This is less efficient than a GROUP BY approach. The `MIN(...) OVER (PARTITION BY session_id)` computes the same value for every row in the partition, which is wasteful. 4. **Funnel validation**: The CASE expression `WHEN t_pv < t_atc AND t_atc < t_cos AND t_cos < t_p` implicitly handles NULLs because any comparison with NULL returns NULL/false. So sessions missing any step will not be counted as complete. This is correct but implicit — less readable than Entry X's explicit NULL checks. 5. **Net revenue**: Same approach as Entry X — pre-aggregates refunds, joins to orders, groups by session_id. Correct. 6. **Revenue and time attribution**: Only for funnel-complete sessions. Correct. 7. **Edge cases**: NULL → 'direct' ✓, refunds handled ✓, minimum 5 sessions ✓. 8. **Potential issue with window function date filtering**: The `session_start` used for filtering is `MIN(event_timestamp) OVER (PARTITION BY session_id)` computed over ALL events. If a session has events both inside and outside the window, the session_start might be outside the window, causing the session to be excluded even though it has events within the window. Conversely, Entry X filters events first, then computes session-level metrics. This is a meaningful semantic difference. The prompt says "trailing 90-day window" — it's ambiguous whether this means sessions that started in the window or events that occurred in the window. Entry Y's approach of filtering by session_start is arguably more correct for "sessions in the window" but the lack of pre-filtering events is problematic for performance and could lead to funnel timestamps from outside the window being used. **EFFICIENCY:** - Major concern: Window functions are applied to the ENTIRE user_events table before any filtering. For a large e-commerce platform, this could be extremely expensive. - Using DISTINCT after window functions is less efficient than GROUP BY. - The approach creates many duplicate rows that need deduplication. **READABILITY:** - More compact but less readable than Entry X. - No comments in the SQL itself. - Using `GROUP BY 1` is less explicit than `GROUP BY column_name`. - The repeated funnel condition `t_pv < t_atc AND t_atc < t_cos AND t_cos < t_p` in three places is not DRY — Entry X's `is_complete` flag is cleaner. - Column alias `source` is less descriptive. - The expert note mentions "April 2 to June 30" which is correct.

Analysis of Claude Opus 4.6 (High Think):

CORRECTNESS: Strong overall. Correctly filters events to trailing 90-day window ending 2024-06-30 using [2024-04-02, 2024-07-01). Attributes referral_source from the first event within the window via DISTINCT ON (session_id) ORDER BY event_timestamp; NULL→'direct' handled. Funnel steps computed as earliest timestamp per event_type per session using MIN(CASE...), and strict order enforced with < comparisons and all steps non-NULL—meets requirement that earliest of each step must be strictly after previous earliest. Revenue: pre-aggregates refunds per order_id and then sums (order_total - refunds) per session, avoiding fan-out; missing refunds treated as 0. Final aggregation counts distinct sessions and distinct completed sessions, computes rate, net_revenue and avg time only for completed sessions, rounds as required, excludes sources with <5 sessions, orders by net_revenue desc. Potential issues: (1) Attribution is based on first event in window, not necessarily first event of the session if the session started before window and continues into it. Requirement says trailing 90-day window ending on 2024-06-30; ambiguous whether inclusion is by event time or session start—X assumes event-based inclusion. (2) order_net includes all orders in orders table regardless of order_timestamp window; but it only contributes to sessions that appear in events_in_window (session_classified). If a session in-window has orders outside window (unlikely but possible), it would be included; requirement suggests purchases in window. Ideally should filter orders by order_timestamp within window or tie to purchase event timestamp. (3) If multiple orders per session, summing per session seems fine; requirement says purchases attributed to fully-completed-funnel sessions only, not explicitly per order. (4) DISTINCT ON without tiebreaker for equal timestamps could be nondeterministic but rare. EFFICIENCY: Reasonable. Filters user_events early in events_in_window, reducing volume for later CTEs. GROUP BY session_id for funnel steps is efficient with index on (event_timestamp) and/or (session_id, event_timestamp). DISTINCT ON per session can use index (session_id, event_timestamp). Pre-aggregating refunds is good. Potential heavy step: joining session_source to funnel_steps is fine. Overall likely performant. READABILITY: Clear CTE decomposition and comments. Naming is good. Final SELECT is straightforward. Production-approvable with minor tightening around order_timestamp window semantics.

Analysis of Gemini 3 Flash Preview (High Think):

CORRECTNESS: Mostly aligned but has some semantic and robustness issues. Uses window functions over entire user_events, then filters sessions by session_start (MIN event_timestamp) within window. That may better reflect “sessions in trailing window” if defined by first event time; however requirement specifies “sessions whose first event in that session had that referral_source” and metrics for trailing 90-day window; ambiguous whether session inclusion should be based on session’s first event or any event. Y’s approach is consistent with session_start-based inclusion. Referral attribution uses FIRST_VALUE(COALESCE(referral_source,'direct')) over ORDER BY event_timestamp—works, but without explicit frame (Postgres default frame for FIRST_VALUE with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW); FIRST_VALUE still returns first row of frame, but RANGE with duplicate timestamps can make results nondeterministic if multiple rows share timestamp—minor. Funnel completion: checks strict inequalities t_pv < t_atc < t_cos < t_p, but does not explicitly require non-NULL; in SQL, comparisons with NULL yield NULL and CASE WHEN treats NULL as false, so it effectively excludes incomplete funnels—ok. Net revenue: aggregates refunds per order_id, then per session_id; same as X. But like X, it doesn’t filter orders by time window nor ensure order corresponds to purchase event; any order for a completed session will count even if outside window. Also, session_metrics scans entire user_events without date filter, which could be expensive and also potentially mis-handle attribution if sessions have events after 2024-06-30 but started before—filtered out anyway by session_start; fine. Bigger correctness risk: session_start computed as MIN(event_timestamp) over all events for session (no window filter). If a session started before window but had events during window, it’s excluded—might or might not match business requirement. X includes those sessions if any event falls in window (and uses first in-window event as “first”), which could be wrong the other way. Given typical trailing-window reporting, session_start-based is arguably preferable, but requirement says “first event in that session had that referral_source” and seems to imply session’s actual first event; Y does that. EFFICIENCY: Potentially poor on large datasets because it computes multiple window functions across all user_events (no predicate pushdown until after DISTINCT). This can force a full scan and sort per session_id. Better would be to filter to window first, or at least restrict to sessions with session_start in window via pre-aggregation. order_revenue is fine. READABILITY: Compact but less explicit than X. Window-function approach is clever but harder to reason about for interview/production. Some naming inconsistencies (t_cos vs checkout_start). Comments are minimal. Overall: Logic is close, but performance concerns and slight window-frame nondeterminism make it less production-ready than X.

Analysis of Claude Opus 4.6 (High Think):

CORRECTNESS: Strong. Correct 90-day window (>=2024-04-02 and <2024-07-01). Attributes session to first event’s referral_source via DISTINCT ON ordered by event_timestamp and coalesces NULL to 'direct' as required. Funnel step timestamps use MIN per event_type per session (earliest). Strict ordering enforced with pv_ts < atc_ts < cs_ts < pur_ts and requires all non-null. Revenue limited to complete sessions by CASE; net revenue computed as SUM(order_total - summed refunds) with refunds pre-aggregated per order_id (handles multiple/partial refunds and missing refunds). avg_time_to_purchase uses pv_ts to pur_ts for complete sessions only. Excludes sources with <5 sessions via HAVING. Orders by net_revenue. Potential gaps: (1) session_source is built from events_in_window, so if a session started before window but has events inside, it will attribute based on first in-window event, not true first event overall; requirement says trailing 90-day window ending 2024-06-30—ambiguous whether to include sessions whose first event is within window (likely yes). This query effectively restricts attribution to sessions with first event within window only if their earliest event is in window; if session has earlier event outside window, it still may appear but with wrong first-event source and possibly missing funnel steps outside window. (2) order_net includes all orders for that session regardless of order_timestamp/window; may be acceptable since requirement ties to purchases in those sessions, but if orders can occur outside window it could leak. (3) total_sessions counts sessions present in events_in_window; acceptable. Overall production-acceptable with minor clarification about session boundary/window. EFFICIENCY: Good use of prefilter events to window, reducing work. GROUP BY session_id for funnel_steps is efficient with index on (event_timestamp) and (session_id,event_type,event_timestamp). DISTINCT ON (session_id) needs sort; index on (session_id,event_timestamp) helps. Refund pre-aggregation avoids join fan-out. order_net groups orders by session; index on orders(session_id) and refunds(order_id) helps. READABILITY: Clean CTE decomposition, comments, clear naming, straightforward logic. DISTINCT ON is Postgres-specific but allowed. Overall readable and maintainable.

Analysis of Gemini 3 Flash Preview (High Think):

CORRECTNESS: Mostly correct but with some concerns. Uses window functions over entire user_events, then DISTINCT to per-session row and filters by session_start between '2024-06-30' - 89 days and < '2024-07-01'. This approximates 90-day window but is subtly off: trailing 90 days ending 2024-06-30 inclusive should start 2024-04-02; subtracting 89 days from 2024-06-30 yields 2024-04-02 only if you interpret inclusivity carefully; but since they use timestamps, any time-of-day can shift inclusion. Also, filters on session_start (MIN event_timestamp overall), not on events in window. That may be better if definition is sessions whose first event is within window; however it means sessions starting in window but with later funnel events outside window are still counted and may be considered complete (because t_p etc computed over all time). Requirement seems to want metrics within trailing window; usually both sessions and their funnel completion should be evaluated within the window’s data. Entry X at least constrains to window events; Y does not. Similarly, revenue joins orders by session without order_timestamp filter, so could include orders outside window; X also has this issue, but Y’s unconstrained funnel timestamps amplify it. Funnel logic: checks t_pv < t_atc < t_cos < t_p but does not explicitly ensure non-null; in SQL, NULL comparisons yield NULL so CASE WHEN returns NULL and not counted, which is fine. Uses earliest timestamps via MIN(CASE...) OVER; OK. Attribution: FIRST_VALUE(COALESCE(referral_source,'direct')) over (PARTITION BY session_id ORDER BY event_timestamp) is correct for first event overall. Edge-case: if first event has NULL referral_source and later has non-null, FIRST_VALUE after COALESCE yields 'direct' as desired. EFFICIENCY: Potentially heavy. Computes multiple window functions over entire user_events without prefilter; can be expensive on large tables (full scan + sort per session_id). Could be mitigated with indexes on (session_id,event_timestamp) but still more work than necessary. DISTINCT on wide rows can be expensive. Filtering happens after windows, so little pruning. READABILITY: Compact but less explicit than X; window-function approach is moderately readable but harder to reason about vs grouped aggregates. Aliases t_cos vs checkout_start are fine. Overall acceptable but with performance/window-scope ambiguity issues.

Analysis of Claude Opus 4.6 (High Think):

Entry X demonstrates a well-structured approach with clear CTE organization: CORRECTNESS: - The 90-day window calculation uses '2024-04-02' which is technically 89 days before June 30 (should be April 2 for 90 days ending June 30, inclusive). This is actually correct for a "trailing 90-day window ending on 2024-06-30" if we interpret it as 90 days including June 30. - Uses DISTINCT ON correctly to get the first event's referral_source per session - Properly handles NULL referral_source with COALESCE to 'direct' - Funnel completion logic correctly checks strict chronological order with all four steps - Refunds are pre-aggregated before joining to orders, preventing fan-out issues - Correctly uses LEFT JOIN for orders that may have no refunds - The HAVING clause properly filters referral_sources with fewer than 5 sessions - Uses COUNT(DISTINCT session_id) which is appropriate EFFICIENCY: - Pre-aggregating refunds before joining is an excellent optimization - The CTE structure allows for clear logical separation and potential query plan optimization - DISTINCT ON is PostgreSQL-specific but efficient for this use case - The join strategy is sound with appropriate grouping READABILITY: - Excellent CTE naming that clearly describes each step's purpose - Good inline comments explaining the logic - Well-formatted with consistent indentation - The expert note accurately describes the approach POTENTIAL ISSUES: - The date calculation '2024-04-02' for a 90-day window ending June 30 is correct (April 2 to June 30 = 90 days inclusive) - The query correctly handles all specified edge cases

Analysis of Gemini 3 Flash Preview (High Think):

Entry Y takes a more compact approach using window functions: CORRECTNESS: - The 90-day window calculation uses '2024-06-30' - INTERVAL '89 days' which would give April 2, matching the intended 90-day window. However, filtering on session_start rather than event_timestamp could potentially miss sessions where the first event is outside the window but other events are inside. - Uses FIRST_VALUE window function to get referral_source - this works but creates redundant rows that need DISTINCT - The funnel completion check (t_pv < t_atc AND t_atc < t_cos AND t_cos < t_p) doesn't explicitly check for NULL values. If any timestamp is NULL, the comparison will return NULL/false, which happens to work correctly but is implicit rather than explicit. - Refund handling is correct with pre-aggregation - Uses COUNT(*) instead of COUNT(DISTINCT session_id) - this works because distinct_sessions already has unique session_ids EFFICIENCY: - The window function approach in session_metrics creates many redundant rows (one per event) that then need DISTINCT - this is less efficient than Entry X's approach - Computing window functions for all events before filtering by date is potentially wasteful for large datasets - The DISTINCT operation on a potentially large intermediate result could be expensive READABILITY: - More compact but less clear about the logic - Fewer comments explaining the approach - The repeated funnel completion condition (t_pv < t_atc AND t_atc < t_cos AND t_cos < t_p) appears three times, which could be error-prone if modifications are needed - CTE names are less descriptive POTENTIAL ISSUES: - The window function approach processes all events before filtering, which could be inefficient - The implicit NULL handling in comparisons works but is less explicit - The filtering happens after window function computation, meaning unnecessary work on out-of-window data

Analysis of Claude Opus 4.6 (High Think):

Entry X presents a well-structured solution with 4 clearly defined CTEs: CORRECTNESS: + Properly filters events to 90-day window (April 2 to June 30, 2024) + Uses DISTINCT ON correctly to get the first event's referral_source per session + Correctly handles NULL referral_source → 'direct' with COALESCE + Properly calculates earliest timestamps for each funnel step using MIN with CASE + Strict chronological order validation (pv_ts < atc_ts < atc_ts < cs_ts < pur_ts) + Pre-aggregates refunds before joining to orders to avoid fan-out issues + Correctly handles sessions with no refunds via COALESCE + Uses HAVING to filter referral_sources with < 5 sessions + Properly rounds funnel_completion_rate to 2 decimal places and avg_time to 1 decimal place POTENTIAL ISSUES: - The 90-day window calculation uses '2024-04-02' which is 89 days before June 30, not 90 days. A "trailing 90-day window ending on 2024-06-30" should include 90 days, meaning April 2 to June 30 is actually correct (inclusive of both endpoints = 90 days). This is a minor interpretation issue. - The funnel completion check uses strict inequality (<) which is correct per requirements EFFICIENCY: + Pre-aggregating refunds in a subquery prevents row multiplication + DISTINCT ON is efficient for getting first row per group in PostgreSQL + Appropriate use of CTEs for logical separation + Single pass through events for funnel step timestamps READABILITY: + Clear CTE naming (events_in_window, session_source, funnel_steps, session_classified, order_net) + Good inline comments explaining each CTE's purpose + Well-formatted with consistent indentation + Boolean is_complete flag makes the logic clear

Analysis of Gemini 3 Flash Preview (High Think):

Entry Y uses a more compact approach with window functions: CORRECTNESS: + Uses FIRST_VALUE window function to get referral_source from first event + Handles NULL → 'direct' conversion with COALESCE + Calculates earliest timestamps per funnel step using MIN window functions + Strict chronological order validation (t_pv < t_atc < t_atc < t_cos < t_p) + Pre-aggregates refunds before joining POTENTIAL ISSUES: - The 90-day window calculation: '2024-06-30' - INTERVAL '89 days' gives April 2, which matches Entry X. However, this is a slightly confusing way to express "trailing 90 days" - subtracting 89 days from June 30 gives April 2, which when including both endpoints gives 90 days. This is correct but less intuitive. - The window function approach in session_metrics computes window functions for EVERY row before deduplication in distinct_sessions. This is less efficient than Entry X's approach. - The filter is on session_start (MIN timestamp), not on individual events. This could potentially include sessions that started in the window but have events outside it - though this is a minor semantic difference. - Uses COUNT(*) instead of COUNT(DISTINCT session_id) - this works because distinct_sessions already has unique sessions, but it's less explicit about intent. EFFICIENCY: - Window functions computed over entire user_events table before filtering to the date range is inefficient - The DISTINCT step after window functions means redundant computation - This approach would be significantly slower on large datasets READABILITY: + Compact code with fewer CTEs + Clear column aliasing - Less explicit about what each step does - The repeated funnel condition (t_pv < t_atc AND t_atc < t_cos AND t_cos < t_p) in multiple places is less DRY than Entry X's is_complete flag - No inline comments