SQL for Data Science: A Beginner’s Roadmap From SELECT to Window Functions

Every data science workflow begins with a query. Before a model trains or a dashboard renders, someone pulls rows from a relational database, filters them, joins them, and reshapes them into features. SQL does that work closest to the data, which makes it faster and more reproducible than loading raw tables into memory first. This guide walks you through six stages, from basic SELECT statements to window functions, using one consistent e-commerce schema and PostgreSQL-compatible syntax.

Quick Takeaways

• Learn in this order: SELECT/WHERE → GROUP BY → JOINs → CTEs → window functions. Each stage builds on the last.
• Window functions (ROW_NUMBER(), LAG(), SUM() OVER) replace most self-joins and Python loops for ranking, retention, and rolling metrics.
• Push computation into the database. Filter and aggregate in SQL, then hand a small, clean result to pandas or Scikit-Learn.
• Practice on real schemas. Use customers, orders, and events tables, not toy single-table exercises.

The Practice Schema

All examples use three tables:

-- customers(customer_id, signup_date, country, plan)
-- orders(order_id, customer_id, order_date, status, total_amount)
-- order_items(item_id, order_id, product_id, category, quantity, unit_price)

Any local PostgreSQL, SQLite, or DuckDB instance works. DuckDB is the lightest option for beginners because it needs no server.

Stage 1: Querying and Filtering Data

A SQL query is declarative. You describe the result you want, and the engine decides how to get it. The logical execution order differs from the written order, and knowing it explains most beginner errors:

1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

-- Explore recent completed orders above $100
SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM orders
WHERE status = 'completed'          -- row-level filter runs before aggregation
  AND total_amount > 100
  AND order_date >= DATE '2026-01-01'
ORDER BY total_amount DESC
LIMIT 20;                           -- always cap exploratory queries

Three habits pay off immediately:

• Name columns explicitly. Avoid SELECT * in production code because schema changes break downstream consumers silently.
• Use LIMIT while exploring. Scanning a billion-row table to eyeball ten rows wastes compute.
• Treat NULL as “unknown,” not zero. The expression NULL = NULL returns NULL, not TRUE. Use IS NULL instead.

Stage 2: Aggregation With GROUP BY and HAVING

Aggregation turns event-level rows into entity-level features. This is the foundation of feature engineering in SQL.

-- Customer-level features: order count, revenue, average basket
SELECT
    customer_id,
    COUNT(*)                         AS order_count,
    SUM(total_amount)                AS lifetime_revenue,
    AVG(total_amount)                AS avg_order_value,
    MAX(order_date)                  AS last_order_date
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) >= 3;                -- HAVING filters groups, WHERE filters rows

WHERE cannot reference aggregates because it runs before grouping. Use HAVING for conditions on COUNT(), SUM(), and similar functions.

Use COUNT(DISTINCT col) for unique counts. Remember that COUNT(col) skips NULLs while COUNT(*) does not.

Stage 3: JOINs for Building Training Tables

Real analysis spans multiple tables. JOINs combine them on shared keys.

-- Include customers who never ordered (churn-risk population)
SELECT
    c.customer_id,
    c.country,
    c.plan,
    COALESCE(COUNT(o.order_id), 0)       AS order_count,
    COALESCE(SUM(o.total_amount), 0)     AS revenue
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.customer_id
      AND o.status = 'completed'         -- filter in ON, not WHERE, to keep the LEFT JOIN intact
GROUP BY c.customer_id, c.country, c.plan;

Placing o.status = ‘completed’ in the WHERE clause would convert this LEFT JOIN into an INNER JOIN, because NULL rows fail the filter. This is one of the most common silent bugs in analytics SQL.

Join fan-out is the second trap. Joining a customer to both orders and order items multiplies rows, so sums double-count. Aggregate each table to the grain of the join key first, then join the results.

Stage 4: Subqueries and CTEs

A Common Table Expression (CTE) names an intermediate result. CTEs turn nested, unreadable queries into a top-to-bottom pipeline.

WITH customer_stats AS (
    SELECT
        customer_id,
        COUNT(*)          AS order_count,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
revenue_benchmark AS (
    SELECT AVG(revenue) AS avg_revenue
    FROM customer_stats
)
SELECT
    s.customer_id,
    s.revenue,
    s.revenue - b.avg_revenue AS delta_vs_average
FROM customer_stats AS s
CROSS JOIN revenue_benchmark AS b
WHERE s.revenue > b.avg_revenue
ORDER BY s.revenue DESC;

Treat CTEs as readability tools. Some engines inline them, while others materialize them, so check the execution plan with EXPLAIN on large data.

Stage 5: Window Functions

A window function computes a value across a set of related rows without collapsing them. GROUP BY reduces rows to one per group. A window function keeps every row and adds a computed column.

The syntax has three parts:

function_name(args) OVER (
    PARTITION BY column     -- defines the group (optional)
    ORDER BY column         -- defines row order inside the group
    ROWS BETWEEN ...        -- defines the frame (optional)
)

Ranking: ROW_NUMBER, RANK, DENSE_RANK

-- Top 3 products by revenue within each category
WITH product_revenue AS (
    SELECT
        category,
        product_id,
        SUM(quantity * unit_price) AS revenue
    FROM order_items
    GROUP BY category, product_id
),
ranked AS (
    SELECT
        category,
        product_id,
        revenue,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY revenue DESC
        ) AS rn
    FROM product_revenue
)
SELECT category, product_id, revenue
FROM ranked
WHERE rn <= 3;                        -- window results cannot be filtered in the same SELECT

