SQL for Analytics and ML Pipelines

Why ML Engineers Need SQL

Data lives in databases. Feature engineering happens close to the data - in SQL - before it ever reaches a Pandas DataFrame. ML engineers who cannot write SQL depend on data engineers to extract features for them, which introduces delays, miscommunications, and bottlenecks.

This module covers the SQL patterns that appear most often in ML feature engineering and dataset creation.

SELECT, WHERE, and GROUP BY: The Core

sqlCopy
-- Select specific columns with a filter
SELECT user_id, amount, category, transaction_date
FROM transactions
WHERE transaction_date >= '2024-01-01'
  AND amount > 0
ORDER BY transaction_date DESC;

-- Aggregate: total spend and transaction count per user
SELECT
    user_id,
    COUNT(*)                    AS num_transactions,
    SUM(amount)                 AS total_spend,
    AVG(amount)                 AS avg_transaction,
    MAX(transaction_date)       AS last_transaction_date
FROM transactions
WHERE transaction_date >= '2023-01-01'
GROUP BY user_id;

GROUP BY is the SQL equivalent of Pandas groupby().agg(). Every ML feature derived from event-level data - total purchases, session count, recency - starts here.

JOINs: Combining Tables

sqlCopy
-- INNER JOIN: only rows that match in both tables
SELECT u.user_id, u.signup_date, t.total_spend
FROM users u
INNER JOIN (
    SELECT user_id, SUM(amount) AS total_spend
    FROM transactions
    GROUP BY user_id
) t ON u.user_id = t.user_id;

-- LEFT JOIN: all users, NULL for those with no transactions
SELECT u.user_id, u.signup_date, COALESCE(t.total_spend, 0) AS total_spend
FROM users u
LEFT JOIN (
    SELECT user_id, SUM(amount) AS total_spend
    FROM transactions GROUP BY user_id
) t ON u.user_id = t.user_id;

COALESCE(value, default) replaces NULL with a default - essential after left joins that produce NULLs for non-matching rows.

CTEs: Making Complex Queries Readable

Common Table Expressions (WITH clauses) name intermediate results, making multi-step queries readable without nested subqueries:

sqlCopy
WITH
user_activity AS (
    SELECT
        user_id,
        COUNT(*)               AS num_events,
        MAX(event_date)        AS last_event,
        MIN(event_date)        AS first_event
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY user_id
),

user_spend AS (
    SELECT user_id, SUM(amount) AS total_spend_90d
    FROM transactions
    WHERE transaction_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY user_id
),

combined_features AS (
    SELECT
        a.user_id,
        a.num_events,
        a.last_event,
        a.first_event,
        EXTRACT(DAY FROM a.last_event - a.first_event) AS active_days,
        COALESCE(s.total_spend_90d, 0)                AS total_spend_90d
    FROM user_activity a
    LEFT JOIN user_spend s USING (user_id)
)

SELECT *
FROM combined_features
WHERE num_events >= 3;

CTEs are how production feature pipelines are written. Each CTE is one logical step, named and reusable within the same query.

Window Functions: Computing Features Without Aggregating Away Rows

Window functions compute statistics over a group of rows while keeping each individual row:

sqlCopy
SELECT
    user_id,
    transaction_date,
    amount,
    -- Running total (cumulative spend at each transaction)
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spend,
    -- Rank within user (most recent = rank 1)
    ROW_NUMBER() OVER (
        PARTITION BY user_id
        ORDER BY transaction_date DESC
    ) AS recency_rank,
    -- Previous transaction amount
    LAG(amount, 1) OVER (
        PARTITION BY user_id
        ORDER BY transaction_date
    ) AS prev_transaction_amount
FROM transactions;

Window functions are critical for temporal feature engineering. LAG gives you the previous value. ROW_NUMBER lets you select the most recent event per entity. SUM() OVER (...) gives you cumulative statistics at each point in time - without temporal leakage.

Data Quality Checks in SQL

Before building features, validate the raw data:

sqlCopy
-- Check for NULL proportions in key columns
SELECT
    COUNT(*) AS total_rows,
    COUNT(user_id) AS non_null_user_id,
    COUNT(amount) AS non_null_amount,
    ROUND(1.0 - COUNT(amount)::FLOAT / COUNT(*), 4) AS amount_null_rate
FROM transactions;

-- Check for unexpected duplicates
SELECT user_id, transaction_id, COUNT(*) AS count
FROM transactions
GROUP BY user_id, transaction_id
HAVING COUNT(*) > 1;

-- Check label distribution
SELECT label, COUNT(*) AS count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct
FROM training_labels
GROUP BY label;

-- Check for train/validation leakage by event date
SELECT
    CASE WHEN event_date < '2024-07-01' THEN 'train' ELSE 'val' END AS split,
    COUNT(*) AS n_records,
    MIN(event_date) AS earliest,
    MAX(event_date) AS latest
FROM labeled_events
GROUP BY 1;

These queries belong in a validate_data.sql or validate_data.py script that runs as part of the data pipeline, not as a one-off check.

Creating a Training Dataset in SQL

A complete training dataset query:

sqlCopy
WITH
-- Step 1: Define the prediction events (what we are predicting, at what time)
prediction_events AS (
    SELECT
        user_id,
        DATE_TRUNC('month', subscription_date) AS label_month,
        CASE WHEN cancelled_within_30d THEN 1 ELSE 0 END AS label
    FROM subscriptions
    WHERE subscription_date BETWEEN '2023-01-01' AND '2024-06-30'
),

-- Step 2: Build features using only information available before the label date
features AS (
    SELECT
        e.user_id,
        e.label_month,
        COUNT(t.transaction_id) AS num_transactions_prior,
        COALESCE(SUM(t.amount), 0) AS total_spend_prior,
        COALESCE(
            EXTRACT(DAY FROM e.label_month - MAX(t.transaction_date)),
            90
        ) AS days_since_last_transaction
    FROM prediction_events e
    LEFT JOIN transactions t
        ON e.user_id = t.user_id
        AND t.transaction_date < e.label_month   -- Only prior data
    GROUP BY e.user_id, e.label_month
)

-- Step 3: Join features to labels
SELECT
    e.user_id,
    e.label_month,
    f.num_transactions_prior,
    f.total_spend_prior,
    f.days_since_last_transaction,
    e.label
FROM prediction_events e
LEFT JOIN features f USING (user_id, label_month);

The AND t.transaction_date < e.label_month clause is the temporal leakage prevention - it ensures features are built from data available at the time of prediction.

Common Mistakes and Bad Instincts

Using COUNT(*) vs. COUNT(column). COUNT(*) counts all rows including NULLs. COUNT(column) counts only non-NULL values. Know which you need.

Joining on a non-unique key without checking. A join on a key that is not unique in one of the tables multiplies rows, inflating your training set with duplicates. Always check COUNT(*) vs COUNT(DISTINCT key) before joining.

Not filtering by date before aggregating. Aggregating all historical data when you only need the last 90 days wastes compute and produces features the model would not have at prediction time.

Using application-layer IDs as ML IDs. Application databases use UUIDs or surrogate keys. ML datasets need stable, consistent entity identifiers. Verify that the user_id in your SQL is the same concept throughout the pipeline.

Where to Go Next

SQL and Pandas are used together in every production ML pipeline: SQL extracts and aggregates at the database level; Pandas handles the last-mile transformations in Python. Module 8 (Software Engineering Fundamentals) shows how to organize both into a testable, reproducible project structure that a team can maintain.