Advanced Reading

Cheatsheet: Advanced Reading

One page for Topic 3 — window functions, nested logic, and date traps.

Window functions: calculation without collapse

A window function computes across a set of rows and writes the answer onto every row — the row count never changes.

Inside OVER (...)

Ranking and ties

Ana 500, Ben 500, Cara 450:

"Top 3" via RANK() <= 3 can return 4+ rows on a tie; via ROW_NUMBER() <= 3 returns exactly 3 but may drop a tied performer. Neither is wrong — know which choice the author made.

GROUP BY + window in one query

SELECT region,
  SUM(revenue) AS region_revenue,
  ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 1) AS pct_of_company
FROM sales
GROUP BY region

Grouping runs first; windows run on the grouped result. SUM(SUM(revenue)) OVER () looks like a typo — it's the standard %-of-total pattern.

The ROW_NUMBER-in-CTE pattern

You cannot filter a window function in WHERE — WHERE runs before windows are computed (WHERE rn = 1 errors). The legitimate pattern:

WITH ranked AS (
  SELECT customer_id, order_total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1   -- most recent order per customer

Some warehouses (Snowflake, BigQuery, DuckDB) offer QUALIFY as a shortcut; PostgreSQL and SQLite do not. AI-generated SQL sometimes emits the broken WHERE version or QUALIFY against a database that lacks it.

Reading nested logic

NOT IN + NULL: the detonation

WHERE customer_id NOT IN (SELECT customer_id FROM orders)

If even one row of the subquery is NULL, this returns zero rows — not wrong rows, zero. NOT IN (1, 2, NULL) requires "not equal to NULL," which is unknown, so no row ever passes. "Zero customers without orders" sounds plausible, so nobody questions it.

Fixes: NOT EXISTS (handles NULLs correctly) or WHERE customer_id IS NOT NULL inside the subquery. Plain IN degrades gently — it's specifically the negated version that detonates. Treat any NOT IN (SELECT ...) as suspect unless the subquery column is guaranteed NULL-free.

SQLite strftime cookbook

SQLite has no DATE_TRUNC; strftime does the bucketing.

Note: strftime returns strings — fine for grouping and sorting (ISO format sorts correctly), but they're not dates anymore. Week conventions differ across systems (Postgres 'week' truncates to Monday; many BI tools start Sunday) — same data, different weekly numbers.

Half-open date ranges beat BETWEEN

The professional filter for "all of June" on a timestamp column:

WHERE created_at >= '2026-06-01' AND created_at < '2026-07-01'   -- [start, next_start)

BETWEEN reads like natural English, so AI reaches for it constantly — it doesn't know your column is a timestamp. Treat every BETWEEN on a timestamp as a bug until proven otherwise.

Also: timestamps are usually stored UTC. Bucketing in UTC vs local time (AT TIME ZONE ...) moves late-evening events across day and month boundaries — the usual cause of small, stubborn dashboard mismatches at month-end.

Cohorts: first event, then join back

A cohort groups users by when they started, then follows each group forward:

WITH first_activity AS (
  SELECT customer_id, MIN(created_at) AS first_order_at
  FROM orders
  GROUP BY 1
)
SELECT strftime('%Y-%m', f.first_order_at) AS cohort_month,
       strftime('%Y-%m', o.created_at)     AS activity_month,
       SUM(o.order_total)                  AS revenue
FROM first_activity f
JOIN orders o ON o.customer_id = f.customer_id
GROUP BY 1, 2

The MIN() is the defining move: one cohort per customer, forever. Then join back to the events table to follow the cohort over time.

Presenting one as the other is how a company convinces itself acquisition is improving when it's just counting old customers' repeat purchases.

Traps

• Filtering a window function in WHERE — it doesn't exist yet; wrap in a CTE and filter the outer query.
• RANK() where ROW_NUMBER() was needed (or vice versa) — ties silently duplicate or drop rows.
• Summing a windowed column downstream — the partition total repeats on every row; you're adding it once per row.
• NOT IN (SELECT ...) where the subquery can contain NULL — zero rows, no error. Use NOT EXISTS.
• = (SELECT ...) that returns one row by luck, not by structure — passes testing, blows up in production.
• Misreading a correlated subquery as independent — the result grain flips from "one row" to "one per group."
• BETWEEN with a date-string endpoint on a timestamp — the last day's afternoon vanishes.
• Mixed timezone bucketing across queries — self-consistent, mutually irreconcilable.
• Month-over-month comparison that includes the current partial month — the in-progress month always "crashes."
• Cohort defined on the wrong event — signup vs first purchase are different populations; the defining event must be stated.