Lag and Lead: Time-Aware Features

-- Days since the previous order, per customer
SELECT
    customer_id,
    order_id,
    order_date,
    order_date - LAG(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS days_since_prev_order        -- NULL for each customer's first order
FROM orders
WHERE status = 'completed';

LAG() looks backward and LEAD() looks forward. Both produce the inter-event gaps that feed churn, recency, and purchase-cycle models.

Running Totals and Moving Averages

-- Daily revenue with cumulative total and 7-day moving average
WITH daily AS (
    SELECT order_date, SUM(total_amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY order_date
)
SELECT
    order_date,
    revenue,
    SUM(revenue) OVER (ORDER BY order_date)                     AS cumulative_revenue,
    AVG(revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW                -- 7-row frame including today
    )                                                           AS moving_avg_7d
FROM daily
ORDER BY order_date;

Frame definitions matter. The default frame with an ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats ties as peers. Specify ROWS BETWEEN explicitly when you want a fixed row count.

Stage 6: Cleaning and Transforming Data in SQL

Three functions handle most cleaning tasks.

SELECT
    customer_id,
    COALESCE(country, 'Unknown')                  AS country_clean,   -- impute missing values
    CASE
        WHEN plan IN ('pro', 'team') THEN 'paid'
        WHEN plan = 'free'           THEN 'free'
        ELSE 'other'
    END                                           AS plan_group,      -- recode categories
    total_amount / NULLIF(item_count, 0)          AS avg_item_price   -- avoid divide-by-zero
FROM customer_summary;

Connecting SQL to Python

Aggregate in the database, then analyze in Python. This keeps memory use low and makes the feature logic versionable.

import pandas as pd
from sqlalchemy import create_engine, text

# Connection string format: dialect+driver://user:password@host:port/dbname
engine = create_engine("postgresql+psycopg2://user:password@localhost:5432/shop")

query = text("""
    SELECT
        customer_id,
        COUNT(*)           AS order_count,
        SUM(total_amount)  AS revenue,
        MAX(order_date)    AS last_order_date
    FROM orders
    WHERE status = 'completed'
      AND order_date >= :start_date
    GROUP BY customer_id
""")

# Parameterized queries prevent SQL injection and allow reuse
df = pd.read_sql(query, engine, params={"start_date": "2026-01-01"})

# Downstream feature engineering in pandas
df["recency_days"] = (pd.Timestamp("2026-10-18") - pd.to_datetime(df["last_order_date"])).dt.days
print(df.describe())

Real-World Use Cases

Predicting Customer Churn: Building the Label and Features

Define churn as no order in the 90 days after a snapshot date. Use LEFT JOIN to attach the label and LAG()-based gaps as features:

WITH snapshot AS (SELECT DATE '2026-06-30' AS snap_date),
features AS (
    SELECT
        o.customer_id,
        COUNT(*)                           AS orders_before,
        SUM(o.total_amount)                AS revenue_before,
        MAX(o.order_date)                  AS last_order
    FROM orders o, snapshot s
    WHERE o.order_date <= s.snap_date
      AND o.status = 'completed'
    GROUP BY o.customer_id
),
future AS (
    SELECT DISTINCT o.customer_id
    FROM orders o, snapshot s
    WHERE o.order_date >  s.snap_date
      AND o.order_date <= s.snap_date + 90
      AND o.status = 'completed'
)
SELECT
    f.*,
    CASE WHEN fu.customer_id IS NULL THEN 1 ELSE 0 END AS churned   -- label
FROM features f
LEFT JOIN future fu USING (customer_id);

Keeping features strictly before the snapshot date prevents data leakage, the most damaging error in churn modeling.

Cohort Retention Analysis

Group customers by signup month with DATE_TRUNC(‘month’, signup_date). Count active customers per month offset using a window or a date difference. The result feeds a retention heatmap.

Deduplicating Event Streams

Sensor and clickstream tables often contain duplicates. Keep the latest record per key:

SELECT *
FROM (
    SELECT
        e.*,
        ROW_NUMBER() OVER (
            PARTITION BY device_id, event_ts
            ORDER BY ingested_at DESC
        ) AS rn
    FROM sensor_events e
) t
WHERE rn = 1;

Performance Habits That Scale

A Six-Week Learning Roadmap

Frequently Asked Questions

Is SQL enough to get a data science job?

No, but it is a hard requirement. Most data science and analyst interviews include live SQL, and the work depends on it for extracting data. Pair SQL with Python or R, statistics, and a modeling library to cover the full role.

What is the difference between GROUP BY and window functions?

GROUP BY collapses rows into one row per group. Window functions keep every input row and add a computed column, such as a rank or running total. Use GROUP BY for summaries and window functions when you need row-level detail alongside group-level context.

How long does it take to learn SQL for data science?

Most beginners reach working proficiency in four to six weeks with daily practice. Core querying and JOINs take about two weeks. Window functions and CTEs take another two to four weeks of applied practice on realistic schemas.

Which SQL dialect should a beginner learn first?

Start with PostgreSQL. Its syntax follows the SQL standard closely, and skills transfer to MySQL, SQL Server, BigQuery, Snowflake, and DuckDB. Dialect differences mostly affect date functions and string handling.