Quick summary
Summarize this blog with AI
What SQL contributes to a data science project
SQL turns a business question into a defined dataset: which rows qualify, what one row represents, and how values are combined. A model or chart is only as reliable as that dataset. This tutorial follows a small purchase analysis from an aggregate to a transaction-level window calculation, then explains the checks needed before handing the result to Python or R.
All examples use PostgreSQL 16 syntax and fictional data. Each query supplies its own inputs with VALUES; it does not create tables or change stored data. The aim is an analysis you can inspect and reproduce, rather than an unsupported claim that SQL automatically improves business outcomes.
The decisions to make before querying
- Grain: one purchase, customer, or customer-month?
- Population: which dates, statuses, currencies, and customers belong?
- Measure: gross sales, net sales after refunds, or another definition?
- Timing: what information was available at the time being analyzed?
- Validation: what counts and totals should stay unchanged?
Define the data and its grain
Our source has one row per purchase. purchase_id is unique, customer_id can repeat, and amount is a non-null value in a single fictional currency. We use calendar dates, not timestamps. A real purchase table also needs explicit rules for cancellations, refunds, duplicate events, and currency conversion.
Start with a question that has boundaries
Question: which customers spent more than 1,000 during January 2026? The strict comparison excludes exactly 1,000. The half-open date range includes January 1 and excludes February 1. This convention also avoids accidentally omitting the last day's afternoon purchases when a production column is a timestamp.
Filter, group, and check the result
WITH purchases(purchase_id, customer_id, purchase_date, amount) AS (
VALUES
(1, 101, DATE '2026-01-02', 600.00),
(2, 101, DATE '2026-01-10', 500.00),
(3, 101, DATE '2026-01-10', 100.00),
(4, 102, DATE '2026-01-03', 700.00),
(5, 102, DATE '2026-01-20', 300.00),
(6, 103, DATE '2026-02-01', 900.00)
)
SELECT customer_id, COUNT(*) AS purchases, SUM(amount) AS total_spent
FROM purchases
WHERE purchase_date >= DATE '2026-01-01'
AND purchase_date < DATE '2026-02-01'
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY customer_id;customer_id | purchases | total_spent
101 | 3 | 1200.00Customer 102 totals exactly 1,000 and is excluded. Customer 103 has only a February purchase and is excluded. Before the HAVING filter, the January population contains five purchases worth 2,200. These are useful cross-checks against accidental row loss or duplication.
An aggregate changes what a row means
The grouped result has one row per customer. It exposes the columns selected by the query, not the individual purchase dates and amounts that were aggregated. A later query cannot retrieve purchase_date from that customer-level result unless it was explicitly retained with a meaningful aggregation. To analyze individual purchases for selected customers, join their IDs back to the purchase-level data.
Combine customer selection with purchase analysis
Use a common filtered population
The next query first defines January purchases, then identifies qualifying customer IDs. Both the qualification and subsequent purchase analysis use that same date range. Keeping these stages visible makes it easier to catch a mismatch such as qualifying customers using lifetime spending but labeling the output as January spending.
Calculate the average of the last two purchases
WITH purchases(purchase_id, customer_id, purchase_date, amount) AS (
VALUES
(1, 101, DATE '2026-01-02', 600.00),
(2, 101, DATE '2026-01-10', 500.00),
(3, 101, DATE '2026-01-10', 100.00),
(4, 102, DATE '2026-01-03', 700.00),
(5, 102, DATE '2026-01-20', 300.00),
(6, 103, DATE '2026-02-01', 900.00)
), january_purchases AS (
SELECT *
FROM purchases
WHERE purchase_date >= DATE '2026-01-01'
AND purchase_date < DATE '2026-02-01'
), high_value_customers AS (
SELECT customer_id
FROM january_purchases
GROUP BY customer_id
HAVING SUM(amount) > 1000
)
SELECT p.customer_id, p.purchase_id, p.purchase_date, p.amount,
ROUND(AVG(p.amount) OVER (
PARTITION BY p.customer_id
ORDER BY p.purchase_date, p.purchase_id
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
), 2) AS last_two_purchase_avg
FROM january_purchases AS p
JOIN high_value_customers AS h ON h.customer_id = p.customer_id
ORDER BY p.customer_id, p.purchase_date, p.purchase_id;customer_id | purchase_id | purchase_date | amount | last_two_purchase_avg
101 | 1 | 2026-01-02 | 600.00 | 600.00
101 | 2 | 2026-01-10 | 500.00 | 550.00
101 | 3 | 2026-01-10 | 100.00 | 300.00The first purchase has no predecessor, so its average uses one value. The next uses 600 and 500; the last uses 500 and 100. The unique purchase ID breaks ties between same-day purchases. This is a two-purchase average, not a two-day average. An unbounded frame would instead calculate a cumulative average. See PostgreSQL's window-function tutorial for frame semantics and its CTE documentation for query composition.
Check what the selection means
This is a retrospective January analysis: a customer qualifies using the whole month's spending, including purchases after their first purchase. It is unsuitable as a historical model feature at January 2. For a predictive task, build eligibility and features from information available strictly before each prediction time, and evaluate on later outcomes. Otherwise the query can leak future information into training.
Hand a validated dataset to Python or R
A database query becomes a data frame
pandas.read_sql_query() sends SQL through a supported database connection and returns a DataFrame. It does not run SQL directly against an arbitrary existing DataFrame. Its API documentation describes accepted connections and parameters. In R, a database interface such as DBI serves the same broad purpose. Keep database credentials outside notebooks and bind query values through the driver.
Export only columns and rows needed for the next step. Record the query, extraction time, database dialect, and data snapshot where available. Inspect how nulls, decimals, dates, and time zones arrive in the destination library. A correct SQL numeric result can still lose precision or change meaning during conversion.
Keep the logic portable, then verify the engine
SQL also appears in distributed processing systems, but functions, types, window features, and execution plans differ. Treat a query tested on PostgreSQL as a PostgreSQL example. Port it deliberately and verify the same small fixture before running it over a larger dataset.
Apply the same discipline to other domains
Retail analysis
Join order items to orders only after deciding whether an order's total is already repeated on every item. Summing a repeated order total over the join overstates revenue. Aggregate each source to the intended join grain first.
Healthcare analysis
Define whether a row represents a person, visit, or measurement. Repeated visits should not silently become additional people in a population count. Access permissions and approved data handling remain separate requirements from query correctness.
Financial analysis
Define event time, posting time, and reversal treatment before summing transactions. A reconciliation should explain differences between the queried total and the authoritative ledger, rather than assume that a successful query proves the measure is correct.
A repeatable review before using the result
Optimize after establishing correctness
Inspect the execution plan and representative data sizes. Test indexes and filters against observed bottlenecks; a CTE is an organizational tool, not a promise of faster execution. Avoid exporting every column to compensate for an unclear analysis requirement.
Test the awkward rows
- Two purchases on the same date: is ordering deterministic?
- A total exactly at the threshold: does the comparison match the wording?
- No purchases in the period: should the customer disappear or appear with zero?
- Null amounts: should they be excluded, rejected, or treated another way?
- Multiple matches in a joined table: do counts and totals multiply?
Save the definition with the query
Keep fixtures, expected results, business definitions, and query revisions together. Have a reviewer explain the output grain and date boundaries without guessing. For more practice, see the SQL window-functions guide.
Build an auditable path from question to dataset
A useful SQL analysis has a defined population, a stable row grain, and checks that would expose plausible mistakes. The worked example demonstrates that path: filter purchases, qualify customers at the aggregate grain, and return to purchase rows for a window calculation.
FAQ
Does SQL replace Python or R?
No. SQL is useful for selecting and transforming database data; Python and R can handle subsequent modeling, visualization, and statistical work. Choose the boundary based on the task and verify the handoff.
Why join the qualifying customer IDs back to purchases?
The customer aggregate no longer contains individual purchase rows. Joining unique qualifying IDs back preserves those rows and provides the dates and amounts needed by the window function.
Why specify a window frame explicitly?
It makes the intended calculation visible. Here the frame is the current row and one preceding row, so the result means the last two purchases rather than a cumulative or time-based average.
