SQL for Data Analysts: The Queries You Need Daily

Most of an analyst's SQL is not clever. It answers the same kinds of questions every week: how many, how much, compared with when, and for whom. What separates dependable analysts is less the syntax they know than the checks they run and the patterns they reach for without thinking. This guide covers seven of those patterns, each run on a real sample database, with the mistakes each one prevents.

The examples use Chinook, a sample database for a digital music store with invoices, invoice lines, customers, tracks and genres (Chinook on GitHub), queried through SQLite from Python:

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))

1. Profile a table before trusting it

Before answering any question, find out what the table actually contains: how many rows, the date range, and where values are missing.

run("""
SELECT COUNT(*) AS invoices, COUNT(DISTINCT CustomerId) AS customers,
  MIN(InvoiceDate) AS first_date, MAX(InvoiceDate) AS last_date,
  SUM(BillingState IS NULL) AS missing_state, SUM(BillingPostalCode IS NULL) AS missing_postcode
FROM Invoice""")

 invoices  customers          first_date           last_date  missing_state  missing_postcode
      412         59 2009-01-01 00:00:00 2013-12-22 00:00:00            202                28

Five years of data, 412 invoices, 59 customers. Almost half the invoices have no billing state, which is not an error: many countries have no states. Knowing that before you write WHERE BillingState = ... prevents a report that silently drops half the business. In SQL, a comparison with a missing value is never true, so rows with NULLs vanish from filters without warning.

2. Reconcile totals before reporting them

When the same number can be computed two ways, compute both. Here, each invoice's stored total should equal the sum of its lines:

run("""
WITH line_totals AS (
  SELECT InvoiceId, SUM(UnitPrice * Quantity) AS lines_total
  FROM InvoiceLine GROUP BY InvoiceId
)
SELECT COUNT(*) AS invoices_checked,
  SUM(ABS(i.Total - l.lines_total) > 0.005) AS mismatches,
  ROUND(SUM(i.Total), 2) AS header_total, ROUND(SUM(l.lines_total), 2) AS lines_total
FROM Invoice i JOIN line_totals l ON l.InvoiceId = i.InvoiceId""")

 invoices_checked  mismatches  header_total  lines_total
              412           0        2328.6       2328.6

No mismatches, so either table can be used for revenue. In real systems this check regularly finds discounts, refunds, taxes or shipping recorded in one place but not the other; finding out before a report goes out is far better than after. The small tolerance avoids false alarms from floating-point rounding.

3. Report by month without losing empty months

A GROUP BY only returns groups that exist. For India, which has few invoices, a monthly revenue query returns just three rows for 2011, and a chart built from it would join the points as if the months between had never happened. A calendar built with a recursive common table expression (SQLite: WITH clause) fixes that:

run("""
WITH RECURSIVE months(month_start) AS (
  SELECT '2011-01-01'
  UNION ALL
  SELECT date(month_start, '+1 month') FROM months WHERE month_start < '2011-12-01'
)
SELECT strftime('%Y-%m', m.month_start) AS month, COUNT(i.InvoiceId) AS invoices,
  ROUND(COALESCE(SUM(i.Total), 0), 2) AS revenue
FROM months m
LEFT JOIN Invoice i
  ON strftime('%Y-%m', i.InvoiceDate) = strftime('%Y-%m', m.month_start)
  AND i.BillingCountry = 'India'
GROUP BY m.month_start ORDER BY m.month_start""")

  month  invoices  revenue
2011-01         0     0.00
2011-02         0     0.00
2011-03         1     8.91
2011-04         0     0.00
2011-05         0     0.00
2011-06         0     0.00
2011-07         0     0.00
2011-08         1     1.98
2011-09         1    13.86
2011-10         0     0.00
2011-11         0     0.00
2011-12         0     0.00

Two details matter. The country filter sits in the join condition, not in a WHERE clause; a WHERE on the invoice table would remove the empty months again. And COUNT(i.InvoiceId) counts only matched invoices, where COUNT(*) would count every calendar row as one. The month arithmetic uses SQLite's date modifiers and strftime (SQLite: date and time functions); other databases have equivalents such as DATE_TRUNC.

4. Share of total with a window function

"What share of revenue does each genre bring?" needs each group's value and the grand total in the same row. A window function over the whole result provides the total without a second query:

