10 SQL Analytics

10 · SQL Analytics

Module: bmw_sales.sql · Queries: sql/queries/*.sql · Report: reports/sql_insights.md · Command: make sql · ADR: 0007

A lightweight analytical layer that runs versioned .sql files directly against the raw CSV via DuckDB — no database server, no ETL. Business logic stays in plain, reviewable SQL; the Python layer just orchestrates execution.

How it works (sql/analytics.py)

_connect() opens an in-memory DuckDB connection and exposes the CSV as a view bmw via read_csv_auto. run_query(name) executes the matching sql/queries/<name>.sql; run_all() runs every query. (DuckDB cannot bind parameters inside CREATE VIEW, so the trusted dataset path is inlined with single-quote escaping — injection-safe.)

The queries

Query	What it computes	SQL features
top_regions_by_volume	total / avg volume + share of total per region	window SUM() OVER ()
price_stats_by_model	mean + P25/median/P75 price per model	quantile_cont
electrification_by_region	Hybrid+Electric share of volume per region	conditional aggregation
yoy_volume	year-over-year total volume + % change	window LAG()
high_rate_by_region	share of High classification per region	conditional aggregation

What the SQL shows

The results are the SQL view of the same finding the statistical audit proves: flat distributions across regions (16.7% each) and models ($75k each) — i.e. the data is uniform noise (see 04-Data-Integrity-and-Signal-Audit). SQL, econometrics and the permutation test all converge on the same truth.

Surfaced in the dashboard's SQL Insights tab (region table, YoY line, electrification bar) — see 12-The-Dashboard.

Continue to 11-Explainability-SHAP.