Lesson 07 — SQL for Data Engineers

The analytical SQL toolkit pandas users miss: windows, CTEs, GROUPING SETS, QUALIFY.

Why SQL, not pandas?

• Lingua franca — every warehouse and lakehouse speaks SQL: Snowflake, BigQuery, Redshift, DuckDB, Iceberg-on-Trino.
• Computation at the engine — SQL pushes work to the database; your laptop doesn't move 100 GB into RAM.
• Declarative — you describe what, the planner figures out how (indexes, partition pruning, vectorisation).
• Universal readability — analysts, data scientists, finance, exec — every downstream consumer reads SQL.

The pandas-user gap

CTEs — name your steps

CTEs read top-down, just like a Python function. Each WITH clause is a named subquery you can compose. Aliasing intermediate steps beats nesting subqueries 4 levels deep.

WITH hourly_events AS (
  SELECT
    date_trunc('hour', created_at) AS hour,
    type,
    COUNT(*) AS n
  FROM events
  GROUP BY 1, 2
),
top_types AS (
  SELECT type, SUM(n) AS total
  FROM hourly_events
  GROUP BY type
  ORDER BY total DESC
  LIMIT 5
)
SELECT * FROM top_types;

Window functions — the headline

OVER (PARTITION BY ... ORDER BY ...) adds rows of context without losing the row itself.

SELECT
  actor,
  created_at,
  COUNT(*) OVER (PARTITION BY actor) AS lifetime_events,
  ROW_NUMBER() OVER (PARTITION BY actor ORDER BY created_at) AS rn
FROM events;

Every row stays in the result. The window function attaches summary context alongside the original row — unlike GROUP BY, which collapses rows.

Three window-function families

QUALIFY — the WHERE for windows

-- Get the FIRST event per user
SELECT
  actor, type, created_at,
  ROW_NUMBER() OVER (PARTITION BY actor ORDER BY created_at) AS rn
FROM events
QUALIFY rn = 1;

QUALIFY filters after the window function — the same role WHERE plays for raw columns, HAVING plays for GROUP BY aggregates. Supported by DuckDB, Snowflake, BigQuery. For Postgres / MySQL: wrap in a CTE and filter with WHERE.

GROUPING SETS, ROLLUP, CUBE

SELECT
  date_trunc('day', created_at) AS day,
  type,
  COUNT(*) AS n
FROM events
GROUP BY GROUPING SETS (
  (day, type),
  (day),
  (type),
  ()
);

One query, four aggregation levels: by (day, type), by day, by type, grand total. ROLLUP is hierarchical: (day, type) → (day) → (). CUBE is the full cross-product of every subset.

Set operators — UNION vs UNION ALL

EXISTS vs IN vs JOIN — the dedup trap

-- WRONG: blows up rows when actors join to commits
SELECT a.* FROM actors a
JOIN commits c ON c.actor_id = a.id;

-- RIGHT: keep one row per actor, filter on existence
SELECT a.* FROM actors a
WHERE EXISTS (SELECT 1 FROM commits c WHERE c.actor_id = a.id);

JOIN multiplies rows; EXISTS doesn't. If you only want to filter, prefer EXISTS. Also: NOT IN returns no rows if any subquery value is NULL — use NOT EXISTS instead.

SQL execution order

You write SQL in one order; the engine evaluates it in another:

The OVER clause sits at step 5 — after GROUP BY/HAVING, before SELECT/ORDER BY. That's why QUALIFY (post-window filter) is its own keyword.

GitHub events worked example

Top 3 event types per actor — one query.

WITH ranked AS (
  SELECT
    actor,
    type,
    COUNT(*) AS n,
    ROW_NUMBER() OVER (
      PARTITION BY actor
      ORDER BY COUNT(*) DESC
    ) AS rn
  FROM events
  GROUP BY actor, type
)
SELECT actor, type, n
FROM ranked
WHERE rn <= 3
ORDER BY actor, rn;

GROUP BY + window function + post-window filter. Roughly 10 lines of SQL = 30 lines of pandas.

EXPLAIN — read your query plan

EXPLAIN ANALYZE
SELECT actor, COUNT(*) AS n
FROM read_parquet('data/parquet/date=*/*.parquet', hive_partitioning => true)
WHERE date = '2024-01-15'
GROUP BY actor
ORDER BY n DESC
LIMIT 10;

Every engine has an EXPLAIN. Read it. The optimiser tells you what scans, joins, and aggregates it will run — and in what order. If a query is slow, the plan is the first place to look. Look for: partition pruning, join order, unexpected cross joins.

Recap & what's next

• SQL pushes work to the engine — return only the small result, not 100 GB of raw data.
• Window functions + CTEs are the analytical toolkit pandas users miss — master them and half the BI work writes itself.
• Read EXPLAIN before you optimise — the plan tells you where the time goes.