SQL-Frontier-Eval Benchmark Suite

Production evaluation of Frontier AI Models on Analytical SQL, Execution Anti-Patterns, and Plan Bottlenecks tested across 1,000,000+ records per table.

95.8%

Overall Benchmark Pass Rate

Total Benchmark Tasks

120

Across 6 Analytical Domains

Underlying Data Volume

3.04 GiB

108,027,582 Records (148 Tables)

Avg Execution Latency (1M Rows)

232.2 ms

Golden Avg: 217.8 ms

Evaluated Model

agy/gemini-3.8-flash-low

115 Passed • 5 Diverged

🔍

Root Cause Analysis (RCA): Yesterday's Run Abort & Ubuntu Process Termination

Incident Timestamp: 2026-11-09 00:38:46 UTC | Host: Ubuntu 24.04 (Linux 6.8 kernel)

1. Did Ubuntu Terminate the Process?

YES. Ubuntu's systemd-oomd sent SIGKILL to the terminal scope (vte-spawn-e541b72c...scope) at 00:38:46 due to memory pressure reaching 61.9% > 50.0% threshold for > 20s.

2. Memory & Disk Spill Evidence

The terminal consumed a peak of 12.1 GB of RAM and 1.4 GB of swap. DuckDB spilled 546.8 MB into .tmp/duckdb_temp_121594_0_storage... before termination.

3. Root Cause Trigger

During gpt-oss-120b-medium evaluation on cyclic dependency graph tasks, generated SQL produced unbounded recursion. Without explicit DuckDB limits, DuckDB allocated up to its 12.0 GB default ceiling (80% system RAM).

4. Permanent Safeguards Applied

Hardened engine in harness/engine.py with SET max_memory = '2GB'; and an active 15-second statement watchdog timer via duckdb.interrupt(). Runaway queries fail fast without OS memory impact.

Multi-Model Frontier Leaderboard & Failure Summary

Empirical Comparison

Model / Baseline	Pass Rate	Correct / Total	Avg Model Latency	Engine Exec Latency (1M)	Primary Analytical Failure Modes
Claude Opus 4.6 (Thinking)	100.0%	8 / 8	6.9s	4.6 ms	Zero failures across core traps baseline suite
Gemini 3.8 Flash (Low)	95.8%	115 / 120	4.6s	232.2 ms	Full 120-task suite: 5 edge-case value/shape divergences
Gemini 3.8 Flash (Medium)	100.0%	8 / 8	27.6s	4.5 ms	Zero failures across core traps baseline suite
Claude Sonnet 4.6 (Thinking)	87.5%	7 / 8	6.3s	4.8 ms	Strict Funnel Precedence (`CROSS_PRODUCT` scalar join)
GPT-OSS 120B (Medium)	87.5%	7 / 8	6.5s	5.5 ms	Strict Funnel Precedence & Recursive Cycle Memory Explosion
Gemini 3.8 Flash (High)	75.0%	6 / 8	51.9s	4.3 ms	Reasoning timeouts on microsecond ASOF & Funnel precedence
Golden Ground Truth (Full 120 Suite)	100.0%	120 / 120	N/A	217.8 ms	Ground Truth Baseline (0% False Negatives on 1M+ Records)

Analytical Domains Evaluated (1M+ Records per Table)

Click any domain to filter queries

Temporal & Time-Series 92.9%

26/28 Passed

205.5ms Avg Latency (1M)

Vector & Embeddings 100.0%

25/25 Passed

379.9ms Avg Latency (1M)

Warehouse & Statistical 93.8%

15/16 Passed

242.2ms Avg Latency (1M)

Metric Inflation / Chasm Trap 100.0%

1/1 Passed

88.5ms Avg Latency (1M)

Sessionization (Gaps & Islands) 100.0%

2/2 Passed

229.8ms Avg Latency (1M)

SCD Type 2 Point-in-Time Joins 100.0%

1/1 Passed

72.1ms Avg Latency (1M)

Three-Valued Logic in Anti-Joins 100.0%

1/1 Passed

44.7ms Avg Latency (1M)

Search & Retrieval 100.0%

26/26 Passed

276.4ms Avg Latency (1M)

Window Frame Default Bounds 100.0%

1/1 Passed

157.0ms Avg Latency (1M)

Strict Funnel Precedence 100.0%

1/1 Passed

43.9ms Avg Latency (1M)

Graph & Hierarchy 85.7%

12/14 Passed

0.9ms Avg Latency (1M)

Quadratic Self-Joins vs Range Windows 100.0%

1/1 Passed

2.8ms Avg Latency (1M)

Non-SARGable Partition Pruning 100.0%

1/1 Passed

0.5ms Avg Latency (1M)

Algorithmic Efficiency / Window Distinct 100.0%

1/1 Passed

658.7ms Avg Latency (1M)

Vector & Retrieval 100.0%

1/1 Passed

12.6ms Avg Latency (1M)

Benchmark Query Deep-Dive Explorer

120 Detailed Queries Available

