SQL for analysis

Questions in steps: subqueries and WITH

A subquery is a query inside another. In WHERE price > (SELECT AVG(price) FROM catalog) the inner query runs first and gives one number. A subquery can also stand in for a table in FROM, or give a list for IN.

Big questions are easier as named steps. WITH (a common table expression, or CTE) names a query so the next step can use it like a table. The first step here cleans the data, the duplicates and test orders, so nothing after it has to remember to:

import sqlite3
import pandas as pd

db = sqlite3.connect(":memory:")
for t in ["orders", "catalog"]:
    pd.read_csv(f"https://api.gradient.test/v1/datasets/{t}.csv").to_sql(t, db, index=False)

def q(sql, params=()):
    return pd.read_sql_query(sql, db, params=params)

print(q("""
WITH clean AS (
    SELECT DISTINCT * FROM orders WHERE sku <> 'TEST-000'
),
by_store AS (
    SELECT store, SUM(qty * unit_price) AS revenue FROM clean GROUP BY store
)
SELECT store, ROUND(revenue) AS revenue
FROM by_store
WHERE revenue > (SELECT AVG(revenue) FROM by_store)
"""))
print(q("SELECT COUNT(*) AS raw_lines FROM orders"), q("WITH clean AS (SELECT DISTINCT * FROM orders WHERE sku <> 'TEST-000') SELECT COUNT(*) AS clean_lines FROM clean"))

The answer looks small, one store, until you check the numbers: the web shop sells more than all three stores together, so only it beats the average. That's a finding worth a second query before anyone acts on it.

import sqlite3
import pandas as pd

db = sqlite3.connect(":memory:")
for t in ["orders", "catalog"]:
    pd.read_csv(f"https://api.gradient.test/v1/datasets/{t}.csv").to_sql(t, db, index=False)

SQL = """
WITH clean AS (SELECT DISTINCT * FROM orders WHERE sku <> 'TEST-000')
SELECT c.category, SUM(o.qty) AS units
FROM clean o JOIN catalog c ON c.sku = o.sku
GROUP BY c.category
ORDER BY units DESC
"""
result = pd.read_sql_query(SQL, db)
print(result)

The rows that aren't there: outer joins

An ordinary (inner) join keeps only rows that match on both sides. A LEFT JOIN keeps every row of the left table, filling the right side's columns with NULL where nothing matches. That makes it the tool for questions about absence: which products haven't sold, which customers never ordered. The pattern, called an anti-join, is a left join followed by WHERE right_side.key IS NULL.

The shop launched a new product yesterday. It's in the catalog but has no orders yet:

import sqlite3
import pandas as pd

db = sqlite3.connect(":memory:")
for t in ["orders", "catalog"]:
    pd.read_csv(f"https://api.gradient.test/v1/datasets/{t}.csv").to_sql(t, db, index=False)
db.execute("INSERT INTO catalog (sku, name, category, price) VALUES ('LA-131', 'Glimmer lantern', 'lighting', 58)")

def q(sql, params=()):
    return pd.read_sql_query(sql, db, params=params)

print(q("""
SELECT c.sku, c.name
FROM catalog c
LEFT JOIN orders o ON o.sku = c.sku
WHERE o.sku IS NULL
"""))

# The same question with NOT IN works here, but watch what one NULL does:
db.execute("CREATE TABLE returns (sku TEXT)")
db.executemany("INSERT INTO returns VALUES (?)", [("TE-101",), (None,)])
print(q("SELECT COUNT(*) AS not_returned FROM catalog WHERE sku NOT IN (SELECT sku FROM returns)"))

The last query says no product at all was never returned, which is plainly wrong. x NOT IN (a, NULL) asks "is x different from a, and from an unknown value?", and the honest answer to the second part is "unknown", so the whole condition is never true. Use a left join with IS NULL, or NOT EXISTS, whenever the list could contain a NULL.

Window functions: comparing across rows

GROUP BY collapses rows. A window function calculates over a set of rows but keeps every row, adding the result beside it. You write it with OVER (...): PARTITION BY says which rows belong together (like a group), and ORDER BY sets the order within them.

• RANK() OVER (PARTITION BY category ORDER BY revenue DESC) numbers products within each category, 1 for the best.
• SUM(revenue) OVER (ORDER BY month) is a running total: each row adds itself to everything before it.
• LAG(revenue) OVER (ORDER BY month) is the previous row's value, for month-on-month change.

import sqlite3
import pandas as pd

db = sqlite3.connect(":memory:")
for t in ["orders", "catalog"]:
    pd.read_csv(f"https://api.gradient.test/v1/datasets/{t}.csv").to_sql(t, db, index=False)

def q(sql, params=()):
    return pd.read_sql_query(sql, db, params=params)

CLEAN = "WITH clean AS (SELECT DISTINCT * FROM orders WHERE sku <> 'TEST-000')"
print(q(CLEAN + """,
revenue AS (
    SELECT c.category, c.name, SUM(o.qty * o.unit_price) AS revenue
    FROM clean o JOIN catalog c ON c.sku = o.sku
    GROUP BY c.sku
)
SELECT * FROM (
    SELECT category, name, ROUND(revenue) AS revenue,
           RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank_in_category
    FROM revenue
)
WHERE rank_in_category <= 2
"""))

monthly = q(CLEAN + """,
months AS (SELECT substr(date, 1, 7) AS month, SUM(qty * unit_price) AS revenue FROM clean GROUP BY month)
SELECT month, ROUND(revenue) AS revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month)) AS running_total,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 1) AS change_pct
FROM months
""")
print(monthly.head(6))

The rank has to be filtered in an outer query, because window functions are worked out at the SELECT step, after WHERE has already run. The first month's change is NaN, pandas' name for a missing number: there's no month before it to compare with.

import sqlite3
import pandas as pd

orders = pd.read_csv("https://api.gradient.test/v1/datasets/orders.csv")
db = sqlite3.connect(":memory:")
orders.to_sql("orders", db, index=False)

in_sql = pd.read_sql_query("""
SELECT store, ROUND(SUM(qty * unit_price)) AS member_revenue
FROM (SELECT DISTINCT * FROM orders)
WHERE member = 1 AND sku <> 'TEST-000' AND date >= ?
GROUP BY store ORDER BY member_revenue DESC
""", db, params=("2025-01-01",))

clean = orders.drop_duplicates()
clean = clean[(clean.member == 1) & (clean.sku != "TEST-000") & (clean.date >= "2025-01-01")]
in_pandas = (clean.assign(revenue=clean.qty * clean.unit_price)
             .groupby("store")["revenue"].sum().round().sort_values(ascending=False))

print(in_sql)
print(in_pandas)