AI Elo

Analysis of Claude Opus 4.6 (High Think):

**CORRECTNESS:** Entry X is highly correct and handles all specified edge cases: 1. **90-day window**: `snapshot_date >= CURRENT_DATE - 89 AND snapshot_date <= CURRENT_DATE` correctly captures the last 90 days inclusive (today minus 89 days through today = 90 days). The upper bound `<= CURRENT_DATE` is a nice defensive addition, though technically unnecessary if snapshots are only taken for past/current dates. 2. **Gaps-and-islands technique**: The classic `snapshot_date - ROW_NUMBER()` approach is correctly applied. By filtering to only stockout days first, any non-stockout day or missing day naturally breaks the consecutive sequence, producing different group keys. This is sound. 3. **Two-level ranking**: This is a notable strength. Entry X first picks the best streak per (warehouse, sku) via `best_per_sku`, then picks the best SKU per warehouse via `ranked`. This two-level approach is technically more rigorous — it ensures that for each SKU, only its single best streak is considered before comparing across SKUs within a warehouse. However, in practice, this extra step doesn't change the final result compared to a single-level ranking directly on all streaks, because the same tie-breaking rules (longest streak → most recent end → smallest sku_id) would produce the same winner at the warehouse level regardless. Still, it shows careful thinking about the problem structure. 4. **Tie-breaking**: Correctly implements all three levels: `streak_length_days DESC, streak_end_date DESC, sku_id ASC`. 5. **Edge cases**: Warehouses with no stockouts produce no rows (correct). Missing dates break streaks (correct, by virtue of the gaps-and-islands technique). Multiple streaks per SKU are handled (correct). 6. **Minor concern**: The `snapshot_date - CAST(ROW_NUMBER() ... AS INT)` syntax works in PostgreSQL (date minus integer = date). This is standard PostgreSQL behavior. The note says "PostgreSQL/SQL Server style" — in SQL Server, date arithmetic works differently, but the prompt allows PostgreSQL style, so this is fine. **EFFICIENCY:** The query processes the data in a clean pipeline. The initial filter in `stockout_days` reduces the dataset early. The extra CTE (`best_per_sku`) adds one additional window function pass, but this is minimal overhead. Overall efficient. **READABILITY:** Excellent. Well-commented CTEs with clear names (`stockout_days`, `streaks`, `streak_stats`, `best_per_sku`, `ranked`). Each step is logically separated and explained. The accompanying note clearly describes the approach. This is production-quality code that would be easy to maintain.

Analysis of Gemini 3 Pro Preview (High Think):

