Joins, aggregations and window functions

The Wednesday session on October 14 starts with a follow-up from Lena. The September list was useful, she says, but she now wants three more things: sales by product category for each region, each store's rank within its region, and a running total of sales through the month so managers can see whether they are on pace. Each request needs a different SQL tool. Category sales need a join and an aggregation; ranks and running totals need window functions.

Joins: bringing tables together

Birchline's sales table records a SKU and a store ID, but not the product category or the store's region. Those live in reference tables. A join combines rows from two tables where a condition is true, usually where key columns match. The type of join decides what happens to rows that have no match on the other side.

The choice between inner and left joins is a decision about what the report should do with incomplete data, and it should be made deliberately. An inner join to the product table silently drops sales of any SKU that is missing from the catalog, so totals shrink without warning. A left join keeps those sales with a NULL category, and the report can show them as "Uncategorized" with COALESCE. Ravi's rule: use a left join from the fact table to a reference table unless you are certain every key matches, and check the count of NULLs afterward.

The fan-out trap

The most dangerous join mistake does not cause an error. It makes totals too large. If the right-hand table has more than one row for a key, each left row is repeated once for every match, and any SUM over the left table's columns is multiplied. This is called fan-out.

Mateo meets it straight away. The product table he joins to has one row per SKU, but the promotions table has one row per SKU per promotion, and three tent SKUs were in two September promotions each. Joining sales to promotions doubles every sale of those tents, and the category total for camping jumps by tens of thousands of dollars. The fix is to know the grain of each table, meaning what one row represents, and to aggregate the many-rows side to the join key before joining, or to join to a version with one row per key.

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE sales (sku TEXT, net_sales REAL);
INSERT INTO sales VALUES ('TNT-2P', 300), ('TNT-2P', 300), ('STK-AL', 20), ('TNT-4P', 500);
CREATE TABLE promos (sku TEXT, promo TEXT);
INSERT INTO promos VALUES ('TNT-2P', 'Long Weekend'), ('TNT-2P', 'Fall Camp'), ('TNT-4P', 'Fall Camp');
""")
q = lambda s: db.execute(s).fetchall()
print("true total:", q("SELECT SUM(net_sales) FROM sales"))
print("after joining promos (fan-out):",
      q("SELECT SUM(s.net_sales) FROM sales s LEFT JOIN promos p USING (sku)"))
print("joined to one row per SKU:",
      q("""SELECT SUM(s.net_sales) FROM sales s
           LEFT JOIN (SELECT sku, COUNT(*) AS n_promos FROM promos GROUP BY sku) p USING (sku)"""))

true total: [(1120.0,)]
after joining promos (fan-out): [(1720.0,)]
joined to one row per SKU: [(1120.0,)]

A quick defensive habit catches fan-out: count the rows before and after a join. If a left join to a reference table returns more rows than the fact table had, some key on the right is duplicated.

Aggregations and grouping

Aggregate functions such as SUM, COUNT, AVG, MIN and MAX reduce many rows to one value per group. Every column in the SELECT list must either be inside an aggregate or appear in GROUP BY. BigQuery also supports GROUP BY ROLLUP, which adds subtotal rows, and GROUPING SETS for several groupings in one pass. Lena's category report uses ROLLUP to give each region's categories, a total for each region, and a grand total in one result.

SELECT
  st.region,
  COALESCE(pr.category, "Uncategorized")       AS category,
  SUM(s.net_sales)                             AS net_sales,
  COUNT(DISTINCT s.sale_id)                    AS lines
FROM `bl-data-prod.curated.sales`        AS s
LEFT JOIN `bl-data-prod.reference.products` AS pr USING (sku)
JOIN `bl-data-prod.reference.stores`     AS st USING (store_id)
WHERE s.sale_date BETWEEN "2026-09-01" AND "2026-09-30"
  AND s.channel = "store"
GROUP BY ROLLUP (st.region, category)
ORDER BY st.region, category;
-- Rows with a NULL category are region subtotals; the row with NULL region is the grand total.

ROLLUP produces NULLs in the grouping columns for its subtotal rows, which can be confused with real NULLs. The GROUPING() function returns 1 for a subtotal row and 0 otherwise, so a report can label subtotals clearly.

Window functions

A window function computes a value for each row using a set of related rows, called the window, without collapsing the rows into one. That is the difference from GROUP BY. A window is described with OVER, which can contain PARTITION BY to split rows into groups, ORDER BY to order rows within each group, and a frame to say which rows around the current one to include.

The three ranking functions differ only in how they treat ties. ROW_NUMBER gives every row a different number, breaking ties arbitrarily unless the ORDER BY is complete. RANK gives tied rows the same number and then skips, so two stores tied for first are followed by third. DENSE_RANK gives tied rows the same number without skipping, so the next store is second. For a league table shown to managers, RANK is usually what people expect.

Lena's pacing view uses a running total within each store, ordered by date, and compares it with the store's monthly target. The rank within region uses RANK partitioned by region. Both are computed in one query, and QUALIFY could filter the result to, say, the top three stores in each region without another layer of nesting.

WITH daily AS (
  SELECT store_id, sale_date, SUM(net_sales) AS net_sales
  FROM `bl-data-prod.curated.sales`
  WHERE sale_date BETWEEN "2026-10-01" AND "2026-10-13" AND channel = "store"
  GROUP BY store_id, sale_date
)
SELECT
  d.store_id, st.region, d.sale_date, d.net_sales,
  SUM(d.net_sales) OVER (PARTITION BY d.store_id ORDER BY d.sale_date)          AS month_to_date,
  AVG(d.net_sales) OVER (PARTITION BY d.store_id ORDER BY d.sale_date
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)              AS avg_7_day,
  RANK() OVER (PARTITION BY st.region, d.sale_date ORDER BY d.net_sales DESC)   AS rank_in_region
FROM daily AS d
JOIN `bl-data-prod.reference.stores` AS st USING (store_id);

Window functions run after WHERE and GROUP BY, which is why the query aggregates to daily totals in a CTE first. A window over raw sale lines would rank individual purchases, not stores.

Checking a window by hand

New window functions should be checked against a small case worked out by hand. Mateo uses SQLite, which supports the same window syntax, to confirm the running total and the ranks on a few rows before trusting the BigQuery result on forty stores.

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE d (store TEXT, day INT, sales REAL)")
rows = [("A", 1, 4.2), ("A", 2, 5.1), ("A", 3, 3.8), ("A", 4, 6.4),
        ("B", 1, 5.0), ("B", 2, 5.1), ("B", 3, 4.0), ("B", 4, 6.4)]
db.executemany("INSERT INTO d VALUES (?, ?, ?)", rows)
sql = """
SELECT store, day, sales,
       ROUND(SUM(sales) OVER (PARTITION BY store ORDER BY day), 1) AS running,
       RANK()       OVER (PARTITION BY day ORDER BY sales DESC)    AS rnk,
       DENSE_RANK() OVER (PARTITION BY day ORDER BY sales DESC)    AS dense,
       ROUND(sales - LAG(sales) OVER (PARTITION BY store ORDER BY day), 1) AS change
FROM d ORDER BY store, day
"""
for r in db.execute(sql):
    print(r)

('A', 1, 4.2, 4.2, 2, 2, None)
('A', 2, 5.1, 9.3, 1, 1, 0.9)
('A', 3, 3.8, 13.1, 2, 2, -1.3)
('A', 4, 6.4, 19.5, 1, 1, 2.6)
('B', 1, 5.0, 5.0, 1, 1, None)
('B', 2, 5.1, 10.1, 1, 1, 0.1)
('B', 3, 4.0, 14.1, 1, 1, -1.1)
('B', 4, 6.4, 20.5, 1, 1, 2.4)

On day 4 both stores sold 6.4, so both rank first. With only two stores, RANK and DENSE_RANK give the same result; with a third store below them, RANK would give it 3 and DENSE_RANK would give it 2. The first change value for each store is NULL, because LAG has no previous row to look at. Explaining these details in a report footnote avoids questions later.

One last check before Lena sees the category report: Mateo compares the grand total from the ROLLUP query with the total from v_store_daily for the same month. They agree to the dollar, which confirms that the left join kept every sale and that no join fanned out. This single reconciliation catches most join mistakes and takes a minute.