Status	Task ID	Query Name & Purpose	Domain	Latency (1M Rows)	Underlying Volume	Action
✔ PASS	`task_001_Q01_asof_trade_quote_matching`	Tick-Level Trade Execution Matching via Native ASOF JOIN In algorithmic trading, every buy or sell order must be evaluated against the most recent ...	Temporal & Time-Series	452.4 ms / 450.0 ms	2,000,000 rows (83.9 MiB)	Deep Dive →
✔ PASS	`task_002_Q02_dynamic_point_in_time_fx`	Multi-Currency Point-in-Time FX Conversion via ASOF JOIN Global payments systems record transactions in local currencies (EUR, JPY, GBP) that must ...	Temporal & Time-Series	79.3 ms / 78.4 ms	1,000,003 rows (38.1 MiB)	Deep Dive →
✔ PASS	`task_003_Q03_clickstream_sessionization_gaps`	Clickstream Sessionization with Inactivity Timeouts (Gaps & Islands) Digital analytics platforms group raw user pageviews into 'sessions'. If a user is inactiv...	Temporal & Time-Series	304.9 ms / 303.9 ms	1,000,000 rows (30.5 MiB)	Deep Dive →
✔ PASS	`task_004_Q04_dual_bound_session_capping`	Sliding Sessionization with Absolute Maximum Duration Cap Real-world web sessions often need dual constraints: a 30-minute inactivity timeout AND a ...	Temporal & Time-Series	408.1 ms / 399.7 ms	1,000,000 rows (30.5 MiB)	Deep Dive →
✔ PASS	`task_005_Q05_iot_sensor_gap_fill_locf`	IoT Sensor Telemetry Gap-Filling via generate_series and LOCF Industrial IoT sensors emit readings intermittently to save bandwidth. Downstream machine ...	Temporal & Time-Series	0.6 ms / 0.4 ms	1,000,000 rows (19.1 MiB)	Deep Dive →
✖ VALUE_MISMATCH	`task_006_Q06_bitemporal_state_reconstruction`	Bitemporal Auditing: Valid-Time vs. Transaction-Time Alignment Financial compliance requires knowing: 'What did we believe customer Alice's credit limit ...	Temporal & Time-Series	0.0 ms / 0.0 ms	1,000,000 rows (42.0 MiB)	Deep Dive →
✔ PASS	`task_007_Q07_scd2_open_ended_intervals`	Slowly Changing Dimension Type 2 Join with Open-Ended Future Customers change addresses or account tiers over time. Joining historical orders to custom...	Temporal & Time-Series	0.4 ms / 0.4 ms	1,000,002 rows (26.7 MiB)	Deep Dive →
✔ PASS	`task_008_Q08_hopping_sliding_time_buckets`	Hopping / Overlapping Window Aggregations via time_bucket Real-time surveillance monitors rolling 15-minute average latency stepped forward every 5 ...	Temporal & Time-Series	0.6 ms / 0.6 ms	1,000,000 rows (34.3 MiB)	Deep Dive →
✔ PASS	`task_009_Q09_ledger_balance_duplicate_timestamps`	Financial Ledger Cumulative Balance with Duplicate Timestamp Ties Ledger entries can occur at the exact same millisecond. Financial ledgers must preserve st...	Temporal & Time-Series	163.3 ms / 146.8 ms	1,000,000 rows (30.5 MiB)	Deep Dive →
✔ PASS	`task_010_Q10_peer_exclusion_moving_average`	Window Frame Exclusion: Peer Period Average via EXCLUDE CURRENT ROW To detect anomalous spikes, analysts compare a metric against the average of neighboring d...	Temporal & Time-Series	307.2 ms / 309.6 ms	1,000,000 rows (26.7 MiB)	Deep Dive →
✔ PASS	`task_011_Q11_exclude_ties_ranking`	Excluding Ties in Percentile Ranks via EXCLUDE TIES When evaluating employee sales performance, ties should not inflate the percentile score a...	Temporal & Time-Series	124.9 ms / 124.2 ms	1,000,000 rows (19.1 MiB)	Deep Dive →
✔ PASS	`task_012_Q12_microsecond_latency_jitter`	Inter-Packet Network Arrival Jitter at Nanosecond Resolution High-speed network telemetry measures packet arrival jitter (variance in inter-packet dela...	Temporal & Time-Series	152.4 ms / 145.3 ms	1,000,000 rows (11.4 MiB)	Deep Dive →
✔ PASS	`task_013_Q13_order_book_snapshot_reconstruction`	Point-in-Time Limit Order Book (LOB) State Reconstruction Market microstructure researchers need to reconstruct the exact bids and asks available at...	Temporal & Time-Series	18.9 ms / 17.9 ms	1,000,000 rows (64.8 MiB)	Deep Dive →
✔ PASS	`task_014_Q14_time_weighted_average_price_twap`	Time-Weighted Average Price (TWAP) with Variable Interval Weights TWAP measures the average price of an asset weighted by the duration of time that each pri...	Temporal & Time-Series	92.0 ms / 83.5 ms	1,000,000 rows (34.3 MiB)	Deep Dive →
✔ PASS	`task_015_Q15_volume_weighted_average_price_vwap`	Intraday Volume-Weighted Average Price (VWAP) Resetting Daily VWAP provides institutional traders the definitive benchmark price weighted by traded volu...	Temporal & Time-Series	448.4 ms / 434.5 ms	1,000,000 rows (38.1 MiB)	Deep Dive →
✔ PASS	`task_016_Q16_sequential_funnel_precedence`	Chronological Multi-Stage Conversion Funnel with Time Boundaries In product funnels (Signup -> Verify -> Purchase), events must occur in exact chronologica...	Temporal & Time-Series	36.0 ms / 31.1 ms	1,000,000 rows (30.5 MiB)	Deep Dive →
✔ PASS	`task_017_Q17_retention_cohort_survival`	Dynamic User Retention Cohort Matrix by Weekly Milestones SaaS analytics track weekly retention curves (Day 0, Day 7, Day 14, Day 30) based on user ...	Temporal & Time-Series	139.8 ms / 148.6 ms	2,000,000 rows (15.3 MiB)	Deep Dive →
✔ PASS	`task_018_Q18_time_decay_user_affinity`	Exponential Time-Decayed User Affinity Scoring Recommendation systems score user interest where recent clicks count significantly more th...	Temporal & Time-Series	209.3 ms / 216.0 ms	1,000,000 rows (30.5 MiB)	Deep Dive →
✔ PASS	`task_019_Q19_intermittent_inactivity_anomaly`	Customer Churn Anomaly Detection vs Historical Purchase Rhythm A customer who normally buys every 3 days is at risk if they haven't bought in 10 days. Bu...	Temporal & Time-Series	323.2 ms / 314.7 ms	1,000,000 rows (7.6 MiB)	Deep Dive →
✔ PASS	`task_01_fanout`	E-Commerce Financial Aggregation without Join Fan-Out Demonstrates the Chasm Trap where joining multiple 1-to-many child tables (discounts and s...	Metric Inflation / Chasm Trap	88.5 ms / 6.1 ms	1,003,000 rows (23.0 MiB)	Deep Dive →
✔ PASS	`task_020_Q20_interval_overlap_concurrency`	Peak Concurrent Workforce Capacity via Interval Overlap Scan Workforce managers must calculate maximum concurrent staffing levels from employee shift s...	Temporal & Time-Series	157.3 ms / 113.5 ms	1,000,000 rows (22.9 MiB)	Deep Dive →
✔ PASS	`task_021_Q21_temporal_gap_detection`	Continuous Asset Lease Unallocated Gap Discovery Fleet managers track commercial vehicles to find idle 'unrented' gaps between back-to-back...	Temporal & Time-Series	46.3 ms / 41.0 ms	1,000,000 rows (11.4 MiB)	Deep Dive →
✔ PASS	`task_022_Q22_linear_interpolation_missing_points`	Bi-directional Linear Value Interpolation between Observations When a sensor drops offline for 30 minutes, data scientists prefer linear interpolation be...	Temporal & Time-Series	161.7 ms / 124.2 ms	1,000,000 rows (15.3 MiB)	Deep Dive →
✖ VALUE_MISMATCH	`task_023_Q23_trimmed_mean_rolling_window`	Outlier-Resistant Rolling Window (Trimmed Mean Aggregation) Financial risk models compute a 7-day rolling average excluding the highest and lowest sin...	Temporal & Time-Series	1204.0 ms / 173.5 ms	1,000,000 rows (11.4 MiB)	Deep Dive →
✔ PASS	`task_024_Q24_sargable_fiscal_partition_prune`	SARGable Fiscal Year Date Range Partition Pruning Data warehouses partition petabyte event tables by date. Wrapping date columns in function...	Temporal & Time-Series	192.7 ms / 181.1 ms	1,000,000 rows (30.5 MiB)	Deep Dive →
✔ PASS	`task_025_Q25_multi_touch_marketing_attribution`	Multi-Touch Marketing Attribution Comparison in a Single Pass CMOs need to compare 3 attribution models (First-Touch, Last-Touch, and Uniform Linear) fo...	Temporal & Time-Series	147.4 ms / 157.7 ms	2,000,000 rows (57.2 MiB)	Deep Dive →
✔ PASS	`task_026_Q26_cosine_similarity_top_k`	Dense Vector Cosine Similarity Nearest Neighbors (Top-K ANN) When a user asks a question in an AI assistant, the query is embedded into a dense vector ...	Vector & Embeddings	9.4 ms / 9.2 ms	1,000,000 rows (38.1 MiB)	Deep Dive →
✔ PASS	`task_027_Q27_euclidean_l2_nearest_neighbors`	L2 Euclidean Distance Nearest Neighbors Search via array_distance In computer vision feature retrieval (facial recognition, product image matching), feature...	Vector & Embeddings	14.9 ms / 19.1 ms	1,000,000 rows (34.3 MiB)	Deep Dive →
✔ PASS	`task_028_Q28_inner_product_mips`	Maximum Inner Product Search (MIPS) for Normalized Embeddings For unit-normalized vectors (e.g. OpenAI text-embedding-3 or Cohere embeddings), cosine si...	Vector & Embeddings	15.6 ms / 18.8 ms	1,000,000 rows (34.3 MiB)	Deep Dive →
✔ PASS	`task_029_Q29_pre_filter_vs_post_filter_trap`	The Pre-Filtering Trap: Relational Predicates with Vector Top-K A user searches for 'cheap mechanical keyboard under $50'. If you run vector search Top-K ...	Vector & Embeddings	8.0 ms / 8.1 ms	1,000,000 rows (50.5 MiB)	Deep Dive →
✔ PASS	`task_02_sessionization`	Clickstream Sessionization with Inactivity Gaps Groups web analytics events into browsing sessions where an inactivity gap greater than 30...	Sessionization (Gaps & Islands)	265.0 ms / 248.3 ms	1,000,000 rows (34.3 MiB)	Deep Dive →
✔ PASS	`task_030_Q30_adaptive_similarity_threshold`	Adaptive Similarity Thresholding with Fallback Top-K Guarantee RAG systems want high-confidence matches (similarity > 0.85) to avoid hallucinations, but ...	Vector & Embeddings	504.2 ms / 435.5 ms	1,000,000 rows (34.3 MiB)	Deep Dive →
✔ PASS	`task_031_Q31_semantic_deduplication_support_tickets`	Semantic Deduplication of IT Incident Tickets via Vector Cosine Threshold During service outages, hundreds of users submit identically phrased IT support tickets ('...	Vector & Embeddings	2009.9 ms / 2118.6 ms	2,500 rows (87.9 KiB)	Deep Dive →
✔ PASS	`task_032_Q32_near_duplicate_product_catalog`	Multi-Merchant Near-Duplicate Catalog Resolution with Price Consistency Marketplaces aggregate catalogs from thousands of sellers. Matching identical items requir...	Vector & Embeddings	495.9 ms / 477.4 ms	2,500 rows (175.8 KiB)	Deep Dive →
✔ PASS	`task_033_Q33_colbert_late_interaction_maxsim`	ColBERT Multi-Vector Late Interaction (MaxSim) in Pure SQL Advanced retrieval models (ColBERT / Sanity) represent queries and documents as collection...	Vector & Embeddings	211.5 ms / 186.7 ms	1,000,002 rows (34.3 MiB)	Deep Dive →
✔ PASS	`task_034_Q34_user_profile_embedding_centroid`	User Profile Preference Vector Centroid Aggregation To personalize feeds, recommendation engines compute a dynamic user preference vector by c...	Vector & Embeddings	937.0 ms / 955.9 ms	1,000,000 rows (19.1 MiB)	Deep Dive →
✔ PASS	`task_035_Q35_kmeans_cluster_assignment_step`	K-Means Clustering Assignment and Centroid Update in Pure SQL Unsupervised customer segmentation can be run directly inside DuckDB by assigning customer...	Vector & Embeddings	299.1 ms / 247.7 ms	1,000,002 rows (11.4 MiB)	Deep Dive →
✔ PASS	`task_036_Q36_knn_vector_anomaly_detection`	Vector Anomaly Detection via Average K-Nearest Neighbor Distance Anomalous sensor readings or fraudulent transaction vectors reside in sparse regions of th...	Vector & Embeddings	802.8 ms / 821.7 ms	2,500 rows (29.3 KiB)	Deep Dive →
✔ PASS	`task_037_Q37_hybrid_relational_vector_cohort_join`	Cohort-Constrained Vector Match with Geographical Regionality Real-world matchmaking (dating apps, gig-economy driver matching) requires vector personal...	Vector & Embeddings	98.8 ms / 91.7 ms	1,000,001 rows (34.3 MiB)	Deep Dive →
✔ PASS	`task_038_Q38_multimodal_image_text_fusion`	Multi-Modal Joint Retrieval (Image + Text Query Embedding Fusion) In e-commerce search, users submit a photo and a text clarification (e.g. photo of dress +...	Vector & Embeddings	240.9 ms / 231.7 ms	1,000,000 rows (38.1 MiB)	Deep Dive →
✔ PASS	`task_039_Q39_maximal_marginal_relevance_mmr`	Maximal Marginal Relevance (MMR) Diversity Reranking in SQL Standard vector retrieval often returns 5 virtually identical articles. MMR penalizes redu...	Vector & Embeddings	297.3 ms / 299.1 ms	1,000,000 rows (30.5 MiB)	Deep Dive →
✔ PASS	`task_03_scd2_join`	Historical SCD Type 2 Point-in-Time Currency Conversion Joins financial transactions to Slowly Changing Dimension (SCD Type 2) exchange rate table...	SCD Type 2 Point-in-Time Joins	72.1 ms / 68.8 ms	1,000,003 rows (45.8 MiB)	Deep Dive →
✔ PASS	`task_040_Q40_cosine_matrix_cluster_pruning`	Pairwise Vector Similarity Self-Join with Coarse Cluster Pruning Finding all similar document pairs across 100,000 docs is O(N^2) (10 billion checks). Part...	Vector & Embeddings	0.7 ms / 0.6 ms	2,500 rows (39.1 KiB)	Deep Dive →
✔ PASS	`task_041_Q41_collaborative_filtering_latent_dot`	Latent Factor Matrix Factorization Dot Product Recommendation Matrix factorization (SVD / ALS) maps users and movies to latent factor vectors. Predicted...	Vector & Embeddings	462.1 ms / 395.8 ms	2,000,000 rows (64.8 MiB)	Deep Dive →
✔ PASS	`task_042_Q42_negative_prompting_vector_repulsion`	Vector Repulsion Scoring (Positive vs Negative Query Guidance) In image generation and semantic search, users supply positive and negative prompts (e.g. ...	Vector & Embeddings	262.4 ms / 256.4 ms	1,000,000 rows (30.5 MiB)	Deep Dive →
✔ PASS	`task_043_Q43_best_chunk_pooling_retrieval`	Document-Level Aggregation with Best-Chunk Pooling in RAG Long PDF manuals are split into 50 chunks each. To avoid returning 5 chunks from the exact...	Vector & Embeddings	459.2 ms / 464.2 ms	1,000,000 rows (53.4 MiB)	Deep Dive →
✔ PASS	`task_044_Q44_token_budget_context_truncation`	Dynamic Context Window Token Budget Packing for LLM RAG When assembling retrieved chunks for an LLM prompt, you have a strict token limit (e.g. 1,...	Vector & Embeddings	98.3 ms / 111.7 ms	1,000,000 rows (15.3 MiB)	Deep Dive →
✔ PASS	`task_045_Q45_cross_encoder_candidate_generation`	Two-Stage Retrieval: Bi-Encoder Candidate Generation for Re-ranking Modern enterprise search uses a 2-stage pipeline: Fast bi-encoder vector search retrieves ...	Vector & Embeddings	21.8 ms / 15.4 ms	1,000,000 rows (30.5 MiB)	Deep Dive →
✔ PASS	`task_046_Q46_temporal_embedding_drift`	Temporal Semantic Drift Tracking across Document Embedding Centroids As companies evolve, the meaning of terms like 'security' or 'remote work' shifts. Trackin...	Vector & Embeddings	1152.0 ms / 1126.3 ms	1,000,000 rows (11.4 MiB)	Deep Dive →
✔ PASS	`task_047_Q47_hierarchical_vector_coarse_fine`	Hierarchical Coarse-to-Fine Multi-Level Vector Filtering In massive enterprise catalogs, searching 100 million items is accelerated by first matchi...	Vector & Embeddings	0.4 ms / 0.4 ms	1,000,002 rows (34.3 MiB)	Deep Dive →
✔ PASS	`task_048_Q48_vector_quantization_distortion`	Vector Quantization Precision Distortion & Reconstruction Error To compress embeddings from 32-bit floats to 8-bit integers, engineers evaluate the recons...	Vector & Embeddings	60.2 ms / 51.9 ms	1,000,000 rows (26.7 MiB)	Deep Dive →
✔ PASS	`task_049_Q49_prototype_intent_classification`	Zero-Shot Query Intent Classification via Prototype Vector Distance Instead of training a BERT classifier, user query vectors are compared against pre-compute...	Vector & Embeddings	834.0 ms / 834.2 ms	1,000,002 rows (30.5 MiB)	Deep Dive →
✔ PASS	`task_04_three_valued_logic`	Customer Anti-Join with Three-Valued Null Semantics Exposes silent data loss caused by SQL three-valued logic when models use NOT IN over subq...	Three-Valued Logic in Anti-Joins	44.7 ms / 48.5 ms	3,000,000 rows (45.8 MiB)	Deep Dive →
✔ PASS	`task_050_Q50_contrastive_triplet_loss_evaluation`	Contrastive Triplet Evaluation: Anchor-Positive-Negative Separation In embedding model evaluation, high-quality models satisfy the triplet condition: distance...	Vector & Embeddings	201.6 ms / 198.8 ms	1,000,000 rows (26.7 MiB)	Deep Dive →
✔ PASS	`task_051_Q51_fts_bm25_lexical_search`	DuckDB Full-Text Search (FTS) BM25 Term Relevance Scoring Traditional databases use simple `LIKE '%search%'` which treats 1 mention the same as 10 m...	Search & Retrieval	54.7 ms / 55.8 ms	1,000,000 rows (42.0 MiB)	Deep Dive →
✔ PASS	`task_052_Q52_reciprocal_rank_fusion_rrf`	Reciprocal Rank Fusion (RRF) Hybrid Search (BM25 + Dense Vectors) Hybrid search combines sparse keyword search (great for exact names and acronyms) with den...	Search & Retrieval	515.6 ms / 508.5 ms	1,000,000 rows (38.1 MiB)	Deep Dive →
✔ PASS	`task_053_Q53_min_max_convex_hybrid_search`	Min-Max Normalized Convex Combination Hybrid Search An alternative to rank-based RRF is score-based fusion: min-max scale lexical scores to [0...	Search & Retrieval	338.8 ms / 329.9 ms	1,000,000 rows (38.1 MiB)	Deep Dive →
✔ PASS	`task_054_Q54_fuzzy_levenshtein_length_pruning`	Typo-Tolerant Search with Levenshtein Edit Distance & Length Bounds Users frequently misspell search terms (e.g. 'postgre' -> 'postgresql'). Levenshtein dista...	Search & Retrieval	134.6 ms / 133.8 ms	1,000,000 rows (22.9 MiB)	Deep Dive →
✔ PASS	`task_055_Q55_jaro_winkler_brand_search`	Brand & Name Fuzzy Search via Jaro-Winkler Prefix Similarity In e-commerce, users often mistype the suffixes of brand names ('Samsumg' vs 'Samsung'). J...	Search & Retrieval	0.7 ms / 0.5 ms	2,500 rows (58.6 KiB)	Deep Dive →
✔ PASS	`task_056_Q56_multi_field_weighted_scoring`	Multi-Field Weighted Full-Text Retrieval (Title 3x, Body 1x) A match in a document title or product headline is far more indicative of relevance than a...	Search & Retrieval	197.3 ms / 200.4 ms	1,000,000 rows (42.0 MiB)	Deep Dive →
✔ PASS	`task_057_Q57_exact_phrase_proximity_boost`	Exact Phrase Proximity Boosting in Keyword Retrieval If a user searches for 'machine learning', an article containing the exact phrase 'machine...	Search & Retrieval	195.9 ms / 186.1 ms	1,000,000 rows (22.9 MiB)	Deep Dive →
✔ PASS	`task_058_Q58_stopword_cleansing_normalization`	Stopword Filtering and Query Token Cleansing in SQL Search queries like 'what is the best laptop' contain stop words ('what', 'is', 'the') tha...	Search & Retrieval	11.0 ms / 14.3 ms	1,000,000 rows (19.1 MiB)	Deep Dive →
✔ PASS	`task_059_Q59_faceted_search_json_attributes`	Faceted Search Aggregations over Semi-Structured JSON Attributes When a customer searches for 'laptops', e-commerce search engines return matching items al...	Search & Retrieval	15.0 ms / 12.7 ms	1,000,000 rows (38.1 MiB)	Deep Dive →
✔ PASS	`task_05_window_frame_bounds`	Financial Ledger Running Balance with Duplicate Timestamps Tests window frame boundary specification (ROWS vs default RANGE) on duplicate timestamp o...	Window Frame Default Bounds	157.0 ms / 137.6 ms	1,000,000 rows (30.5 MiB)	Deep Dive →
✔ PASS	`task_060_Q60_ngram_log_signature_search`	Trigram / N-Gram Token Substring Matching for Technical Error Logs In observability platforms (Datadog, Splunk), engineers search stack traces with partial e...	Search & Retrieval	252.7 ms / 233.8 ms	1,000,000 rows (22.9 MiB)	Deep Dive →
✔ PASS	`task_061_Q61_synonym_expansion_reranking`	Dynamic Query Synonym Expansion via Relational Thesaurus When a customer searches for 'sofa', they also expect to see listings titled 'couch' or 's...	Search & Retrieval	211.8 ms / 229.0 ms	1,000,003 rows (22.9 MiB)	Deep Dive →
✔ PASS	`task_062_Q62_autocomplete_prefix_popularity`	Search Autocomplete Suggestion with Popularity Frequency Weighting As users type 'air...', instant search suggests 'air fryer' (100k searches) before 'air ma...	Search & Retrieval	9.0 ms / 8.1 ms	1,000,000 rows (38.1 MiB)	Deep Dive →
✔ PASS	`task_063_Q63_boolean_search_negation`	High-Precision Boolean Search with Negation and Grouping Legal e-discovery and patent search rely on exact Boolean syntax: (patent AND battery) NOT...	Search & Retrieval	85.9 ms / 93.8 ms	1,000,000 rows (22.9 MiB)	Deep Dive →
✔ PASS	`task_064_Q64_pure_sql_tfidf_calculation`	TF-IDF (Term Frequency - Inverse Document Frequency) in Pure SQL TF-IDF is the foundational algorithm of information retrieval. Calculating it directly in ...	Search & Retrieval	173.6 ms / 195.9 ms	1,000,000 rows (26.7 MiB)	Deep Dive →
✔ PASS	`task_065_Q65_search_position_bias_dcg`	Discounted Cumulative Gain (DCG) with Search Position Bias Discount Users are far more likely to click the #1 search result than the #5 result regardless of r...	Search & Retrieval	43.1 ms / 43.5 ms	1,000,000 rows (11.4 MiB)	Deep Dive →
✔ PASS	`task_066_Q66_alphanumeric_sku_normalization`	Alphanumeric SKU Matching with Punctuation & Case Invariance Customers search for model numbers in messy formats: 'wh-1000xm5', 'WH1000-XM5', 'wh 1000 ...	Search & Retrieval	322.0 ms / 325.5 ms	1,000,000 rows (42.0 MiB)	Deep Dive →
✔ PASS	`task_067_Q67_search_negation_term_suppression`	Dynamic Negative Keyword Exclusion in E-Commerce Filters A user searches 'monitor -curved' to find flat computer monitors. The engine must extract ...	Search & Retrieval	172.5 ms / 130.6 ms	1,000,000 rows (42.0 MiB)	Deep Dive →
✔ PASS	`task_068_Q68_multilingual_search_routing`	Multi-Lingual Catalog Routing by Detected ISO Language Code Global portals store titles in multiple localized columns (title_en, title_es, title_fr). ...	Search & Retrieval	118.2 ms / 121.2 ms	1,000,000 rows (61.0 MiB)	Deep Dive →
✔ PASS	`task_069_Q69_query_relaxation_fallback`	Zero-Result Search Query Relaxation (Strict AND -> Loose OR Fallback) If a multi-word search query (e.g. 'ergonomic mechanical wireless split keyboard') returns...	Search & Retrieval	262.5 ms / 245.2 ms	1,000,000 rows (22.9 MiB)	Deep Dive →
✔ PASS	`task_06_funnel_precedence`	Sequential Multi-Stage User Onboarding Conversion Funnel Calculates user conversion through a multi-step sequence where events must strictly obey c...	Strict Funnel Precedence	43.9 ms / 71.4 ms	1,000,000 rows (34.3 MiB)	Deep Dive →
✔ PASS	`task_070_Q70_search_snippet_keyword_extraction`	Search Snippet Extraction: Context Window Around Matching Terms Search results show a preview snippet containing the searched keyword with 30 characters o...	Search & Retrieval	152.3 ms / 158.2 ms	1,000,000 rows (22.9 MiB)	Deep Dive →
✔ PASS	`task_071_Q71_search_journey_order_attribution`	Customer Search-to-Purchase Path Attribution (Session Query Lift) E-commerce search product managers need to know which search queries drove actual revenue....	Search & Retrieval	247.9 ms / 227.3 ms	2,000,000 rows (61.0 MiB)	Deep Dive →
✔ PASS	`task_072_Q72_fuzzy_entity_resolution_dedup`	Fuzzy Customer Entity Resolution across Disparate Data Sources Merging CRM and billing databases requires linking customers with slight name and address ...	Search & Retrieval	2891.3 ms / 2907.3 ms	5,000 rows (214.8 KiB)	Deep Dive →
✔ PASS	`task_073_Q73_search_term_cooccurrence_matrix`	Search Term Co-Occurrence Matrix (Market Basket of Search Queries) To suggest related searches, engines analyze queries searched together in the same session...	Search & Retrieval	0.9 ms / 0.9 ms	2,500 rows (58.6 KiB)	Deep Dive →
✔ PASS	`task_074_Q74_did_you_mean_spell_suggestion`	'Did You Mean?' Query Spell-Correction Suggestion Engine When a query produces few results, search engines recommend a spell-corrected candidate wo...	Search & Retrieval	98.1 ms / 82.6 ms	1,000,000 rows (22.9 MiB)	Deep Dive →
✔ PASS	`task_075_Q75_hybrid_search_metadata_soft_boost`	Hybrid Search with Hard Categorical Filter & Soft Merchant Quality Boost Top marketplaces enforce strict hard filters (in-stock, prime shipping) while soft-boostin...	Search & Retrieval	135.8 ms / 139.1 ms	1,000,000 rows (50.5 MiB)	Deep Dive →
✔ PASS	`task_076_Q76_bill_of_materials_recursive_rollup`	Hierarchical Bill of Materials (BOM) Cost Rollup via Recursive CTE Manufacturing an electric vehicle requires thousands of nested parts (Car -> Battery Pack ...	Graph & Hierarchy	0.8 ms / 0.8 ms	4 rows (240 B)	Deep Dive →
✔ PASS	`task_077_Q77_org_hierarchy_span_of_control`	Organizational Reporting Depth & Management Span of Control HR analytics measures how deep the management chain is from the CEO to individual contribu...	Graph & Hierarchy	0.9 ms / 0.9 ms	5 rows (140 B)	Deep Dive →
✔ PASS	`task_078_Q78_transitive_fraud_sybil_ring`	Transitive Fraud Ring & Sybil Account Detection via Device Links Financial fraudsters create hundreds of fake accounts linked by shared credit cards, devic...	Graph & Hierarchy	1.8 ms / 1.5 ms	4 rows (32 B)	Deep Dive →
✔ PASS	`task_079_Q79_graph_cycle_detection_visited_array`	Cycle Detection in Directed Dependency Graphs with Visited Array In build systems (e.g. Webpack, Bazel) or task scheduling, circular dependencies (Task A -...	Graph & Hierarchy	0.7 ms / 0.6 ms	3 rows (120 B)	Deep Dive →
✔ PASS	`task_07_quadratic_self_join`	Rolling 7-Day Window Aggregation without Quadratic Self-Joins Tests whether models leverage native SQL range window frames over non-equi self-joins that...	Quadratic Self-Joins vs Range Windows	2.8 ms / 2.8 ms	2,500 rows (48.8 KiB)	Deep Dive →
✔ PASS	`task_080_Q80_logistics_shortest_path_routing`	Lowest-Cost Routing across Logistics Hubs via Recursive Dijkstra Supply chain networks ship containers through intermediary freight hubs. Finding the cheap...	Graph & Hierarchy	0.8 ms / 1.0 ms	4 rows (192 B)	Deep Dive →
✔ PASS	`task_081_Q81_incident_root_cause_dependency_chain`	Causal Microservice Incident Root-Cause Dependency Chain When a user checkout fails, it could be caused by payments, auth, database, or network. Tr...	Graph & Hierarchy	0.8 ms / 0.9 ms	7 rows (312 B)	Deep Dive →
✔ PASS	`task_082_Q82_connected_components_community_labeling`	Graph Connected Components Labeling via Min-ID Propagation Graph connected components partition isolated clusters of customers or machines. Propagati...	Graph & Hierarchy	2.0 ms / 1.6 ms	3 rows (24 B)	Deep Dive →
✔ PASS	`task_083_Q83_degree_centrality_pagerank_step`	Network Node In-Degree Centrality and Influence Ranking Social network influence and API hub criticality are determined by node in-degree centrali...	Graph & Hierarchy	0.3 ms / 0.2 ms	7 rows (56 B)	Deep Dive →
✔ PASS	`task_084_Q84_critical_path_method_scheduling`	Task Scheduling Critical Path Method (CPM) Duration Analysis Project managers identify the 'Critical Path'—the longest sequence of dependent tasks that...	Graph & Hierarchy	0.9 ms / 0.7 ms	5 rows (220 B)	Deep Dive →
✔ PASS	`task_085_Q85_multilevel_referral_commissions`	Multi-Level Affiliate Referral Tree Commission Rollup (Tier 1-3) Affiliate programs reward referrals up to 3 tiers deep (Direct referrer gets 10%, Tier 2 g...	Graph & Hierarchy	1.0 ms / 0.9 ms	4 rows (48 B)	Deep Dive →
✖ VALUE_MISMATCH	`task_086_Q86_multiparent_allocation_cost_rollup`	Multi-Parent Shared Infrastructure Cost Allocation Rollup Cloud infrastructure (e.g. shared Kubernetes cluster) is co-owned by multiple products wit...	Graph & Hierarchy	0.2 ms / 0.2 ms	3 rows (132 B)	Deep Dive →
✖ VALUE_MISMATCH	`task_087_Q87_circular_fx_arbitrage_triangle`	Triangular FX Currency Arbitrage Detection (Loop Profitability) High-frequency FX trading bots scan for triangular arbitrage where converting USD -> EUR -...	Graph & Hierarchy	0.3 ms / 0.3 ms	3 rows (144 B)	Deep Dive →
✔ PASS	`task_088_Q88_state_machine_transition_verification`	Transactional State Machine Transition Sequence Verification Financial compliance requires payment transactions to strictly follow the state sequence: ...	Graph & Hierarchy	0.7 ms / 0.5 ms	5 rows (140 B)	Deep Dive →
✔ PASS	`task_089_Q89_chasm_trap_fanout_resolution`	Chasm Trap Resolution: Multi-Table 1:N Aggregation without Fan-Out Inflation When an order has multiple discounts AND multiple shipment records, joining both child tab...	Warehouse & Statistical	127.4 ms / 133.5 ms	1,002,000 rows (22.9 MiB)	Deep Dive →
✔ PASS	`task_08_sargable_pruning`	SARGable Temporal Partition Pruning and Exact Boundary Range Tests SARGable timestamp filtering and strict month boundary conditions, preventing full-s...	Non-SARGable Partition Pruning	0.5 ms / 0.4 ms	1,000,000 rows (53.4 MiB)	Deep Dive →
✔ PASS	`task_090_Q90_three_valued_logic_safe_anti_join`	Three-Valued Logic Safe Anti-Join (Eliminating the NOT IN NULL Trap) Marketing queries to find active customers who have not churned often use `WHERE id NOT IN...	Warehouse & Statistical	52.5 ms / 49.7 ms	2,000,000 rows (26.7 MiB)	Deep Dive →
✔ PASS	`task_091_Q91_dynamic_pivot_monthly_revenue`	Dynamic PIVOT of Monthly Revenue by Product Category Financial reporting requires transforming normalized long-form transaction rows into cross...	Warehouse & Statistical	19.7 ms / 27.1 ms	1,000,000 rows (53.4 MiB)	Deep Dive →
✔ PASS	`task_092_Q92_dynamic_unpivot_wide_metrics`	Dynamic UNPIVOT of Wide Tabular Sensor / Survey Metrics Legacy data or spreadsheet exports often arrive with metric names as column headers (cpu, ...	Warehouse & Statistical	785.0 ms / 739.9 ms	1,000,000 rows (45.8 MiB)	Deep Dive →
✔ PASS	`task_093_Q93_list_lambdas_nested_json_arrays`	List Lambdas for High-Performance Nested Array Transformations Modern analytical warehouses store nested array payloads (e.g. array of discount percentag...	Warehouse & Statistical	1554.3 ms / 1607.7 ms	1,000,000 rows (15.3 MiB)	Deep Dive →
✔ PASS	`task_094_Q94_median_absolute_deviation_mad`	Median Absolute Deviation (MAD) Robust Statistical Outlier Detection Traditional standard deviation Z-scores fail in the presence of extreme outliers because t...	Warehouse & Statistical	241.7 ms / 239.8 ms	1,000,000 rows (11.4 MiB)	Deep Dive →
✔ PASS	`task_095_Q95_robust_z_score_iqr_normalization`	Robust Z-Score Normalization using Median and Interquartile Range (IQR) Standardizing skewed metrics (like API response latency) for machine learning feature pipe...	Warehouse & Statistical	129.8 ms / 132.2 ms	1,000,000 rows (11.4 MiB)	Deep Dive →
✔ PASS	`task_096_Q96_window_frame_range_vs_rows_ties`	Financial Ledger Running Total: ROWS vs. RANGE Semantic Contrast In financial auditing, understanding the exact behavioral difference between `ROWS BETWEEN...	Warehouse & Statistical	178.3 ms / 164.9 ms	1,000,000 rows (22.9 MiB)	Deep Dive →
✔ PASS	`task_097_Q97_sargable_date_boundary_pruning`	SARGable Partition Pruning: Exact Half-Open Date Boundary Ranges When filtering analytical partitions for a calendar month, `BETWEEN '2026-01-01' AND '2026...	Warehouse & Statistical	0.1 ms / 0.1 ms	1,000,000 rows (11.4 MiB)	Deep Dive →
✔ PASS	`task_098_Q98_window_exclude_group_moving_peer`	Window Peer Frame Exclusion: EXCLUDE GROUP for Clean Cross-Group Contrast When comparing sales across product categories or dates, analysts want to compute the aver...	Warehouse & Statistical	304.3 ms / 287.2 ms	1,000,000 rows (30.5 MiB)	Deep Dive →
✔ PASS	`task_099_Q99_window_exclude_ties_duplicate_handling`	Window Frame Ties Exclusion: EXCLUDE TIES for Unbiased Cumulative Totals In financial ranking, when multiple entities have the exact same score, evaluating cumulat...	Warehouse & Statistical	98.7 ms / 109.1 ms	1,000,000 rows (7.6 MiB)	Deep Dive →
✔ PASS	`task_100_Q100_market_basket_association_rules`	Market Basket Association Rules: Support, Confidence, and Lift in Pure SQL Data miners discover product affinities ('Customers who buy Item A also buy Item B') using...	Warehouse & Statistical	113.7 ms / 112.4 ms	1,000,000 rows (22.9 MiB)	Deep Dive →
✔ PASS	`task_101_Q101_kaplan_meier_churn_survival`	Kaplan-Meier Non-Parametric Customer Survival Analysis in SQL Actuarial and SaaS subscription models estimate the probability of customer survival past ...	Warehouse & Statistical	5.1 ms / 8.0 ms	1,000,000 rows (8.6 MiB)	Deep Dive →
✔ PASS	`task_102_Q102_equi_depth_dynamic_binning_ntile`	Dynamic Equi-Depth Histogram Binning via NTILE in Pure SQL Creating balanced feature histograms for ML requires equal-depth binning (each bucket cont...	Warehouse & Statistical	47.9 ms / 52.3 ms	1,000,000 rows (19.1 MiB)	Deep Dive →
✔ PASS	`task_111_rolling_distinct_active_users`	Rolling 7-Day Distinct Active Users without Triangular Join Explosion Calculates rolling 7-day distinct active users per calendar day. SQL window functions forb...	Algorithmic Efficiency / Window Distinct	658.7 ms / 386.9 ms	1,000,000 rows (7.6 MiB)	Deep Dive →
✔ PASS	`task_112_dual_constraint_sessionization`	Dual-Constraint Sessionization: 30-min Inactivity AND 4-Hour Absolute Hard Cap Groups web clickstream events into sessions. A new session starts if: 1) more than 30 minu...	Sessionization (Gaps & Islands)	194.5 ms / 225.8 ms	1,000,000 rows (11.4 MiB)	Deep Dive →
✔ PASS	`task_113_vector_prefilter_recall_drop`	Vector Nearest Neighbors: The Post-Filtering Empty Set Trap Demonstrates the classic vector database failure mode where applying relational metadata f...	Vector & Retrieval	12.6 ms / 13.9 ms	1,000,000 rows (46.7 MiB)	Deep Dive →
✔ PASS	`task_114_asof_tolerance_expiry`	ASOF Point-in-Time Quote Matching with 500ms Expiry Tolerance Matches executed trades with the prevailing market quote as-of the trade timestamp, but en...	Temporal & Time-Series	352.6 ms / 319.3 ms	2,000,000 rows (76.3 MiB)	Deep Dive →
✔ PASS	`task_115_rrf_hybrid_rank_fusion_isolation`	Reciprocal Rank Fusion (RRF) Hybrid Retrieval without Scale Collapse Fuses BM25 keyword rankings and vector cosine similarity rankings using rank-based Recipro...	Search & Retrieval	546.1 ms / 518.7 ms	1,000,000 rows (38.1 MiB)	Deep Dive →
✔ PASS	`task_116_graph_cycle_visited_protection`	Directed Dependency Graph Cycle Protection via Visited Array Path Traverses downstream dependencies in a directed acyclic graph that contains an intentional...	Graph & Hierarchy	1.0 ms / 0.9 ms	5 rows (200 B)	Deep Dive →
✔ PASS	`task_117_bitemporal_retroactive_audit`	Bitemporal Auditing: Retroactive Policy Revision Point-in-Time State Reconstructs historical corporate insurance coverage on an effective business date, as kno...	Temporal & Time-Series	0.1 ms / 0.1 ms	1,000,000 rows (45.8 MiB)	Deep Dive →
✔ PASS	`task_118_reversal_interleaved_ledger`	Financial Ledger Running Total with Transaction Reversal Nullification Calculates running account balance where REVERSAL records completely void specific prior t...	Warehouse & Statistical	103.4 ms / 101.0 ms	1,000,000 rows (45.8 MiB)	Deep Dive →
✔ PASS	`task_119_multitouch_decay_attribution`	Exponential Half-Life Multi-Touch Marketing Attribution Normalized to 100% Calculates exponential time-decay attribution across previous marketing touches (7-day hal...	Temporal & Time-Series	229.3 ms / 223.9 ms	2,000,000 rows (64.8 MiB)	Deep Dive →
✖ SHAPE_MISMATCH	`task_120_market_basket_lift_smoothing`	Association Rule Lift Matrix with Directional Pair Evaluation Calculates market basket association rule Lift for directional product pairs (A -> B), cor...	Warehouse & Statistical	112.7 ms / 134.5 ms	1,000,000 rows (22.9 MiB)	Deep Dive →