SQL for Data Science: Queries Every Data Scientist Must Know

Most of the data a data scientist works with lives in databases, and SQL is how you get it out. You do not need every feature of the language. You need a set of patterns that come up every week, and you need to write them correctly, because a wrong query does not fail; it returns a plausible wrong number. This guide works through those patterns on the Chinook sample database, a small digital music store with customers, invoices, tracks and genres (Chinook database). Every query below was run, and its output is shown.

The examples use SQLite from Python, with pandas to display results. The SQL itself is standard apart from one date function, noted where it appears.

import sqlite3
import urllib.request
import pandas as pd

url = "https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite"
urllib.request.urlretrieve(url, "chinook.sqlite")
con = sqlite3.connect("chinook.sqlite")

def run(sql):
    print(pd.read_sql(sql, con).to_string(index=False))

run("""
SELECT (SELECT COUNT(*) FROM Customer) AS customers, (SELECT COUNT(*) FROM Invoice) AS invoices,
       (SELECT COUNT(*) FROM InvoiceLine) AS invoice_lines, (SELECT COUNT(*) FROM Track) AS tracks""")

 customers  invoices  invoice_lines  tracks
        59       412           2240    3503

Knowing table sizes is the first habit: it tells you what a join should produce and helps you notice when it does not.

1. Join and aggregate

The core analytical query joins tables to bring related facts together, then groups and aggregates. Revenue by genre needs invoice lines, their tracks and the tracks' genres:

run("""
SELECT g.Name AS genre,
       COUNT(*) AS tracks_sold,
       ROUND(SUM(il.UnitPrice * il.Quantity), 2) AS revenue
FROM InvoiceLine il
JOIN Track t ON t.TrackId = il.TrackId
JOIN Genre g ON g.GenreId = t.GenreId
GROUP BY g.Name
ORDER BY revenue DESC
LIMIT 5""")

             genre  tracks_sold  revenue
              Rock          835   826.65
             Latin          386   382.14
             Metal          264   261.36
Alternative & Punk          244   241.56
          TV Shows           47    93.53

Rock dominates. Notice TV Shows: few sales, but each costs more, so it ranks on revenue above genres that sold more units. Always decide whether you are counting rows or summing values before you write the query.

2. The join fan-out trap

This is the most expensive mistake in everyday SQL. Each invoice has one total, but many lines. Sum the totals directly, then sum them after joining to the lines:

run("SELECT ROUND(SUM(Total), 2) AS revenue FROM Invoice")
run("""
SELECT ROUND(SUM(i.Total), 2) AS revenue_after_join
FROM Invoice i
JOIN InvoiceLine il ON il.InvoiceId = i.InvoiceId""")

 revenue
  2328.6
 revenue_after_join
           20848.62

The join repeats each invoice's total once per line, inflating revenue about ninefold. Nothing errors; the number is simply wrong. The rule: know the grain of each table, meaning what one row represents, and aggregate at the right grain before joining, or sum only columns that belong to the finest table in the join. Checking a joined total against the unjoined one, as here, catches the problem in seconds.

3. Find what is missing: the anti-join

"Which tracks have never been sold?" is a question about absence. A LEFT JOIN keeps every track and fills unmatched sales with NULL, so filtering for NULL finds them:

run("""
SELECT COUNT(*) AS tracks_never_sold
FROM Track t
LEFT JOIN InvoiceLine il ON il.TrackId = t.TrackId
WHERE il.InvoiceLineId IS NULL""")

 tracks_never_sold
              1519

Of 3,503 tracks, 1,519 have never sold. The same pattern finds customers without orders, products without stock records, or users who signed up but never logged in. NOT EXISTS expresses the same idea and is often clearer.

4. CTEs and window functions: running totals and change

A common table expression, written with WITH, names an intermediate result so a complex query reads in steps (SQLite: WITH clause). Window functions then compute across rows without collapsing them, as GROUP BY would (PostgreSQL: window functions):

run("""
WITH yearly AS (
    SELECT strftime('%Y', InvoiceDate) AS year, ROUND(SUM(Total), 2) AS revenue
    FROM Invoice
    GROUP BY year
)
SELECT year, revenue,
       ROUND(SUM(revenue) OVER (ORDER BY year), 2) AS running_total,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY year), 2) AS change
FROM yearly""")

year  revenue  running_total  change
2009   449.46         449.46     NaN
2010   481.45         930.91   31.99
2011   469.58        1400.49  -11.87
2012   477.53        1878.02    7.95
2013   450.58        2328.60  -26.95

