Chapter 8
SQL for Data Science
Most Solutions Architects already know how to write SELECT * FROM orders WHERE status = 'active'. That’s operational SQL. Retrieving specific records. What separates it from the SQL a data scientist writes is intent. Analytical SQL summarises, ranks, compares across time windows, and answers questions like “which customers increased their spend the most last quarter?” Most business data lives in relational databases or warehouses rather than tidy CSV files. Fluency in this analytical style is a prerequisite for working with real data. This chapter treats SQL as a language for asking questions of sets of rows, rather than a language for retrieving individual ones. It builds up from GROUP BY to the two tools that make that shift possible: common table expressions and window functions.
8.1 Motivation
The gap between “I can write a WHERE clause” and “I can write the query that answers the business question” is the gap between operational and analytical SQL. It’s where a lot of otherwise-capable engineers stall the first time they’re handed a warehouse table and a business question instead of a well-specified lookup. Relational databases store data in tables. SQL performs set operations on those tables: filtering, joining, grouping, transforming collections of rows at once. Declaratively. You describe what you want and the query planner decides how. The shift that unlocks analytical SQL is thinking in terms of aggregations over groups. Almost every analytical question reduces to “for each group, what is the aggregate of value?” That’s what GROUP BY plus an aggregate function expresses, and what the rest of this chapter’s tools extend.
8.2 Exposition
8.2.1 GROUP BY and Aggregations
GROUP BY partitions the rows of a table into groups sharing a common value, then collapses each group down to a single summary row:
SELECT
department,
COUNT(*) AS headcount,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary,
SUM(salary) AS total_payroll
FROM employees
GROUP BY department
ORDER BY total_payroll DESC;HAVING is the group-level equivalent of WHERE. It filters after the grouping and aggregation have happened. That’s why it can reference an aggregate expression that doesn’t exist until the grouping is done:
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
-- Only departments with high average salary
HAVING AVG(salary) > 80000;WHERE and HAVING don’t do the same job. WHERE restricts which rows enter a group in the first place. HAVING restricts which already-computed groups survive into the output.
8.2.2 CTEs — Common Table Expressions
A common table expression (CTE) is a named subquery defined at the top of a query with WITH. Its purpose is readability, not performance. It lets a complex query be built up as a sequence of named, single-purpose steps, the same way a complicated Python function gets broken into smaller named helper functions, rather than one dense expression.
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
),
revenue_with_growth AS (
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month)
AS prev_revenue,
revenue - LAG(revenue) OVER (ORDER BY month)
AS growth
FROM monthly_revenue
)
SELECT * FROM revenue_with_growth WHERE month >= '2023-01-01';A useful discipline: each CTE should do exactly one thing. Its name should describe that thing well enough that the final SELECT reads almost like English.
8.2.3 Window Functions — The Most Powerful Analytical Tool
A window function computes a value across a set of rows related to the current row, without collapsing those rows the way GROUP BY does. This is the distinction: GROUP BY produces one row per group. A window function keeps every original row and adds a computed column alongside it. The general shape is:
function_name() OVER (
-- like GROUP BY, but doesn't collapse rows
PARTITION BY grouping_column
-- defines the sequence for ordered functions
ORDER BY ordering_column
-- optional: defines the window frame
ROWS BETWEEN ...
)Three families cover most analytical needs. Ranking functions: ROW_NUMBER() assigns 1, 2, 3… with no ties. RANK() assigns 1, 1, 3…, where tied rows share a rank and a gap follows. DENSE_RANK() assigns 1, 1, 2…, with no gap. Offset functions: LAG(col, n) reaches back n rows, LEAD(col, n) reaches forward, both within the current partition and ordering. Aggregate window functions: the same SUM, AVG, COUNT used with GROUP BY, but applied OVER a window instead. SUM(amount) OVER (ORDER BY date) is a running total. With ORDER BY and no frame clause, the default frame is RANGE: rows tied on the ordering column share one running total. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to step row by row. A ROWS frame counts rows, not days, so a 7-row moving average covers 7 days only when every day has exactly one row. COUNT(*) OVER (), an empty OVER (), attaches the grand total row count to every row without filtering or grouping anything.
8.2.4 Joins for Analysis
Most analytical questions pull data from more than one table. INNER JOIN keeps only rows matching in both tables. LEFT JOIN keeps every row from the left table, with NULLs filled in where the right table has no match. RIGHT JOIN is the mirror image; rare in practice, since a LEFT JOIN with the tables reordered reads more naturally. FULL OUTER JOIN keeps every row from both sides. In analytics, LEFT JOIN dominates: there is almost always a fact table (orders, events, transactions) that needs enriching with dimension data (customer or product attributes) while every fact row is preserved regardless of whether a match exists.
8.2.5 Using SQL at Scale
A query that runs fine on a thousand-row table can take minutes, or fail outright, on a billion-row one. The SQL hasn’t changed. What has changed is how much data the engine has to touch to answer it. A badly formed query touches far more of it than necessary.
The starting point for any slow query is EXPLAIN, or EXPLAIN ANALYZE, which runs the query and reports real timings instead of estimates. It shows the query plan: the sequence of scans, joins, and aggregations the engine chose, and roughly how much work each step costs. A sequential scan reads every row in a table. An index scan reads only the rows that match a condition, using a structure built in advance to look them up directly. On a large table, the difference between the two is often the difference between milliseconds and minutes.
Four habits keep a query fast as data grows:
- Filter early. A
WHEREclause applied before aJOINshrinks the table the join has to process. Most modern query planners reorder this automatically, but not always. A query that filters manually inside a CTE before joining never depends on the planner getting it right. - Select only the columns needed.
SELECT *costs little on a small row-based table. On the columnar formats warehouses use, Parquet, Redshift, Snowflake, each column is stored separately. A wideSELECT *on a hundred-column table forces the engine to read all hundred columns off disk, even when a query only needs three of them. - Avoid wrapping a filtered column in a function.
WHERE YEAR(order_date) = 2023cannot use an index onorder_date, because the engine would have to evaluateYEAR()on every row before it can tell which ones qualify.WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'expresses the identical filter in a form the engine can push down to an index or a partition boundary. - Know what’s driving the join. A join between a small table and a large one is cheap if the engine can broadcast the small table to every worker. A join between two large tables usually requires shuffling data across the network, the single most expensive operation in a distributed query engine. Filtering both sides down before the join, not after, is what keeps that shuffle small.
At warehouse scale, these habits are not just about latency. Tools like Amazon Athena bill per byte scanned: a query that reads every column of every partition costs measurably more than the same query with column pruning and partition filters applied, independent of how long it takes to run.
8.3 Trade-offs and Limits
SQL and pandas overlap enough that the real skill is knowing which to reach for, not picking one permanently. SQL is the right tool when the data already lives in a database or warehouse, when a query joins more than two tables, or when the aggregation runs over more rows than fit comfortably in memory. A well-indexed warehouse table can aggregate billions of rows in a way a laptop’s RAM cannot. pandas is the right tool when the data is already loaded as a DataFrame, when the transformation is a custom one specific to feature engineering for a model, or when the task is quick exploration of a CSV that was never going to touch a database. The strongest data scientists are fluent in both and treat them as adjacent stages of a pipeline rather than competitors. SQL does the aggregation in the database, where it’s fast and where the data already lives. pandas does the final feature engineering in Python, where custom logic and ML libraries live. Trying to force one tool to do the other’s job (writing a ten-table join as chained pandas merge() calls, or trying to hand-roll a gradient-boosted feature transform in a CASE WHEN) is an avoidable source of slow, unreadable code.
8.4 Procedures
Procedure 8.1 (Register a DataFrame as a SQL Table and Query It). When to use: data is already in Python as a pandas DataFrame, and the next transformation is more naturally expressed as SQL than as chained pandas calls. This is most often the case once a query needs a GROUP BY, a multi-table JOIN, or a window function.
import duckdb
con = duckdb.connect()
con.register('my_table', df) # register DataFrame as a SQL table
# Run a query and get a DataFrame back
result = con.execute(
"SELECT * FROM my_table WHERE value > 100"
).df()
# Shorthand helper used throughout this chapter
def sql(query): return con.execute(query).df()
# DuckDB can also query DataFrames directly by variable name
result = duckdb.sql("SELECT * FROM df WHERE col > 5").df()Common mistake: relying on DuckDB finding a DataFrame by its variable name. It does, through both duckdb.sql(...) and a connection’s execute(), but only while that variable is in scope. Query it from somewhere else and DuckDB raises CatalogException. con.register('name', df) gives the table a fixed name that works anywhere the connection is used.
Procedure 8.2 (Filter Rows with pandas.query as a Lightweight Alternative). When to use: the only operation needed is row filtering and setting up a DuckDB connection would be overkill.
result = df.query("age > 30 and salary < 80000")
# Reference a Python variable in the query string with @
threshold = 30
result = df.query("age > @threshold")
result = df.query("department == 'Engineering'")Common mistake: reaching for pandas.query on a task that needs a GROUP BY or a window function. It supports row filtering only, nothing else. The moment a query needs an aggregation across groups, switch to Procedure 8.1.
Procedure 8.3 (Aggregate with GROUP BY and HAVING). When to use: the business question is a “for each X, what is the Y” question, the single most common shape in analytical work.
SELECT col, COUNT(*), SUM(val), AVG(val), MIN(val), MAX(val)
FROM table
GROUP BY col
HAVING SUM(val) > 1000
ORDER BY SUM(val) DESC;
-- Conditional aggregation: split one column's sum by a condition
SELECT
SUM(CASE WHEN status = 'completed'
THEN revenue ELSE 0 END) AS completed_rev,
SUM(CASE WHEN status = 'returned'
THEN revenue ELSE 0 END) AS returned_rev
FROM orders;Common mistake: referencing a column alias inside HAVING (e.g. HAVING avg_salary > 80000 when avg_salary was defined with AS in the SELECT). Standard SQL evaluates HAVING before aliases are resolved, and PostgreSQL, SQL Server and Oracle reject the alias. DuckDB, MySQL and SQLite accept it. Repeating the full aggregate expression works everywhere.
Procedure 8.4 (Build Multi-Step Logic with CTEs). When to use: any query with more than two logical steps. Past that point, a single nested query becomes unreadable and hard to debug. A chain of named CTEs is not.
WITH
cte_name_1 AS (
SELECT ... -- step 1
),
cte_name_2 AS (
SELECT ... -- step 2, may reference cte_name_1
FROM cte_name_1
WHERE ...
),
cte_name_3 AS (
SELECT
*,
RANK() OVER (PARTITION BY ... ORDER BY ...) AS rank
FROM cte_name_2
)
SELECT * FROM cte_name_3
WHERE rank <= 10;Common mistake: treating a CTE referenced multiple times in the same query as if it were materialised once. Some engines re-evaluate it at each reference. Others, such as PostgreSQL 12 and later, materialise it. Check the query plan. If a CTE is expensive and reused, a temp table makes the choice explicit.
Procedure 8.5 (Rank and Compare Rows with Window Functions). When to use: a per-row computed value is needed: a rank, a running total, a comparison to the previous row, without collapsing the rows the way GROUP BY would.
SELECT
customer_id,
order_date,
revenue,
-- Running total (cumulative sum)
SUM(revenue) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS running_total,
-- Previous row's value
LAG(revenue, 1) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS prev_revenue,
-- Rank within a group
RANK() OVER (
PARTITION BY region ORDER BY revenue DESC
) AS rank_in_region,
-- Moving average (last 7 rows)
AVG(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7
FROM orders;Common mistake: expecting LAG() to return zero for the first row in a partition. It returns NULL, because there is no previous row. Wrap it in COALESCE(LAG(col) OVER (...), 0) if a zero is what the downstream calculation needs.
Procedure 8.6 (Compute Period-over-Period Growth). When to use: month-over-month, quarter-over-quarter, or year-over-year growth calculations: a GROUP BY to get one row per period, followed by LAG to compare consecutive periods.
WITH monthly AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(revenue) AS revenue
FROM orders
GROUP BY 1
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS abs_change,
(revenue / LAG(revenue) OVER (ORDER BY month) - 1) * 100
AS pct_change
FROM monthly;Common mistake: forgetting that DuckDB’s date arithmetic (INTERVAL '7 days') is not portable. SQLite, for instance, expects date(col, '+7 days'). Check the specific engine’s date-function syntax before assuming a query will run unmodified elsewhere.
Procedure 8.7 (Diagnose a Slow Query with EXPLAIN). When to use: a query is slower than expected and the reason isn’t obvious from reading the SQL alone.
EXPLAIN ANALYZE
SELECT c.customer_id, SUM(o.revenue) AS ltv
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id;Common mistake: running plain EXPLAIN and reading its cost estimates as if they were real timings. Estimates come from table statistics that can be stale or wrong. EXPLAIN ANALYZE executes the query and reports real row counts and timings at each step, which is what a genuine performance investigation needs.
8.5 Worked Example
The tutorial notebook builds a small, realistic in-memory e-commerce database with DuckDB (200 customers, 50 products, and 3,000 orders, generated with np.random.seed(42) so every number below is exactly reproducible), then answers six business questions against it with increasingly complex SQL. Of the 3,000 orders, 2,529 (84.3%) are completed, 321 (10.7%) returned, and 150 (5.0%) pending. Every query below filters to completed orders. Returned or pending revenue hasn’t landed yet.
The first question is a plain GROUP BY: which product category generates the most completed revenue?
SELECT
p.category,
COUNT(o.order_id) AS total_orders,
ROUND(SUM(o.revenue), 2) AS total_revenue,
ROUND(AVG(o.revenue), 2) AS avg_order_value
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY total_revenue DESC;Sports leads on total revenue ($630,002.73 across 756 orders), ahead of Clothing ($553,492.97, 535 orders). But Clothing has the highest average order value at $1,034.57, versus Sports’ $833.34. That’s easy to miss if the chart only shows the total-revenue bar. Home ($408,763.29), Electronics ($344,965.32), and Books ($179,920.02, the smallest category) round out the ranking. Total revenue and average order value answer different business questions. A GROUP BY computes both from the same rows for free. There’s rarely a reason not to pull both.
The second question chains a CTE with a window function to answer something GROUP BY alone cannot: what fraction of total revenue comes from the top customers, the Pareto question?
WITH customer_ltv AS (
SELECT c.customer_id, ROUND(SUM(o.revenue), 2) AS ltv
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id
)
SELECT
ROW_NUMBER() OVER (
ORDER BY ltv DESC, customer_id
) AS customer_rank,
ROUND(
SUM(ltv) OVER (
ORDER BY ltv DESC, customer_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
/ SUM(ltv) OVER () * 100,
2
) AS cum_pct
FROM customer_ltv;The windowed SUM(ltv) is a running total ordered by descending value, with customer_id breaking ties. Dividing by SUM(ltv) OVER (), the same aggregate with an empty window, i.e. the grand total attached to every row, converts that running total into a cumulative percentage without a second query. On this dataset it takes 138 of the 200 customers (69.0%) to reach 80% of total revenue, and 74 customers (37.0%) to reach 50%. That is a noticeably flatter curve than the classic 80/20 rule. That’s worth pausing on. It’s a direct consequence of how the data was generated: revenue per order is quantity × price, drawn from independent, roughly uniform distributions, not from the heavy-tailed distribution real purchasing behaviour usually follows. A synthetic dataset built from independent uniform draws will never produce as concentrated a Pareto curve as real transaction data does. That’s the distributional reasoning from Chapter 3 at work.
The third question is the capstone: of customers whose first order fell in Q1 2022, what fraction placed a second order within 90 days?
WITH first_orders AS (
SELECT customer_id, MIN(order_date) AS first_order_date
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
),
q1_cohort AS (
SELECT customer_id, first_order_date
FROM first_orders
WHERE first_order_date >= '2022-01-01'
AND first_order_date < '2022-04-01'
),
second_orders AS (
SELECT
o.customer_id,
o.order_date,
ROW_NUMBER() OVER (
PARTITION BY o.customer_id ORDER BY o.order_date
) AS order_num
FROM orders o
JOIN q1_cohort c ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
AND o.order_date > c.first_order_date
)
SELECT
COUNT(DISTINCT q1_cohort.customer_id) AS cohort_size,
COUNT(DISTINCT CASE
WHEN second_orders.order_num = 1
AND second_orders.order_date
<= q1_cohort.first_order_date + INTERVAL '90 days'
THEN second_orders.customer_id
END) AS retained_90d
FROM q1_cohort
LEFT JOIN second_orders
ON q1_cohort.customer_id = second_orders.customer_id;The Q1 2022 cohort has 156 customers; 128 of them (82.1%) placed a second order within 90 days. That retention figure is high enough to be suspicious for a real e-commerce business. And it is, for the same reason the Pareto curve above was flatter than expected. Orders were assigned to customers uniformly at random across a 730-day window. So a customer with even a modest order count is, by construction, likely to have some order land in the next 90 days purely by chance. The lesson generalises past this dataset: a retention number computed correctly from synthetic data can still be structurally meaningless if the data-generating process doesn’t resemble the real process it’s standing in for. The query being right is necessary, not sufficient.
-- These same queries run in Amazon Athena (S3-backed
-- serverless SQL) and Amazon Redshift (data warehouse)
-- with minimal changes.
-- Athena date truncation (same as DuckDB)
DATE_TRUNC('month', order_date)
-- Redshift window function (identical syntax)
SUM(revenue) OVER (PARTITION BY customer_id ORDER BY order_date)
-- Athena reads Parquet directly from S3:
-- CREATE EXTERNAL TABLE orders ...
-- LOCATION 's3://my-bucket/orders/'When to prefer Athena over pandas + DuckDB: once the data lives in S3 and is too large to comfortably fit in memory. Athena scales horizontally and is billed per byte of data scanned. For prototyping on a sample or working set that fits in memory, pandas + DuckDB is faster to iterate with and free.
8.6 History and Further Reading
Thakur’s practical approach applies pandas to many of the same operations covered in this chapter. It’s worth reading side-by-side for the comparison (Thakur 2020). For a foundational, SQL-only reference that goes considerably deeper into syntax and query planning than this chapter needs to, Beaulieu’s Learning SQL is the standard recommendation (Beaulieu 2005). DuckDB’s own documentation (duckdb.org) is unusually readable for a database’s docs. It’s worth bookmarking directly, since it’s the engine every listing in this chapter runs against. For how these same concepts map onto managed AWS services at scale, the Athena and Redshift documentation covers the translation referenced in this chapter’s AWS callout.
8.7 How This Connects
This chapter builds on basic SQL (SELECT, WHERE, JOIN) together with Chapter 6’s pandas material. The two are used side by side throughout the Worked Example, with SQL doing the aggregation and pandas or DuckDB moving results back and forth. It enables data pipeline construction (Book 2, Chapter 24) and feature engineering pulled directly from database sources (Chapter 14), both of which assume the GROUP BY/CTE/window-function vocabulary built here. It’s most often confused with pandas, since the two overlap a lot in what they can express. But the right framing is not “which is better” but “where does the data live”: SQL belongs in the database, pandas belongs in Python, and most real pipelines use both.
8.8 Exercises
[W] In one or two sentences, explain why
HAVING SUM(revenue) > 1000works butHAVING total_revenue > 1000(referencing an alias defined asSUM(revenue) AS total_revenuein the same query) fails in many engines.[W] Explain the difference in output between
RANK(),DENSE_RANK(), andROW_NUMBER()when three rows are tied for first place, using Listing 8.9’srank_in_regioncolumn as a starting point.[A] Adapt Listing 8.12 to group by
regioninstead ofcategory(joiningorderstocustomersrather thanproducts). Which region has the highest total completed revenue, and how close is it to the second-place region?[A] Using Listing 8.14 as a base, change the retention window from 90 days to 60 days. Does the retention rate change substantially, and does that change your reading of how meaningful the original 90-day figure was?
[A] Using Listing 8.13’s output, determine how many customers are needed to reach 50% of total revenue, and express that as a percentage of the full 200-customer base.
[E] The Worked Example argues the dataset’s Pareto curve is flatter than real business data because revenue was generated from independent uniform draws rather than a heavy-tailed distribution. Propose a concrete change to the data-generating code in the tutorial notebook (a specific distribution or dependency between variables) that would produce a sharper, more realistic 80/20 split. Explain why your change would have that effect.
[E] Write a query skeleton (not necessarily runnable against this chapter’s schema) that uses conditional aggregation (
SUM(CASE WHEN ... THEN ... ELSE 0 END)) to compute, in a singleGROUP BY categoryquery, both completed revenue and returned revenue side by side as two columns. Explain why this is preferable to running two separate queries and joining the results.[R] Read DuckDB’s documentation on the logical order of SQL clause evaluation (
FROM→WHERE→GROUP BY→HAVING→ window functions →SELECT→ORDER BY). Explain why window functions are evaluated afterWHERE/GROUP BY/HAVINGbut before the finalSELECTlist is resolved. Explain what that ordering implies about why a window function can’t appear directly inside aWHEREclause.
8.9 Mental Model
SQL is a language for asking questions of sets. Not describing procedures. Every analytical question worth writing a query for reduces to the same underlying operation. Partition the data into groups, and optionally order the rows within those groups. Then either aggregate the group down to one row (GROUP BY) or attach a computed value to every row without collapsing it (a window function). Once every query is read through that lens, window functions stop being a scary advanced feature and become the obvious tool for “I need GROUP BY’s aggregation, but I need to keep all my rows.”