SQL-FRONTIER Benchmark Deep-Dive / Vector & Embeddings / task_036_Q36_knn_vector_anomaly_detection
← Prev: K-Means Clustering Assign... ⊞ All 120 Queries Next: Cohort-Constrained Vector... →
✔ PASS Vector & Embeddings task_036_Q36_knn_vector_anomaly_detection Scaled: 1M+ Records Ordered: Yes

Vector Anomaly Detection via Average K-Nearest Neighbor Distance

Candidate Latency (1M Rows)
802.82 ms
(1.0x faster than golden)
Golden Latency (1M Rows)
821.72 ms
DuckDB in-memory engine
Underlying Data Volume
2,500 rows
29.3 KiB across 1 table
Evaluated Model
gemini-3.8-flash-low
Gen Latency: 12.2s
✔ Correctness Verified on 1M+ Records
Candidate SQL output matches ground truth golden query across all multiset rows and numeric tolerances (tol=1e-3). Zero data skew or anti-patterns detected on full dataset scale.

1. Analytical Significance & What It Means

Scenario & Frontier Trap
Business Scenario & Problem

Anomalous sensor readings or fraudulent transaction vectors reside in sparse regions of the embedding space. Measuring the average distance to a vector's 3 nearest neighbors flags outliers.

⚡ The Frontier Trap (Why LLMs Fail)

Setting a global static distance threshold fails when data clusters have variable local densities.

Prompt / User Request Given to Model
Anomalous sensor readings or fraudulent transaction vectors reside in sparse regions of the embedding space. Measuring the average distance to a vector's 3 nearest neighbors flags outliers.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: tx_id, outlier_score, classification.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

1 Tables (2,500 rows • 29.3 KiB)

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

📊 transaction_embeddings 2,500 rows 29.3 KiB Data Volume
tx_id (INTEGER) vec (FLOAT[2])
Data Preview (showing sample rows from 2,500 total rows):
# tx_id vec
1 1 [0.05 0.02]
2 2 [0.01 0.04]
3 3 [0.03 0.01]
4 4 [10. 10.]
5 4 [0.7947 0.1747]
6 5 [0.9107 0.096 ]
7 6 [0.8065 0.1282]
8 7 [0.8067 0.0655]
9 8 [0.8703 0.0569]
10 9 [0.8026 0.0572]
11 10 [0.9017 0.0982]
12 11 [0.8521 0.0285]
13 12 [0.7849 0.1049]
14 13 [0.8606 0.136 ]
15 14 [0.9308 0.1349]

3. Graphical Query Execution Plan & Cost Bottlenecks

Physical Operator Tree (DuckDB 2.0 Profiler)

Visual flowchart of the physical execution pipeline. Operators with high relative costs (≥ 20% of query runtime or heavy latency) are visually highlighted:

Pipeline Latency Distribution (Total Plan Time: 795.49 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
📐 PROJECTION 0.0%
Latency: 0.01 ms
Rows In/Out: 2,499
Data Vol: 0.07 MB
📁 Projections: ['CAST(#0 AS INTEGE...
⏳ ORDER_BY 0.0%
Latency: 0.35 ms
Rows In/Out: 2,499
Data Vol: 0.06 MB
📐 PROJECTION 0.0%
Latency: 0.00 ms
Rows In/Out: 2,499
Data Vol: 0.06 MB
📁 Projections: ['CAST(#0 AS SMALLI...
📐 PROJECTION 0.0%
Latency: 0.11 ms
Rows In/Out: 2,499
Data Vol: 0.07 MB
📁 Projections: ['tx_id', 'outlier_...
📐 PROJECTION 0.0%
Latency: 0.01 ms
Rows In/Out: 2,499
Data Vol: 0.03 MB
📁 Projections: ['CAST(#0 AS INTEGE...
📊 PERFECT_HASH_GROUP_BY 0.0%
Latency: 0.08 ms
Rows In/Out: 2,499
Data Vol: 0.02 MB
📐 PROJECTION 0.0%
Latency: 0.02 ms
Rows In/Out: 4,998
Data Vol: 0.05 MB
📁 Projections: ['tx_id', 'CAST(dis...
📐 PROJECTION 0.0%
Latency: 0.01 ms
Rows In/Out: 4,998
Data Vol: 0.03 MB
📁 Projections: ['CAST(#0 AS SMALLI...
📐 PROJECTION 0.0%
Latency: 0.07 ms
Rows In/Out: 4,998
Data Vol: 0.04 MB
📁 Projections: ['tx_id', 'dist']
📐 PROJECTION 0.0%
Latency: 0.01 ms
Rows In/Out: 4,998
Data Vol: 0.10 MB
📁 Projections: ['#0', '#[2.1]', '#...
⚙️ UNNEST 0.0%
Latency: 0.14 ms
Rows In/Out: 4,998
Data Vol: 0.31 MB
📊 PERFECT_HASH_GROUP_BY 🔥 83.4% Cost
Latency: 663.77 ms
Rows In/Out: 2,499
Data Vol: 0.12 MB
⚠️ Heavy Execution Bottleneck (83.4% of query runtime)
📐 PROJECTION 🔥 10.4% Cost
Latency: 82.61 ms
Rows In/Out: 6,247,498
Data Vol: 190.66 MB
📁 Projections: ['tx_id', 'struct_p...
⚠️ Heavy Execution Bottleneck (10.4% of query runtime)
⚡ NESTED_LOOP_JOIN 6.1%
Latency: 48.23 ms
Rows In/Out: 6,247,498
Data Vol: 142.99 MB
🔍 TABLE_SCAN 0.0%
Latency: 0.04 ms
Rows In/Out: 2,500
Data Vol: 0.03 MB
📁 memory.main.transaction_embeddin...
🔍 TABLE_SCAN 0.0%
Latency: 0.04 ms
Rows In/Out: 2,500
Data Vol: 0.03 MB
📁 memory.main.transaction_embeddin...
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
PERFECT_HASH_GROUP_BY 83.4% 663.77 ms 2,499 0.12 MB - 🔥 BOTTLENECK
PROJECTION 10.4% 82.61 ms 6,247,498 190.66 MB Projections: ['tx_id', 'struct_pack(#[2.1], #[3. 🔥 BOTTLENECK
NESTED_LOOP_JOIN 6.1% 48.23 ms 6,247,498 142.99 MB - Normal
ORDER_BY 0.0% 0.35 ms 2,499 0.06 MB - Normal
UNNEST 0.0% 0.14 ms 4,998 0.31 MB - Normal
PROJECTION 0.0% 0.11 ms 2,499 0.07 MB Projections: ['tx_id', 'outlier_score', 'classif Normal
PERFECT_HASH_GROUP_BY 0.0% 0.08 ms 2,499 0.02 MB - Normal
PROJECTION 0.0% 0.07 ms 4,998 0.04 MB Projections: ['tx_id', 'dist'] Normal
TABLE_SCAN 0.0% 0.04 ms 2,500 0.03 MB memory.main.transaction_embeddings Normal
TABLE_SCAN 0.0% 0.04 ms 2,500 0.03 MB memory.main.transaction_embeddings Normal
PROJECTION 0.0% 0.02 ms 4,998 0.05 MB Projections: ['tx_id', 'CAST(dist AS DOUBLE)'] Normal
PROJECTION 0.0% 0.01 ms 4,998 0.10 MB Projections: ['#0', '#[2.1]', '#[3.1]'] Normal
PROJECTION 0.0% 0.01 ms 2,499 0.03 MB Projections: ['CAST(#0 AS INTEGER)', '#1'] Normal
PROJECTION 0.0% 0.01 ms 2,499 0.07 MB Projections: ['CAST(#0 AS INTEGER)', '#1', '#2'] Normal
PROJECTION 0.0% 0.01 ms 4,998 0.03 MB Projections: ['CAST(#0 AS SMALLINT)', '#1'] Normal
PROJECTION 0.0% 0.00 ms 2,499 0.06 MB Projections: ['CAST(#0 AS SMALLINT)', '#1', '#2' Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted
Candidate SQL Executed by gemini-3.8-flash-low (Runtime on 1M Rows: 802.82 ms) 
WITH pairwise_distances AS
  (SELECT a.tx_id,
          b.tx_id AS neighbor_id,
          array_distance(a.vec, b.vec) AS dist,
          row_number() OVER (PARTITION BY a.tx_id
                             ORDER BY array_distance(a.vec, b.vec) ASC) AS k_rank
   FROM transaction_embeddings a
   JOIN transaction_embeddings b ON a.tx_id != b.tx_id),
     knn_avg AS
  (SELECT tx_id,
          avg(dist) AS mean_2nn_distance
   FROM pairwise_distances
   WHERE k_rank <= 2
   GROUP BY tx_id)
SELECT tx_id,
       round(mean_2nn_distance, 4) AS outlier_score,
       CASE
           WHEN mean_2nn_distance > 5.0 THEN 'ANOMALOUS_OUTLIER'
           ELSE 'NORMAL'
       END AS classification
FROM knn_avg
ORDER BY outlier_score DESC;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 821.72 ms) 
-- Calculate average distance to 2 nearest neighbors to score anomaly level
 WITH pairwise_distances AS
  (SELECT a.tx_id,
          b.tx_id AS neighbor_id,
          array_distance(a.vec, b.vec) AS dist,
          row_number() OVER (PARTITION BY a.tx_id
                             ORDER BY array_distance(a.vec, b.vec) ASC) AS k_rank
   FROM transaction_embeddings a
   JOIN transaction_embeddings b ON a.tx_id != b.tx_id),
      knn_avg AS
  (SELECT tx_id,
          avg(dist) AS mean_2nn_distance
   FROM pairwise_distances
   WHERE k_rank <= 2
   GROUP BY tx_id)
SELECT tx_id,
       round(mean_2nn_distance, 4) AS outlier_score,
       CASE
           WHEN mean_2nn_distance > 5.0 THEN 'ANOMALOUS_OUTLIER'
           ELSE 'NORMAL'
       END AS classification
FROM knn_avg
ORDER BY outlier_score DESC;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE transaction_embeddings (tx_id INT, vec FLOAT[2]);

-- Normal cluster around (0,0); tx_id 4 is an outlier at (10, 10)

INSERT INTO transaction_embeddings
VALUES (1, [0.05, 0.02]::FLOAT[2]),
       (2, [0.01, 0.04]::FLOAT[2]),
       (3, [0.03, 0.01]::FLOAT[2]),
       (4, [10.0, 10.0]::FLOAT[2]);

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (2,499 rows × 3 cols)
# tx_id outlier_score classification
1 2 0.0404 NORMAL
2 1,158 0.04 NORMAL
3 21 0.0391 NORMAL
4 1 0.0335 NORMAL
5 2,180 0.0332 NORMAL
6 988 0.0324 NORMAL
7 1,822 0.0303 NORMAL
8 3 0.0292 NORMAL
9 281 0.0288 NORMAL
10 184 0.0279 NORMAL
11 2,219 0.0274 NORMAL
12 1,541 0.0256 NORMAL
13 1,591 0.0244 NORMAL
14 1,238 0.0207 NORMAL
15 1,138 0.0201 NORMAL
16 683 0.02 NORMAL
17 2,222 0.0186 NORMAL
18 1,718 0.0174 NORMAL
19 1,262 0.0172 NORMAL
20 1,494 0.0171 NORMAL
21 2,158 0.016 NORMAL
22 1,997 0.0156 NORMAL
23 1,396 0.0145 NORMAL
24 42 0.014 NORMAL
25 1,430 0.014 NORMAL
Showing first 25 of 2,499 total rows.
Expected Golden Output (2,499 rows × 3 cols)
# tx_id outlier_score classification
1 2 0.0404 NORMAL
2 1,158 0.04 NORMAL
3 21 0.0391 NORMAL
4 1 0.0335 NORMAL
5 2,180 0.0332 NORMAL
6 988 0.0324 NORMAL
7 1,822 0.0303 NORMAL
8 3 0.0292 NORMAL
9 281 0.0288 NORMAL
10 184 0.0279 NORMAL
11 2,219 0.0274 NORMAL
12 1,541 0.0256 NORMAL
13 1,591 0.0244 NORMAL
14 1,238 0.0207 NORMAL
15 1,138 0.0201 NORMAL
16 683 0.02 NORMAL
17 2,222 0.0186 NORMAL
18 1,718 0.0174 NORMAL
19 1,262 0.0172 NORMAL
20 1,494 0.0171 NORMAL
21 2,158 0.016 NORMAL
22 1,997 0.0156 NORMAL
23 1,396 0.0145 NORMAL
24 42 0.014 NORMAL
25 1,430 0.014 NORMAL
Showing first 25 of 2,499 total rows.
← Prev: K-Means Clustering Assign... ⊞ Back to Benchmark Dashboard Next: Cohort-Constrained Vector... →