vectorized_aggregation.sql

-- =============================================================================
-- Query: vectorized_aggregation.sql
-- Applies to: DuckDB columnar / vectorized execution
-- =============================================================================
-- Purpose:
--   Run heavy aggregations across seasons (avg lap time, fastest lap, laps
--   completed per driver) to show how DuckDB only reads needed columns.
--
-- When to use:
--   After ingest has written Parquet under data/parquet/f1/.
--   Compare wall-clock time with the same shape of query on PostgreSQL.
--
-- How to run:
--   npm run query -- queries/vectorized_aggregation.sql
-- =============================================================================

-- Lap pace across all seasons (columnar: only time_ms + keys are needed)
SELECT
  l.season,
  d.family_name,
  CAST(ROUND(AVG(l.time_ms), 1) AS DOUBLE) AS avg_lap_ms,
  CAST(MIN(l.time_ms) AS INTEGER) AS fastest_lap_ms,
  CAST(COUNT(*) AS INTEGER) AS laps_completed
FROM read_parquet(
  'data/parquet/f1/facts/season=*/laps.parquet',
  hive_partitioning := true
) l
JOIN read_parquet('data/parquet/f1/dims/drivers.parquet') d
  USING (driver_id)
WHERE l.time_ms IS NOT NULL
GROUP BY l.season, d.family_name
ORDER BY l.season, avg_lap_ms
LIMIT 40;

-- Points rollup — another thin-column scan over results
SELECT
  r.season,
  c.constructor_name,
  CAST(SUM(r.points) AS DOUBLE) AS points,
  CAST(SUM(CASE WHEN r.finish_position = 1 THEN 1 ELSE 0 END) AS INTEGER) AS wins
FROM read_parquet(
  'data/parquet/f1/facts/season=*/results.parquet',
  hive_partitioning := true
) r
JOIN read_parquet('data/parquet/f1/dims/constructors.parquet') c
  USING (constructor_id)
GROUP BY r.season, c.constructor_name
ORDER BY r.season, points DESC;