Introduction

Many Machine Learning projects begin with data stored in relational databases. Customer records, transactions, products, invoices, inventory movements, support requests, sensor readings, and operational events are commonly organized into related tables.

SQL, which stands for Structured Query Language, is used to work with relational databases. It allows data professionals to select columns, filter rows, combine tables, calculate summaries, inspect data quality, and create model-ready datasets.

SQL does not replace Python, pandas, or a Machine Learning library. Instead, SQL is commonly used to retrieve and prepare the required information before modeling continues in Python or another analytical environment.

Learning Objectives

Prerequisites

Understanding Relational Data

A relational database organizes information into tables. Each table normally represents a business entity, event, or relationship. Tables can be connected using keys.

Example Database Schema

The examples in this lesson use a simplified customer-order schema. The exact names and column types in your environment may be different.

The SELECT Statement

The SELECT statement retrieves data from one or more database objects.

SELECT
    CustomerId,
    CreatedDate,
    Region,
    Segment,
    IsActive
FROM Customers;

SELECT *
FROM Customers;

SELECT
    CustomerId,
    CreatedDate,
    Region,
    Segment
FROM Customers;

Selecting only required columns reduces unnecessary data transfer, makes the feature definition clearer, and lowers the risk of retrieving sensitive or irrelevant information.

Filter Rows with WHERE

The WHERE clause restricts the returned rows according to one or more conditions.

SELECT
    CustomerId,
    Region,
    Segment
FROM Customers
WHERE IsActive = 1;

Multiple Conditions

SELECT
    CustomerId,
    Region,
    Segment
FROM Customers
WHERE IsActive = 1
  AND Region = 'East';

Match Several Values

SELECT
    CustomerId,
    Region,
    Segment
FROM Customers
WHERE Segment IN (
    'Standard',
    'Premium'
);

Filter a Date Range

SELECT
    OrderId,
    CustomerId,
    OrderDate,
    OrderAmount
FROM Orders
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2026-02-01';

Sort and Limit Results

Use ORDER BY to control result order. During exploration, a row limit can prevent an unnecessarily large result from being returned.

SELECT
    OrderId,
    CustomerId,
    OrderDate,
    OrderAmount
FROM Orders
ORDER BY OrderDate DESC;

A SQL Server example for returning a sample is:

SELECT TOP (100)
    OrderId,
    CustomerId,
    OrderDate,
    OrderAmount
FROM Orders
ORDER BY OrderDate DESC;

Some other database systems use LIMIT instead of TOP.

Handle NULL Values Correctly

A database NULL normally represents a missing, unknown, or unavailable value. It is not the same as zero, an empty string, or the word “NULL.”

Find Missing Values

SELECT
    CustomerId,
    Region
FROM Customers
WHERE Region IS NULL;

Find Non-Missing Values

SELECT
    CustomerId,
    Region
FROM Customers
WHERE Region IS NOT NULL;

Replace NULL for Reporting

SELECT
    CustomerId,
    COALESCE(Region, 'Unknown') AS Region
FROM Customers;

WHERE Region = NULL

WHERE Region IS NULL

Aggregate Functions

Aggregate functions summarize multiple rows into one result. These functions are useful for data profiling and feature engineering.

SELECT
    COUNT(*) AS OrderCount,
    SUM(OrderAmount) AS TotalOrderAmount,
    AVG(OrderAmount) AS AverageOrderAmount,
    MIN(OrderAmount) AS MinimumOrderAmount,
    MAX(OrderAmount) AS MaximumOrderAmount
FROM Orders
WHERE OrderStatus = 'Completed';

Group Data with GROUP BY

The GROUP BY clause calculates summaries for different groups. In Machine Learning feature engineering, it is commonly used to create one set of aggregate features for each observation, such as one row per customer.

SELECT
    CustomerId,
    COUNT(*) AS OrderCount,
    SUM(OrderAmount) AS TotalOrderAmount,
    AVG(OrderAmount) AS AverageOrderAmount,
    MAX(OrderDate) AS MostRecentOrderDate
FROM Orders
WHERE OrderStatus = 'Completed'
GROUP BY CustomerId;

Filter aggregated groups using HAVING:

SELECT
    CustomerId,
    COUNT(*) AS OrderCount,
    SUM(OrderAmount) AS TotalOrderAmount
FROM Orders
WHERE OrderStatus = 'Completed'
GROUP BY CustomerId
HAVING COUNT(*) >= 5;