**CORRECTNESS:** Entry Y is also correct and handles the core requirements: 1. **90-day window**: `snapshot_date >= CURRENT_DATE - 89` correctly captures the last 90 days. It omits the upper bound `<= CURRENT_DATE`, which is fine in practice (future snapshots are unlikely to exist, and if they did, arguably they shouldn't be included — but this is a very minor point). 2. **Gaps-and-islands technique**: Same correct approach as Entry X. Filters to stockout days first, then uses `snapshot_date - ROW_NUMBER()` to identify consecutive groups. 3. **Single-level ranking**: Entry Y goes directly from streaks to ranking per warehouse, skipping the intermediate "best streak per SKU" step that Entry X includes. This is actually still correct — when you rank all streaks across all SKUs within a warehouse by `streak_length_days DESC, streak_end_date DESC, sku_id ASC`, the winner will be the same as if you had first picked each SKU's best streak and then compared. The tie-breaking rules are transitive in this way. So the result is correct. 4. **Tie-breaking**: Correctly implements all three levels: `streak_length_days DESC, streak_end_date DESC, sku_id ASC`. 5. **Edge cases**: Warehouses with no stockouts produce no rows (correct). Missing dates break streaks (correct). Multiple streaks per SKU are handled (correct — even without the intermediate step, the ranking naturally picks the best overall streak). 6. **Same minor note about date arithmetic**: Uses `CAST(... AS INTEGER)` which is equivalent to Entry X's `CAST(... AS INT)`. **EFFICIENCY:** Slightly more efficient than Entry X due to fewer CTEs and one fewer window function pass. The single-level ranking is sufficient and avoids the extra `best_per_sku` step. In practice, the difference is negligible, but Entry Y is marginally leaner. **READABILITY:** Functional but notably less polished than Entry X. Uses `SELECT *` in the `ranked` CTE, which is generally discouraged in production code (though acceptable here since the columns are well-defined from the previous CTE). Minimal comments — only the CTE names provide context. The accompanying note is brief but adequate. The formatting is compact, which some might prefer for simplicity but others might find harder to maintain. No inline comments explaining the logic steps.

Analysis of Claude Opus 4.6 (High Think):

Correctness: Largely correct. Filters to last 90 days inclusive (CURRENT_DATE-89 through CURRENT_DATE) and stockout condition (on_hand-reserved<=0). Uses gaps-and-islands via snapshot_date - row_number to ensure only consecutive calendar dates are grouped; because it filters to stockout rows only, any non-stockout day is absent and thus creates a gap (difference changes) so streak breaks as required. Missing snapshot dates also break for same reason. Computes each island’s length and end date correctly. Handles multiple streaks per SKU by aggregating per grp then selecting best streak per (warehouse, sku) with ROW_NUMBER ordered by length desc, end desc. Then selects best SKU per warehouse with required tie-breaks including sku_id asc. Warehouses with no stockouts absent. Potential portability caveat: date arithmetic `snapshot_date - CAST(row_number AS INT)` is PostgreSQL-ish; in SQL Server you’d need DATEADD, but prompt allows PostgreSQL/SQL Server style; still, it claims standards-compliant—this expression isn’t fully standard. Also `CAST(... AS INT)` may not be necessary; and `snapshot_date - integer` returns date in Postgres; good. Efficiency: Good use of window functions and grouping; filters early to last 90 days and stockouts, minimizing rows. Would benefit from index on inventory_snapshots(snapshot_date, warehouse_id, sku_id) or (warehouse_id, sku_id, snapshot_date) (already PK includes those columns), and perhaps include on_hand,reserved in covering index if heavy. Two window passes + group by, acceptable. Readability: Well-structured CTEs with comments, clear tie-breaking, separation of concerns; production-approvable with minor portability note.

Analysis of Gemini 3 Pro Preview (High Think):

Correctness: Partially correct but misses a key edge case: it ranks streaks directly per warehouse without first selecting the maximum streak per SKU. Requirement says consider the maximum streak per SKU, then pick best SKU per warehouse. As written, if a warehouse has multiple streaks for the same sku, one of them could win the warehouse ranking (fine), but also two streaks of same sku compete with other skus; still the warehouse-level winner will be the best streak overall, which usually implies the SKU with the best streak, but tie-breaking can go wrong: if the same sku has two streaks, it might block correct sku_id tie-break because ranking is by sku_id after end_date—however row selection is among streaks, not skus. Example: SKU 10 has two streaks length 5 ending yesterday and length 5 ending today; SKU 11 has length 5 ending today. Ranking picks SKU10 ending today (ok). But requirement’s step of “max per SKU” matters especially when later tie-breaking by sku_id among SKUs with equal best streak; selecting among streak rows could pick a non-max streak for a SKU if a different SKU’s best streak ties but a SKU has a shorter streak with more recent end that outranks another SKU’s longer streak? Actually ordering prioritizes length then end, so shorter can’t outrank longer; so the main issue is not selecting one row per SKU before warehouse ranking could still yield correct final row, but it violates spec and could matter if additional per-SKU constraints existed. More concrete bug: date filtering is incomplete—only `snapshot_date >= CURRENT_DATE - 89` is applied, no `<= CURRENT_DATE`, so future-dated snapshots would be included. Also no explicit inclusion of CURRENT_DATE is ok, but in presence of timezones or bad data could matter. Portability: same date-row_number subtraction issue. Efficiency: Slightly simpler (fewer CTEs), fewer window functions; filters early but not bounded above. Still acceptable. Readability: Concise but less explicit about two-level selection; lacks comments and clarity on spec nuances.

Analysis of Claude Opus 4.6 (High Think):

Correctness: Strong. Filters to last 90 days inclusive (>= CURRENT_DATE-89 and <= CURRENT_DATE). Stockout-only rows ensure in-stock days break streaks (by absence in stockout_days). Missing dates break because date - row_number grouping changes across gaps. Computes streak per (wh,sku,grp) with COUNT and MAX(end). Then correctly selects best streak per SKU (rn=1 ordered by length desc, end desc) before selecting best SKU per warehouse with required tie-breaks (length, end, sku_id). Warehouses with no stockouts naturally excluded. Minor portability concern: date - integer syntax and casting ROW_NUMBER() to INT is Postgres-friendly; SQL Server would use DATEADD(day,-row_number, snapshot_date). But prompt allows Postgres/SQL Server style; as written it’s more Postgres. Also assumes snapshot_date has no time component (it’s DATE). Efficiency: Good use of early filter to 90 days and stockouts reduces rows. Window functions partitioned by (warehouse_id, sku_id) leverage PK ordering; index on (snapshot_date) or (warehouse_id, sku_id, snapshot_date) already exists via PK, so range scan by date may not be optimal unless additional index on snapshot_date; but acceptable. Two window passes plus aggregations; reasonable. Readability: Well-structured CTEs with clear comments and separation of concerns; tie-breaking explicit. Overall production-approvable with minor dialect tweak if needed.

Analysis of Gemini 3 Pro Preview (High Think):

Correctness: Partially correct but misses an important edge case: it ranks streaks directly per warehouse without first taking the maximum streak per SKU and then choosing best SKU; however, ranking streaks per warehouse still yields the best streak overall, which often corresponds to some SKU’s max streak. The real problem is tie-breaking when multiple streaks from the same SKU exist: requirement says consider each SKU’s maximum streak, then pick best SKU per warehouse; if a SKU has two streaks and the longer one is older, while a different SKU has same length but newer end date, the per-streak ranking could pick the newer streak of the first SKU (shorter) incorrectly in certain contrived cases? More concretely: because it doesn’t collapse to per-SKU maxima, it might select a non-max streak of a SKU if that non-max streak has a later end date and still beats other SKUs’ max streaks under ordering—violating the spec. Also it omits explicit <= CURRENT_DATE upper bound (though typically unnecessary if no future dates). Like X, date - integer is Postgres-ish; SQL Server would differ. Efficiency: Slightly simpler than X (fewer CTEs/window steps), so potentially faster, but at cost of correctness. Still filters early. Readability: Concise but less self-explanatory; lacks handling of the per-SKU step and comments are minimal. Production readiness is lower due to spec mismatch.

Analysis of Claude Opus 4.6 (High Think):

Entry X demonstrates a thorough, methodical approach to the gaps-and-islands problem. CORRECTNESS: - Properly filters to stockout days (available_qty <= 0) within the 90-day window - Uses the classic gaps-and-islands technique correctly: subtracting ROW_NUMBER from date creates group identifiers where consecutive dates share the same group - Handles the edge case of multiple streaks per SKU by first selecting the best streak per SKU (best_per_sku CTE), then selecting the best SKU per warehouse - Correctly implements all three tie-breaking rules in the final ranking: streak_length_days DESC, streak_end_date DESC, sku_id ASC - Missing snapshot dates naturally break streaks since they won't appear in stockout_days - Warehouses with no stockouts won't produce any rows (correct behavior) - The date arithmetic `snapshot_date - CAST(ROW_NUMBER() ... AS INT)` is PostgreSQL-compatible EFFICIENCY: - Clean separation of concerns across CTEs allows the optimizer to work effectively - The two-level ROW_NUMBER approach (first per SKU, then per warehouse) is slightly more verbose but ensures correctness when a single SKU has multiple streaks - No unnecessary joins or subqueries READABILITY: - Excellent use of descriptive CTE names (stockout_days, streaks, streak_stats, best_per_sku, ranked) - Well-commented with clear explanations of each step - The expert note provides good documentation of the approach - Code is properly formatted and easy to follow

Analysis of Gemini 3 Pro Preview (High Think):

Entry Y takes a more concise approach to the same problem. CORRECTNESS: - Correctly filters to stockout days within the 90-day window - Uses the same gaps-and-islands technique with date minus ROW_NUMBER - Correctly implements tie-breaking rules in the final ranking - Missing dates and non-stockout days naturally break streaks POTENTIAL ISSUE: Entry Y skips the intermediate step of selecting the best streak per SKU before ranking across the warehouse. It goes directly from all streaks to ranking per warehouse. This could be problematic in an edge case: Consider: SKU A has two streaks (5 days, 3 days) and SKU B has one streak (4 days). In Entry Y's approach, all three streaks compete directly. The 5-day streak from SKU A wins, which is correct. However, if SKU A had streaks of (4 days ending Jan 10, 4 days ending Jan 5) and SKU B had (4 days ending Jan 8), the ranking would be: 1. SKU A (4 days, Jan 10) 2. SKU B (4 days, Jan 8) 3. SKU A (4 days, Jan 5) This actually produces the correct result (SKU A wins with its best streak). So the approach is still correct - the best streak per SKU will naturally rank highest among that SKU's streaks, and the final ROW_NUMBER picks the best overall. Actually, upon further reflection, Entry Y's approach IS correct because when ranking all streaks per warehouse, the best streak for each SKU will naturally rank above that SKU's other streaks. The final selection picks the top-ranked streak, which will be the best overall. EFFICIENCY: - More concise with fewer CTEs - Slightly more efficient as it avoids an extra ranking pass - Uses SELECT * which is generally discouraged in production code READABILITY: - Compact but less documented - Uses SELECT * in the ranked CTE (minor style issue) - Less descriptive variable naming - Expert note is brief but adequate

Analysis of Claude Opus 4.6 (High Think):

Entry X demonstrates a thorough, well-structured approach to the gaps-and-islands problem: **CORRECTNESS:** - Correctly filters to the last 90 days using `CURRENT_DATE - 89` (inclusive of today = 90 days) - Properly identifies stockout days where `(on_hand - reserved) <= 0` - The gaps-and-islands technique is correctly implemented: subtracting ROW_NUMBER from snapshot_date creates unique group identifiers for consecutive date sequences - Missing dates naturally break streaks because they won't appear in stockout_days, causing the ROW_NUMBER sequence to skip - Uses a two-level ranking approach: first finds the best streak per SKU (best_per_sku), then finds the best SKU per warehouse (ranked) - Tie-breaking is correctly implemented: streak_length_days DESC, streak_end_date DESC, sku_id ASC - Warehouses with no stockouts correctly produce no rows **EFFICIENCY:** - The query is reasonably efficient with a single pass through the data for filtering - The two-level ROW_NUMBER approach adds some overhead but ensures correctness - Could benefit from an index on (warehouse_id, sku_id, snapshot_date, on_hand, reserved) **READABILITY:** - Excellent structure with clearly named CTEs - Good inline comments explaining each step - The expert's note provides clear explanation of the technique - Easy to follow the logical flow from filtering → grouping → ranking **POTENTIAL ISSUE:** - The date arithmetic `snapshot_date - CAST(ROW_NUMBER() ... AS INT)` syntax may vary slightly between databases, but is generally valid for PostgreSQL-style systems

Analysis of Gemini 3 Pro Preview (High Think):

Entry Y takes a more concise approach to the same problem: **CORRECTNESS:** - Correctly filters to the last 90 days using `CURRENT_DATE - 89` - Properly identifies stockout conditions with `(on_hand - reserved) <= 0` - Uses the same gaps-and-islands technique with date minus ROW_NUMBER - Tie-breaking is correctly implemented: streak_length_days DESC, streak_end_date DESC, sku_id ASC **CRITICAL ISSUE:** - Entry Y skips the intermediate step of finding the best streak per SKU before ranking across the warehouse. It directly ranks ALL streaks per warehouse. This actually works correctly because the final ranking considers all streaks and picks the best one regardless of which SKU it belongs to. The tie-breaking rules will naturally select the best streak. - Wait - actually this is fine. The requirement is to find the longest consecutive stockout streak within each warehouse. Entry Y ranks all streaks directly and picks the best one. This is actually MORE efficient than Entry X's two-level approach because if two different SKUs have streaks of the same length, the tie-breaking rules will correctly choose between them. **EFFICIENCY:** - More efficient than Entry X - fewer CTEs and one less ROW_NUMBER operation - Achieves the same result with less computational overhead **READABILITY:** - More compact but less documented - Uses `SELECT *` in the ranked CTE which is generally discouraged in production code - Lacks inline comments explaining the logic - The expert's note is brief but adequate **MINOR ISSUES:** - Missing upper bound check `snapshot_date <= CURRENT_DATE` (though this is unlikely to matter in practice if future dates don't exist) - Uses `SELECT *` which is a minor style concern