run("""
SELECT g.Name AS genre, ROUND(SUM(il.UnitPrice * il.Quantity), 2) AS revenue,
  ROUND(100.0 * SUM(il.UnitPrice * il.Quantity)
        / SUM(SUM(il.UnitPrice * il.Quantity)) OVER (), 1) AS pct_of_total
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  revenue  pct_of_total
              Rock   826.65          35.5
             Latin   382.14          16.4
             Metal   261.36          11.2
Alternative & Punk   241.56          10.4
          TV Shows    93.53           4.0

Rock brings over a third of revenue. The 100.0 matters: in many databases, dividing one integer by another discards the fraction, so a share computed from integer counts can come out as zero.

5. Period-over-period change with LAG

LAG returns a value from the previous row in the window (SQLite: window functions), which turns a yearly total into a year-over-year comparison:

run("""
WITH yearly AS (
  SELECT strftime('%Y', InvoiceDate) AS year, ROUND(SUM(Total), 2) AS revenue
  FROM Invoice GROUP BY year
)
SELECT year, revenue, LAG(revenue) OVER (ORDER BY year) AS previous_year,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY year))
        / LAG(revenue) OVER (ORDER BY year), 1) AS pct_change
FROM yearly ORDER BY year""")

year  revenue  previous_year  pct_change
2009   449.46            NaN         NaN
2010   481.45         449.46         7.1
2011   469.58         481.45        -2.5
2012   477.53         469.58         1.7
2013   450.58         477.53        -5.6

Revenue has been flat for five years, within a few percent either way. The first year has no previous value, shown as missing, which is correct: do not replace it with zero, which would imply a 100% increase.

6. New versus returning customers

Flat revenue raises the next question: is the business still winning customers? Tag each invoice with the year of the customer's first purchase, then count:

run("""
WITH tagged AS (
  SELECT CustomerId, strftime('%Y', InvoiceDate) AS year,
    strftime('%Y', MIN(InvoiceDate) OVER (PARTITION BY CustomerId)) AS first_year
  FROM Invoice
)
SELECT year, COUNT(DISTINCT CustomerId) AS active_customers,
  COUNT(DISTINCT CASE WHEN year = first_year THEN CustomerId END) AS new_customers
FROM tagged GROUP BY year ORDER BY year""")

year  active_customers  new_customers
2009                46             46
2010                46             13
2011                47              0
2012                47              0
2013                46              0

This is the finding the revenue trend hid: the store gained no new customers after 2010. Every sale in the last three years came from customers first won in 2009 and 2010. Flat revenue from a closed customer base is a very different situation from flat revenue with healthy acquisition. A sample database is small and artificial, but the same query on real data is one of the most useful an analyst can run. Vanity vs actionable metrics shows the same pattern in real retail data.

7. The latest record for each customer

"Show each customer's most recent order" is a daily request, and a plain GROUP BY cannot return the other columns of that row reliably. Number the rows within each customer with ROW_NUMBER and keep the first:

run("""
WITH ranked AS (
  SELECT c.CustomerId, c.Country, i.InvoiceDate, i.Total,
    ROW_NUMBER() OVER (PARTITION BY c.CustomerId
                       ORDER BY i.InvoiceDate DESC, i.InvoiceId DESC) AS rn
  FROM Customer c JOIN Invoice i ON i.CustomerId = c.CustomerId
)
SELECT CustomerId, Country, InvoiceDate AS last_invoice, Total AS last_total
FROM ranked WHERE rn = 1 ORDER BY last_invoice LIMIT 5""")

 CustomerId Country        last_invoice  last_total
         59   India 2012-05-30 00:00:00        8.91
         38 Germany 2012-06-30 00:00:00        8.91
          2 Germany 2012-07-13 00:00:00        0.99
         17     USA 2012-07-31 00:00:00       10.91
         40  France 2012-08-13 00:00:00        0.99

Sorted oldest first, the query becomes a list of lapsed customers: these five have not bought for well over a year before the data ends. The second sort key, the invoice ID, breaks ties when a customer has two invoices on the same date, so the result is the same every time the query runs. The same pattern also removes duplicates, keeping one row per key.

Habits that keep everyday SQL correct

• Check row counts after every join. If a join multiplies rows unexpectedly, totals inflate; SQL for data science walks through this fan-out trap.
• Choose the right count. COUNT(*) counts rows, COUNT(column) counts rows where the column is not missing, and COUNT(DISTINCT column) counts unique values.
• Be explicit with date boundaries. InvoiceDate < '2012-01-01' is safer than <= '2011-12-31' when timestamps include times.
• Save and comment queries that feed reports, so the next person can see what was included and why.

For analysts moving from spreadsheets, from Excel to SQL maps SUMIFS, lookups and pivot tables to their SQL equivalents.

Window functions and CTEs are covered in the SQL Server course, and SQL for analysis is a module of the Data Analytics course.

Sources

• Chinook sample database (GitHub) — GitHub
• Window functions (SQLite documentation) — SQLite
• The WITH clause (SQLite documentation) — SQLite
• Date and time functions (SQLite documentation) — SQLite