Combine Tables with JOIN

Real datasets are frequently distributed across multiple tables. A join combines related rows using a defined relationship.

INNER JOIN

An inner join returns rows for which matching values exist in both joined sources.

SELECT
    c.CustomerId,
    c.Region,
    o.OrderId,
    o.OrderDate,
    o.OrderAmount
FROM Customers AS c
INNER JOIN Orders AS o
    ON o.CustomerId = c.CustomerId;

LEFT JOIN

A left join retains all rows from the left table and returns NULL values when a matching right-side row does not exist.

SELECT
    c.CustomerId,
    c.Region,
    o.OrderId,
    o.OrderDate,
    o.OrderAmount
FROM Customers AS c
LEFT JOIN Orders AS o
    ON o.CustomerId = c.CustomerId;

Understand Dataset Granularity

Granularity describes what one row represents. Before writing an ML query, define the observation unit clearly.

Detect Row Multiplication After a Join

A join may produce more rows than expected when one key matches multiple records on either side. Compare row and identifier counts before and after a join.

SELECT
    COUNT(*) AS TotalRows,
    COUNT(DISTINCT CustomerId) AS UniqueCustomers
FROM Customers;

SELECT
    COUNT(*) AS JoinedRows,
    COUNT(DISTINCT c.CustomerId) AS UniqueCustomers
FROM Customers AS c
LEFT JOIN Orders AS o
    ON o.CustomerId = c.CustomerId;

Aggregate Data Before Joining

The following query creates one row per customer from the Orders table before joining the result to Customers.

WITH OrderFeatures AS (
    SELECT
        CustomerId,
        COUNT(*) AS OrderCount,
        SUM(OrderAmount) AS TotalOrderAmount,
        AVG(OrderAmount) AS AverageOrderAmount,
        MAX(OrderDate) AS MostRecentOrderDate
    FROM Orders
    WHERE OrderStatus = 'Completed'
    GROUP BY CustomerId
)
SELECT
    c.CustomerId,
    c.Region,
    c.Segment,
    COALESCE(ofe.OrderCount, 0) AS OrderCount,
    COALESCE(ofe.TotalOrderAmount, 0) AS TotalOrderAmount,
    COALESCE(ofe.AverageOrderAmount, 0) AS AverageOrderAmount,
    ofe.MostRecentOrderDate
FROM Customers AS c
LEFT JOIN OrderFeatures AS ofe
    ON ofe.CustomerId = c.CustomerId;

Create Features with CASE

A CASE expression creates a derived value based on conditions.

SELECT
    CustomerId,
    OrderAmount,
    CASE
        WHEN OrderAmount >= 1000 THEN 'High'
        WHEN OrderAmount >= 500 THEN 'Medium'
        ELSE 'Low'
    END AS OrderValueBand
FROM Orders;

Create a Binary Indicator

SELECT
    CustomerId,
    OrderDate,
    CASE
        WHEN OrderStatus = 'Cancelled' THEN 1
        ELSE 0
    END AS IsCancelled
FROM Orders;

Build Time-Based Features

Dates and timestamps can be transformed into features representing recency, frequency, duration, weekday, month, season, or time since a previous event.

The following example uses SQL Server-style date functions:

DECLARE @PredictionDate date = '2026-09-01';

SELECT
    CustomerId,
    DATEDIFF(
        day,
        CreatedDate,
        @PredictionDate
    ) AS AccountAgeDays
FROM Customers
WHERE CreatedDate <= @PredictionDate;

Count Recent Orders

DECLARE @PredictionDate date = '2026-09-01';

SELECT
    CustomerId,
    COUNT(*) AS OrdersInPrevious90Days,
    SUM(OrderAmount) AS SpendingInPrevious90Days
FROM Orders
WHERE OrderDate < @PredictionDate
  AND OrderDate >= DATEADD(
        day,
        -90,
        @PredictionDate
      )
  AND OrderStatus = 'Completed'
GROUP BY CustomerId;

Prediction Windows and Observation Windows

Time-aware Machine Learning datasets commonly separate the feature observation window from the target prediction window.

Use Window Functions

Window functions calculate values across related rows without collapsing those rows into one grouped result.

Number Orders Within Each Customer

SELECT
    CustomerId,
    OrderId,
    OrderDate,
    ROW_NUMBER() OVER (
        PARTITION BY CustomerId
        ORDER BY OrderDate DESC
    ) AS OrderRecencyRank
