Analytical SQL: CTEs and window functions

Plain GROUP BY collapses rows: you get one row per group and lose the detail. Most analytical questions need both at once, such as “each order, alongside the customer’s running total” or “the latest event per user”. Window functions answer those, and CTEs keep the queries readable.

CTEs for readability

A common table expression (CTE) names a subquery with WITH, so a long query reads top to bottom as a series of steps instead of nesting inside out.

WITH paid_orders AS (
  SELECT customer_id, total, created_at
  FROM orders
  WHERE status = 'paid'
),
customer_totals AS (
  SELECT customer_id, SUM(total) AS lifetime_total
  FROM paid_orders
  GROUP BY customer_id
)
SELECT * FROM customer_totals WHERE lifetime_total > 1000;

Give each CTE a name that says what it contains. Engines differ in whether they materialise a CTE or inline it into the outer query; PostgreSQL has inlined simple CTEs since version 12. Treat CTEs as a readability tool and check the plan when performance matters.

Window functions

A window function computes a value over a set of related rows without collapsing them. The OVER clause defines that set:

• PARTITION BY splits rows into groups, like GROUP BY but keeping every row;
• ORDER BY sets the order inside each partition;
• a frame, such as ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, limits which rows in the partition are included.

SELECT
  customer_id,
  created_at,
  total,
  SUM(total) OVER (
    PARTITION BY customer_id ORDER BY created_at
  ) AS running_total,
  LAG(created_at) OVER (
    PARTITION BY customer_id ORDER BY created_at
  ) AS previous_order_at
FROM orders;

LAG reads a value from an earlier row in the partition and LEAD from a later one, which makes gaps between events easy to compute.

Ranking functions

Three functions number rows, and they differ only on ties:

ROW_NUMBER always gives unique numbers, breaking ties arbitrarily unless the ORDER BY is fully deterministic.

Top-N per group and deduplication

Both follow the same pattern: number the rows in each partition, then keep the ones you want.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY updated_at DESC, id DESC
    ) AS rn
  FROM user_events
)
SELECT * FROM ranked WHERE rn = 1;

This keeps the latest row per user, which is the standard way to deduplicate data loaded with at-least-once delivery. Change the filter to rn <= 3 for the top three per group. Note the tie-breaker id DESC: without it, two rows with the same updated_at could swap between runs.

Common mistakes

• Filtering on a window result in WHERE. Window functions are evaluated after WHERE, GROUP BY and HAVING, so WHERE ROW_NUMBER() OVER (...) = 1 is an error. Wrap the query in a CTE or subquery, as above. Snowflake, BigQuery, DuckDB and Databricks support QUALIFY, which filters on window results directly; PostgreSQL, MySQL and SQL Server do not.
• Forgetting the default frame. With ORDER BY and no explicit frame, the frame runs from the start of the partition to the current row, including rows tied with it. So rows with equal timestamps get the same running total, and LAST_VALUE returns the current row rather than the last one in the partition. Write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when you mean the whole partition.
• Non-deterministic ordering. Ranking over a column with ties gives results that can change between runs. Add a unique column as a tie-breaker.

Habits

• Build long queries as named CTE steps and check each one on its own.
• Reach for ROW_NUMBER with a deterministic ORDER BY for deduplication.
• Write the frame explicitly whenever the result depends on it.
• Filter on window results in an outer query, or with QUALIFY where your warehouse supports it.