SUM(...) OVER (ORDER BY year) gives a running total; LAG fetches the previous row's value, so year-on-year change is one expression. The first year has no previous row, hence the missing value. Date functions differ between databases: strftime is SQLite's; PostgreSQL uses date_trunc or EXTRACT.

5. Top-N per group

"The top two genres in each country" defeats a plain ORDER BY ... LIMIT, which ranks across everything. ROW_NUMBER() with PARTITION BY restarts the ranking in each group:

run("""
WITH genre_country AS (
    SELECT i.BillingCountry AS country, g.Name AS genre, SUM(il.UnitPrice * il.Quantity) AS revenue
    FROM Invoice i
    JOIN InvoiceLine il ON il.InvoiceId = i.InvoiceId
    JOIN Track t ON t.TrackId = il.TrackId
    JOIN Genre g ON g.GenreId = t.GenreId
    GROUP BY country, genre
),
ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY country ORDER BY revenue DESC) AS rank_in_country
    FROM genre_country
)
SELECT country, rank_in_country, genre, ROUND(revenue, 2) AS revenue
FROM ranked
WHERE rank_in_country <= 2 AND country IN ('USA', 'Brazil', 'Canada')
ORDER BY country, rank_in_country""")

country  rank_in_country genre  revenue
 Brazil                1  Rock    80.19
 Brazil                2 Latin    52.47
 Canada                1  Rock   105.93
 Canada                2 Latin    59.40
    USA                1  Rock   155.43
    USA                2 Latin    90.09

The two CTEs read like a plan: first aggregate to one row per country and genre, then rank within each country. Use RANK() instead of ROW_NUMBER() if ties should share a position. This pattern also gives each customer's latest order, each product's best month, and the first event per user.

6. Counting correctly: NULLs and HAVING

COUNT(*) counts rows; COUNT(column) counts only non-NULL values (SQLite: aggregate functions). And HAVING filters groups after aggregation, where WHERE filters rows before it:

run("SELECT COUNT(*) AS customers, COUNT(Company) AS with_company FROM Customer")
run("""
SELECT BillingCountry AS country,
       COUNT(DISTINCT CustomerId) AS customers,
       ROUND(SUM(Total) / COUNT(DISTINCT CustomerId), 2) AS revenue_per_customer
FROM Invoice
GROUP BY country
HAVING COUNT(DISTINCT CustomerId) >= 5
ORDER BY revenue_per_customer DESC""")

 customers  with_company
        59            10
country  customers  revenue_per_customer
    USA         13                 40.24
 France          5                 39.02
 Brazil          5                 38.02
 Canada          8                 37.99

Only 10 of 59 customers have a company recorded; averaging or filtering on that column silently uses a sixth of the data. The HAVING clause keeps only countries with at least five customers, so a per-customer average is not based on one or two people, a small-sample guard worth building into any ranking of groups.

Building a feature table for a model

These patterns combine naturally into the query data scientists write before most modelling projects: one row per entity, such as a customer, with columns describing its history up to a cut-off date. A typical customer table for a churn model would use a CTE to restrict invoices to those before the cut-off, GROUP BY customer to compute the number of orders, total and average spend and the date of the last purchase, window functions for the gap between consecutive orders, and a LEFT JOIN back to the customer table so that customers with no orders still appear, with zeros rather than disappearing. The label, whether the customer bought again after the cut-off, comes from a separate query on later invoices. Keeping features strictly before the cut-off and the label strictly after it is what stops future information leaking into the model.

Habits that prevent wrong answers

• State the grain of every table and every result: one row per what?
• Reconcile totals after joins against the source table.
• Check row counts at each step of a complex query; CTEs make this easy.
• Handle NULLs explicitly in filters, counts and averages.
• Guard small groups with HAVING or by showing the counts next to averages.
• Keep queries readable with CTEs, clear aliases and one idea per step, so others can review them.

Once the query returns the right, compact result, analysis often continues in Python; pandas vs SQL covers where to draw that line.

Joins, aggregations, subqueries and data extraction make up the SQL for data science module of the Data Science course, and the SQL Server course covers joins, NULL handling and aggregation in depth.

Sources

• Chinook Database (GitHub) — Luis Rocha
• The WITH clause (SQLite documentation) — SQLite
• Window Functions (PostgreSQL tutorial) — PostgreSQL
• Built-in aggregate functions (SQLite documentation) — SQLite