FROM Orders;

Find the Previous Order Date

SELECT
    CustomerId,
    OrderId,
    OrderDate,
    LAG(OrderDate) OVER (
        PARTITION BY CustomerId
        ORDER BY OrderDate
    ) AS PreviousOrderDate
FROM Orders;

Create a Running Total

SELECT
    CustomerId,
    OrderId,
    OrderDate,
    OrderAmount,
    SUM(OrderAmount) OVER (
        PARTITION BY CustomerId
        ORDER BY OrderDate, OrderId
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW
    ) AS RunningOrderAmount
FROM Orders;

Common Table Expressions

A common table expression, commonly called a CTE, gives a temporary name to a query result. It can make a complex feature query easier to read and maintain.

WITH CompletedOrders AS (
    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        OrderAmount
    FROM Orders
    WHERE OrderStatus = 'Completed'
),
CustomerOrderFeatures AS (
    SELECT
        CustomerId,
        COUNT(*) AS OrderCount,
        SUM(OrderAmount) AS TotalOrderAmount,
        AVG(OrderAmount) AS AverageOrderAmount
    FROM CompletedOrders
    GROUP BY CustomerId
)
SELECT
    c.CustomerId,
    c.Region,
    c.Segment,
    COALESCE(f.OrderCount, 0) AS OrderCount,
    COALESCE(f.TotalOrderAmount, 0) AS TotalOrderAmount,
    COALESCE(f.AverageOrderAmount, 0) AS AverageOrderAmount
FROM Customers AS c
LEFT JOIN CustomerOrderFeatures AS f
    ON f.CustomerId = c.CustomerId;

Create a Supervised Learning Target

In Supervised Learning, the target represents the outcome the model must learn to predict. The target definition should be created consistently and documented clearly.

The following illustrative SQL Server query creates a target indicating whether a customer had a churn outcome within 30 days after the prediction date.

DECLARE @PredictionDate date = '2026-09-01';

SELECT
    c.CustomerId,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM CustomerOutcomes AS co
            WHERE co.CustomerId = c.CustomerId
              AND co.Churned = 1
              AND co.OutcomeDate >= @PredictionDate
              AND co.OutcomeDate < DATEADD(
                    day,
                    30,
                    @PredictionDate
                  )
        )
        THEN 1
        ELSE 0
    END AS ChurnedWithin30Days
FROM Customers AS c
WHERE c.CreatedDate <= @PredictionDate;

Prevent Machine Learning Data Leakage

Data leakage occurs when information that would not legitimately be available at prediction time influences model training or evaluation. SQL feature queries are a common place where leakage can be introduced accidentally.

SELECT
    CustomerId,
    COUNT(*) AS AllKnownOrders
FROM Orders
GROUP BY CustomerId;

If this query is used to create a historical training observation, it may include orders that occurred after that observation's prediction date.

DECLARE @PredictionDate date = '2026-09-01';

SELECT
    CustomerId,
    COUNT(*) AS HistoricalOrderCount
FROM Orders
WHERE OrderDate < @PredictionDate
GROUP BY CustomerId;

Data-Quality Checks with SQL

SQL can detect missing values, duplicates, invalid ranges, unexpected categories, missing relationships, and inconsistent identifiers before the data is used for Machine Learning.

Count Rows and Unique Keys

SELECT
    COUNT(*) AS TotalRows,
    COUNT(DISTINCT CustomerId) AS UniqueCustomers
FROM Customers;

Find Duplicate Keys

SELECT
    CustomerId,
    COUNT(*) AS DuplicateCount
FROM Customers
GROUP BY CustomerId
HAVING COUNT(*) > 1;

Count Missing Values

SELECT
    SUM(
        CASE
            WHEN Region IS NULL THEN 1
            ELSE 0
        END
    ) AS MissingRegionCount,
    SUM(
        CASE
            WHEN Segment IS NULL THEN 1
            ELSE 0
        END
    ) AS MissingSegmentCount
FROM Customers;

Find Invalid Numerical Values

SELECT
    OrderId,
    CustomerId,
    OrderAmount
FROM Orders
WHERE OrderAmount < 0;

Inspect Category Values

SELECT
    Region,
    COUNT(*) AS CustomerCount
FROM Customers
GROUP BY Region
ORDER BY CustomerCount DESC;

Find Missing Parent Records

SELECT
    o.OrderId,
    o.CustomerId
FROM Orders AS o
LEFT JOIN Customers AS c
    ON c.CustomerId = o.CustomerId
WHERE c.CustomerId IS NULL;

Review Target Distribution

Classification datasets should be examined for target imbalance. A high overall accuracy can be misleading when one class is much more common than another.

SELECT
    Churned,
    COUNT(*) AS CustomerCount
FROM CustomerOutcomes
GROUP BY Churned
ORDER BY Churned;

Calculate Class Percentage

SELECT
    Churned,
    COUNT(*) AS CustomerCount,
    100.0 * COUNT(*) /
        SUM(COUNT(*)) OVER () AS CustomerPercentage
FROM CustomerOutcomes
GROUP BY Churned
ORDER BY Churned;

Write Efficient Queries

ML extraction queries may process large tables. Efficiency reduces database load, execution time, data transfer, and operational cost.

WHERE YEAR(OrderDate) = 2026
  AND MONTH(OrderDate) = 8

WHERE OrderDate >= '2026-08-01'
  AND OrderDate < '2026-09-01'

Range-based filtering can make the time boundary clearer and may allow a database to use an appropriate index more effectively. Actual performance still depends on the database, indexes, data distribution, and query plan.

Secure SQL Practices

ML data access must follow the same security principles as any production data application. The model-development process does not justify unrestricted database access.

customer_id = user_input

query = (
    "SELECT CustomerId, Region "
    "FROM Customers "
    "WHERE CustomerId = '"
    + customer_id
    + "'"
)

query = """
SELECT
    CustomerId,
    Region
FROM Customers
WHERE CustomerId = :customer_id
"""

Access SQL Data from Python

Install Common Packages

pip install pandas sqlalchemy

Install the database driver required for your selected database. The exact package and connection configuration depend on the database platform and organizational environment.

Parameterized pandas Query

import os
import pandas as pd

from sqlalchemy import create_engine, text

# Read the connection string from a protected environment variable.
connection_string = os.environ[
    "ML_DATABASE_CONNECTION_STRING"
]

engine = create_engine(
    connection_string
)

query = text("""
SELECT
    CustomerId,
    Region,
    Segment,
    CreatedDate
FROM Customers
WHERE IsActive = :is_active
  AND CreatedDate < :prediction_date
""")

query_parameters = {
    "is_active": 1,
    "prediction_date": "2026-09-01"
}

with engine.connect() as connection:
    customers = pd.read_sql_query(
        query,
        connection,
        params=query_parameters
    )

print(customers.head())
print(customers.shape)

Code Explanation

connection_string = (
    "database-user-name:"
    "real-password@database-server"
)

Load Large Results in Chunks

A query result may be too large to load into memory at once. pandas can return the result in chunks so each portion can be processed separately.

import pandas as pd
from sqlalchemy import text

query = text("""
SELECT
    OrderId,
    CustomerId,
    OrderDate,
    OrderAmount
FROM Orders
WHERE OrderDate >= :start_date
  AND OrderDate < :end_date
""")

parameters = {
    "start_date": "2026-01-01",
    "end_date": "2026-02-01"
}

with engine.connect() as connection:
    chunks = pd.read_sql_query(
        query,
        connection,
        params=parameters,
        chunksize=10000
    )

    for chunk_number, chunk in enumerate(
        chunks,
        start=1
    ):
        print(
            f"Chunk {chunk_number}: "
            f"{len(chunk)} rows"
        )

        # Perform approved processing here.

End-to-End SQL Feature Dataset

The following SQL Server-style example creates one row per eligible customer. Historical orders and support requests are restricted to dates before the prediction point. The target is measured during a separate future period.

DECLARE @PredictionDate date = '2026-09-01';
DECLARE @ObservationStartDate date = DATEADD(
    day,
    -90,
    @PredictionDate
);
DECLARE @TargetEndDate date = DATEADD(
    day,
    30,
    @PredictionDate
);

WITH EligibleCustomers AS (
    SELECT
        CustomerId,
        CreatedDate,
        Region,
        Segment
    FROM Customers
    WHERE CreatedDate <= @PredictionDate
),
OrderFeatures AS (
    SELECT
        CustomerId,
        COUNT(*) AS OrdersInPrevious90Days,
        SUM(OrderAmount) AS SpendingInPrevious90Days,
        AVG(OrderAmount) AS AverageOrderAmount,
        MAX(OrderDate) AS MostRecentOrderDate
    FROM Orders
    WHERE OrderDate >= @ObservationStartDate
      AND OrderDate < @PredictionDate
      AND OrderStatus = 'Completed'
    GROUP BY CustomerId
),
SupportFeatures AS (
    SELECT
        CustomerId,
        COUNT(*) AS RequestsInPrevious90Days,
        SUM(
            CASE
                WHEN Priority = 'High' THEN 1
                ELSE 0
            END
        ) AS HighPriorityRequests
    FROM SupportRequests
    WHERE CreatedDate >= @ObservationStartDate
      AND CreatedDate < @PredictionDate
    GROUP BY CustomerId
),
TargetLabels AS (
    SELECT
        ec.CustomerId,
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM CustomerOutcomes AS co
                WHERE co.CustomerId = ec.CustomerId
                  AND co.Churned = 1
                  AND co.OutcomeDate >= @PredictionDate
                  AND co.OutcomeDate < @TargetEndDate
            )
            THEN 1
            ELSE 0
        END AS ChurnedWithin30Days
    FROM EligibleCustomers AS ec
)
SELECT
    ec.CustomerId,
    ec.Region,
    ec.Segment,
    DATEDIFF(
        day,
        ec.CreatedDate,
        @PredictionDate
    ) AS AccountAgeDays,
    COALESCE(
        ofe.OrdersInPrevious90Days,
        0
    ) AS OrdersInPrevious90Days,
    COALESCE(
        ofe.SpendingInPrevious90Days,
        0
    ) AS SpendingInPrevious90Days,
    COALESCE(
        ofe.AverageOrderAmount,
        0
    ) AS AverageOrderAmount,
    CASE
        WHEN ofe.MostRecentOrderDate IS NULL
            THEN NULL
        ELSE DATEDIFF(
            day,
            ofe.MostRecentOrderDate,
            @PredictionDate
        )
    END AS DaysSinceMostRecentOrder,
    COALESCE(
        sfe.RequestsInPrevious90Days,
        0
    ) AS RequestsInPrevious90Days,
    COALESCE(
        sfe.HighPriorityRequests,
        0
    ) AS HighPriorityRequests,
    tl.ChurnedWithin30Days
FROM EligibleCustomers AS ec
LEFT JOIN OrderFeatures AS ofe
    ON ofe.CustomerId = ec.CustomerId
LEFT JOIN SupportFeatures AS sfe
    ON sfe.CustomerId = ec.CustomerId
INNER JOIN TargetLabels AS tl
    ON tl.CustomerId = ec.CustomerId;

Query Design Explanation

Validate the Final ML Dataset

Before exporting or training from the resulting query, validate its granularity, uniqueness, feature ranges, missing values, and target distribution.

Validate One Row per Customer

WITH FinalDataset AS (
    -- Place the final feature query here.
    SELECT
        CustomerId
    FROM Customers
)
SELECT
    CustomerId,
    COUNT(*) AS RowCount
FROM FinalDataset
GROUP BY CustomerId
HAVING COUNT(*) > 1;

Validate Feature Ranges

WITH FinalDataset AS (
    -- Place the final feature query here.
    SELECT
        CustomerId,
        AccountAgeDays,
        OrdersInPrevious90Days,
        SpendingInPrevious90Days
    FROM CustomerFeatureView
)
SELECT
    MIN(AccountAgeDays) AS MinimumAccountAgeDays,
    MAX(AccountAgeDays) AS MaximumAccountAgeDays,
    MIN(OrdersInPrevious90Days) AS MinimumOrderCount,
    MAX(OrdersInPrevious90Days) AS MaximumOrderCount,
    MIN(SpendingInPrevious90Days) AS MinimumSpending,
    MAX(SpendingInPrevious90Days) AS MaximumSpending
FROM FinalDataset;

Validate the Target Distribution

WITH FinalDataset AS (
    -- Place the final feature query here.
    SELECT
        ChurnedWithin30Days
    FROM CustomerFeatureView
)
SELECT
    ChurnedWithin30Days,
    COUNT(*) AS CustomerCount
FROM FinalDataset
GROUP BY ChurnedWithin30Days;

Reproducibility and Version Control

The SQL used to create a Machine Learning dataset should be treated as project source code. It should be documented, reviewed, tested, and version controlled.

Common SQL Mistakes in ML Projects

SQL Data Access Best Practices

Hands-On Practice

Knowledge Check

Lesson Summary