Chapter 43: Advanced SQL: Window Functions & CTEs

What Is a CTE?

A Common Table Expression (CTE) is a named, temporary result set defined at the top of a query using the WITH keyword. The result set is scoped to the single statement that follows. CTEs were standardized in SQL:1999 and are supported by every major analytical database.

The motivating problem is readability. Consider computing, for each product category, the top-3 products by revenue. One approach nests three levels of subqueries:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM (
    SELECT category, product_id, SUM(amount) AS revenue
    FROM orders
    GROUP BY category, product_id
  ) agg
) ranked
WHERE rn <= 3;

The same logic with a CTE is immediately legible:

WITH revenue_by_product AS (
  SELECT category, product_id, SUM(amount) AS revenue
  FROM orders
  GROUP BY category, product_id
),
ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM revenue_by_product
)
SELECT *
FROM ranked
WHERE rn <= 3;

Each CTE block produces a named result that subsequent blocks — and the final query — can reference. The chain reads like a recipe: first we compute revenue, then we rank, then we filter.

Syntax and Scoping Rules

The full grammar is:

WITH
  cte1 AS (SELECT ...),
  cte2 AS (SELECT ... FROM cte1 ...),
  ...
FINAL_QUERY;

Key rules:

• A CTE can reference any CTE defined before it in the same WITH clause.
• CTEs are not persistent; they exist only for the duration of the statement.
• In PostgreSQL and DuckDB, non-recursive CTEs are inlined by default — the optimizer folds them back into the main query for planning. To force materialization (useful when a CTE is expensive and referenced twice), add the MATERIALIZED keyword: WITH expensive AS MATERIALIZED (...). BigQuery always materializes CTEs.
• CTEs can wrap INSERT, UPDATE, or DELETE statements (writable CTEs), which enables multi-step data transformations in a single statement.

Why CTEs Matter for Analytical Queries

Analytical queries frequently need to compute intermediate aggregates, then filter or rank those aggregates, and sometimes join them back to raw data. Nested subqueries force you to read inside-out. CTEs let you write top-to-bottom, matching the order in which you reason about the problem.

A second practical benefit: debugging. You can run each CTE block independently (just copy it into a standalone SELECT) to verify intermediate results before composing them.

A third benefit is deduplication of logic. If the same subquery appears in multiple places in a complex JOIN, extracting it into a CTE avoids repeating code and ensures consistent results.

Example: Multi-Step Customer Segmentation

WITH
-- Step 1: compute lifetime value per customer
customer_ltv AS (
  SELECT customer_id,
         MIN(order_date)         AS first_order,
         MAX(order_date)         AS last_order,
         COUNT(*)                AS order_count,
         SUM(amount)             AS ltv
  FROM orders
  GROUP BY customer_id
),
-- Step 2: segment customers
segmented AS (
  SELECT *,
         CASE
           WHEN ltv >= 1000 THEN 'high'
           WHEN ltv >= 250  THEN 'medium'
           ELSE                  'low'
         END AS segment
  FROM customer_ltv
),
-- Step 3: summarize segment distribution
summary AS (
  SELECT segment,
         COUNT(*)       AS customers,
         AVG(ltv)       AS avg_ltv,
         AVG(order_count) AS avg_orders
  FROM segmented
  GROUP BY segment
)
SELECT * FROM summary
ORDER BY avg_ltv DESC;

Each CTE captures one conceptual step. Any step can be inspected independently, and the final query is four lines long.

Common Pitfalls

• Assuming materialization: in PostgreSQL, referencing a non-recursive CTE twice does not guarantee the optimizer computes it once. Use MATERIALIZED when the CTE is expensive and referenced multiple times.
• Circular references: you cannot have cte<em>a reference cte</em>b which references cte_a in a non-recursive CTE; the database will return an error.
• Naming collisions: if a CTE has the same name as a real table, the CTE takes precedence within that statement — a subtle source of bugs during refactoring.

Why Recursion in SQL?

Relational tables are flat, but real-world data is frequently hierarchical: organizational reporting chains, product bill-of-materials trees, filesystem paths, category taxonomies, and social-graph friendships. Before recursive CTEs, navigating these structures required either application-side loops or denormalized adjacency-list tricks. Recursive CTEs (also called recursive queries) handle these patterns natively.

Anatomy of a Recursive CTE

WITH RECURSIVE cte_name AS (
  -- Anchor member: the base case (non-recursive)
  SELECT ...
  UNION ALL
  -- Recursive member: references cte_name
  SELECT ... FROM cte_name WHERE <termination condition>
)
SELECT * FROM cte_name;

The engine evaluates the query iteratively:

1. Execute the anchor member, producing a seed result set <!--MATHBLOCK0-->.
2. Execute the recursive member with <!--MATHBLOCK1--> substituted for cte<em>name, producing <!--MATHBLOCK2-->.
3. Replace the working table with <!--MATHBLOCK3--> and repeat until the recursive member returns zero rows.
4. Return <!--MATHBLOCK4-->

UNION ALL is standard (duplicates allowed). UNION deduplicates at each step and is occasionally useful to prevent cycles, but it is slower.

Example: Organizational Hierarchy

Assume an employees table:

employees(id, name, manager_id)   -- manager_id is NULL for the CEO

To find all reports (direct and indirect) under a given manager:

WITH RECURSIVE org_tree AS (
  -- Anchor: the starting manager
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE id = 42  -- root manager

  UNION ALL

  -- Recursive: employees whose manager is already in the tree
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth
FROM org_tree
ORDER BY depth, name;

Adding depth is important: it tells you the number of hops from the root and is essential for rendering indented org charts.

Building a Full Path String

A common requirement is to construct a breadcrumb path (e.g., Engineering > Backend > Platform):

WITH RECURSIVE category_path AS (
  SELECT id, name, parent_id,
         name::TEXT AS path
  FROM categories
  WHERE parent_id IS NULL  -- root categories

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         cp.path || ' > ' || c.name
  FROM categories c
  JOIN category_path cp ON c.parent_id = cp.id
)
SELECT id, path
FROM category_path
ORDER BY path;

Cycle Detection and Safety

If your data might contain cycles (e.g., a social graph where A follows B and B follows A), a naive recursive CTE loops forever. PostgreSQL provides two escape hatches:

• CYCLE clause (PostgreSQL 14+, SQL:2016 standard): CYCLE id SET is</em>cycle USING path appends a boolean column that becomes TRUE when a repeated node is detected and halts that branch.
• Manual visited array: accumulate visited IDs in an array and add WHERE NOT (e.id = ANY(visited_ids)) in the recursive member.

WITH RECURSIVE reachable AS (
  SELECT id, ARRAY[id] AS visited
  FROM graph_nodes
  WHERE id = 1

  UNION ALL

  SELECT g.target_id, r.visited || g.target_id
  FROM graph_edges g
  JOIN reachable r ON g.source_id = r.id
  WHERE NOT g.target_id = ANY(r.visited)
)
SELECT DISTINCT id FROM reachable;

Bill-of-Materials Rollup

A classic manufacturing query: given a product tree where each component may have sub-components, compute total leaf-level quantities:

WITH RECURSIVE bom AS (
  SELECT component_id, parent_id, quantity, quantity AS total_qty
  FROM components
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.component_id, c.parent_id,
         c.quantity,
         bom.total_qty * c.quantity   -- multiply down the tree
  FROM components c
  JOIN bom ON c.parent_id = bom.component_id
)
SELECT component_id, SUM(total_qty) AS required_units
FROM bom
WHERE component_id NOT IN (SELECT DISTINCT parent_id FROM components WHERE parent_id IS NOT NULL)
GROUP BY component_id;

Performance Notes

Recursive CTEs are always materialized — the optimizer cannot inline them. For deep hierarchies (thousands of levels) consider:

• Closure table pattern: pre-materialize all ancestor-descendant pairs.
• Nested sets (Celko trees): encode left/right bounds for O(1) subtree queries.
• ltree extension (PostgreSQL): native hierarchical path data type with GiST indexes.

The Window Function Model

A window function computes a value for each row by looking at a set of rows related to the current row — its window — without collapsing those rows into a single output row. This is the fundamental difference from GROUP BY: every input row survives to the output, enriched with the computed value.

The general syntax is:

function_name(args) OVER (
  [PARTITION BY partition_exprs]
  [ORDER BY sort_exprs]
  [frame_clause]
)

Every part is optional, but omitting all three gives a window equal to the entire result set, which is occasionally useful (e.g., SUM(amount) OVER () computes the grand total on every row).

PARTITION BY

PARTITION BY divides rows into independent groups — partitions — and the window function restarts for each partition. It is conceptually similar to GROUP BY, except rows are not collapsed.

SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

Every row gets its own customer<em>total equal to that customer's lifetime order value. Without PARTITION BY, all rows would get the same grand total.

ORDER BY Inside OVER

Adding ORDER BY to the window changes its semantics: by default, when ORDER BY is present and no explicit frame is given, the frame becomes RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — a running aggregate up to and including the current row's value. This is the standard SQL behavior and is the source of much confusion.

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Be careful: if two rows share the same order</em>date, they both get the same running_total equal to the sum through all rows of that date. This is RANGE semantics — "include all peers". To avoid this, switch to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which processes rows physically one at a time.

Frame Clauses

A frame is the subset of the partition that the function sees for the current row. Two frame units exist:

• ROWS: physical row offsets. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the current row plus the two immediately preceding rows — exactly three rows regardless of value ties.
• RANGE: logical value offsets. RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW means all rows whose ORDER BY value falls within seven days of the current row's value. Useful for time-series but requires compatible data types.

Frame bounds:

• UNBOUNDED PRECEDING — from the start of the partition
• N PRECEDING — N rows/values before
• CURRENT ROW
• N FOLLOWING — N rows/values after
• UNBOUNDED FOLLOWING — to the end of the partition

Moving Average Example

SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7day_ma
FROM daily_revenue
ORDER BY date;

This computes a 7-day moving average (current row plus 6 prior rows). Note that the first six rows will average fewer than seven values — the frame simply uses whatever rows are available.

WINDOW Clause: Reusing Window Definitions

When multiple window functions share the same OVER (...) specification, repeating it is verbose and error-prone. SQL provides a WINDOW clause:

SELECT
  date,
  revenue,
  SUM(revenue)   OVER w AS running_total,
  AVG(revenue)   OVER w AS running_avg,
  COUNT(*)       OVER w AS days_elapsed
FROM daily_revenue
WINDOW w AS (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY date;

The named window w is defined once and referenced three times. Changes to the frame apply everywhere automatically.

Execution Order

Window functions are evaluated after WHERE, GROUP BY, and HAVING but before the outer SELECT's DISTINCT and ORDER BY. This has a critical implication: you cannot filter on a window function result in the same query's WHERE clause. You must wrap the query in a subquery or CTE:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM products
)
SELECT * FROM ranked WHERE rn = 1;  -- OK: filter happens after window evaluation

Performance Considerations

The database must sort the data for each distinct window specification. Multiple windows with different ORDER BY columns require multiple sort passes. Strategies to manage cost:

• Minimize the number of distinct window specifications; reuse named windows.
• Pre-filter or pre-aggregate in a CTE before applying windows to reduce row count.
• Ensure the table is clustered or has an index on the PARTITION BY + ORDER BY columns when querying large tables; some engines (e.g., Redshift, BigQuery) can exploit sort keys to skip sort steps.

The Ranking Family

SQL provides four ranking functions. Understanding their differences is essential for correct analytical results.

ROWNUMBER

SELECT
  product_id,
  category,
  revenue,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
FROM product_revenue;

Choose RANK for "top N by value" when you want to respect that ties share a position. Choose DENSERANK when you want contiguous tier labels (e.g., quartile-like groupings without gaps).

NTILE

NTILE(n) divides the partition into n roughly equal buckets and assigns each row a bucket number. If rows do not divide evenly, earlier buckets receive one extra row.

SELECT
  customer_id,
  ltv,
  NTILE(4) OVER (ORDER BY ltv DESC) AS ltv_quartile
FROM customer_ltv;

NTILE is useful for percentile-based segmentation (deciles, quartiles) but note that it assigns a label, not an actual percentile value. For the actual $p$-th percentile value use PERCENTILE<em>CONT(p) WITHIN GROUP (ORDER BY ...), which is an ordered-set aggregate, not a window function.

Practical Pattern: Top-N Per Group

The canonical pattern for "top N rows per partition":

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY region
      ORDER BY sale_amount DESC
    ) AS rn
  FROM sales
)
SELECT *
FROM ranked
WHERE rn <= 5;

This replaces a correlated subquery that would scan sales once per distinct region.

Deduplication with ROWNUMBER

A frequent ETL task: de-duplicate rows, keeping only the most recent version of each record.

WITH deduped AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM customer_events
)
SELECT * FROM deduped WHERE rn = 1;

LAG and LEAD: Temporal Comparisons

LAG(expr, offset, default) returns the value of expr from offset rows before the current row within the partition. LEAD does the same for rows after. The default argument supplies a value when the offset falls outside the partition boundary.

Period-over-Period Change

SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month)           AS prev_month_revenue,
  revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS mom_delta,
  ROUND(
    100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY month))
           / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_pct_change
FROM monthly_revenue
ORDER BY month;

Using NULLIF(..., 0) prevents division-by-zero when the prior month had zero revenue.

Gap and Island Detection

A classic problem: find consecutive active days per user. The island detection idiom uses ROWNUMBER to identify groups:

WITH numbered AS (
  SELECT
    user_id,
    activity_date,
    activity_date
      - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date))::INT
      AS grp  -- rows in the same consecutive run share the same grp value
  FROM user_activity
),
streaks AS (
  SELECT
    user_id,
    grp,
    MIN(activity_date) AS streak_start,
    MAX(activity_date) AS streak_end,
    COUNT(*)           AS streak_length
  FROM numbered
  GROUP BY user_id, grp
)
SELECT *
FROM streaks
WHERE streak_length >= 7  -- find users active 7+ consecutive days
ORDER BY streak_length DESC;

SELECT
  user_id,
  event_type,
  event_time,
  LEAD(event_type)  OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
  LEAD(event_time)  OVER (PARTITION BY user_id ORDER BY event_time) AS next_event_time,
  LEAD(event_time)  OVER (PARTITION BY user_id ORDER BY event_time)
    - event_time                                                     AS time_to_next
FROM user_events;

FIRSTVALUE, LASTVALUE, NTHVALUE

These return the first, last, or Nth value within the window frame. A subtle trap with LAST<em>VALUE: by default the frame ends at the current row, so LAST</em>VALUE returns the current row's value, not the partition's maximum. Fix with an explicit frame:

SELECT
  customer_id,
  order_date,
  FIRST_VALUE(amount) OVER w AS first_order_amount,
  LAST_VALUE(amount)  OVER w AS latest_order_amount
FROM orders
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);  -- full-partition frame for LAST_VALUE to be meaningful

Why Analytical Patterns Belong in SQL

Cohort analysis, funnel conversion, and retention are the three workhorses of product analytics. While tools like Mixpanel and Amplitude implement them in proprietary UIs, building them directly in SQL gives you full control over definitions, the ability to slice by arbitrary dimensions, and queryable results that integrate with your data warehouse.

Cohort Analysis

A cohort is a group of users who share a common starting event within the same time period — most commonly the week or month in which they first appeared. Cohort analysis asks: how does the behavior of users acquired in period T compare to users acquired in period T+1, T+2, etc.?

Step 1: Assign Cohort to Each User

WITH user_cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_time)) AS cohort_month
  FROM events
  WHERE event_type = 'signup'
  GROUP BY user_id
),

Step 2: Compute Each User's Activity Periods

user_activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_time) AS activity_month
  FROM events
),

Step 3: Join and Compute Period Offset

cohort_activity AS (
  SELECT
    uc.cohort_month,
    ua.activity_month,
    (
      EXTRACT(YEAR FROM ua.activity_month) * 12
      + EXTRACT(MONTH FROM ua.activity_month)
    ) - (
      EXTRACT(YEAR FROM uc.cohort_month) * 12
      + EXTRACT(MONTH FROM uc.cohort_month)
    ) AS months_since_cohort,
    COUNT(DISTINCT ua.user_id) AS active_users
  FROM user_cohorts uc
  JOIN user_activity ua USING (user_id)
  GROUP BY 1, 2, 3
),

Step 4: Compute Retention Rate

cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size
  FROM user_cohorts
  GROUP BY cohort_month
)
SELECT
  ca.cohort_month,
  ca.months_since_cohort,
  ca.active_users,
  cs.cohort_size,
  ROUND(100.0 * ca.active_users / cs.cohort_size, 1) AS retention_pct
FROM cohort_activity ca
JOIN cohort_sizes cs USING (cohort_month)
ORDER BY cohort_month, months_since_cohort;

The result is a cohort retention matrix. Month 0 is always 100% (users who signed up in that month were active that month). Month 1 shows what fraction returned in the following month.

Funnel Analysis

A funnel measures how many users complete each sequential step in a defined process (e.g., visit landing page → view product → add to cart → purchase).

The key challenge: users may trigger events out of order, repeat steps, or skip steps. The SQL must enforce the ordering constraint.

Self-Join Approach (Simple Funnels)

WITH step1 AS (
  SELECT user_id, MIN(event_time) AS t1
  FROM events WHERE event_type = 'page_view'
  GROUP BY user_id
),
step2 AS (
  SELECT user_id, MIN(event_time) AS t2
  FROM events WHERE event_type = 'add_to_cart'
  GROUP BY user_id
),
step3 AS (
  SELECT user_id, MIN(event_time) AS t3
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  COUNT(DISTINCT s1.user_id)                                 AS reached_step1,
  COUNT(DISTINCT s2.user_id)                                 AS reached_step2,
  COUNT(DISTINCT s3.user_id)                                 AS reached_step3,
  ROUND(100.0 * COUNT(DISTINCT s2.user_id)
              / NULLIF(COUNT(DISTINCT s1.user_id), 0), 1)   AS step1_to_2_pct,
  ROUND(100.0 * COUNT(DISTINCT s3.user_id)
              / NULLIF(COUNT(DISTINCT s2.user_id), 0), 1)   AS step2_to_3_pct
FROM step1 s1
LEFT JOIN step2 s2 ON s1.user_id = s2.user_id AND s2.t2 >= s1.t1
LEFT JOIN step3 s3 ON s2.user_id = s3.user_id AND s3.t3 >= s2.t2;

The AND s2.t2 >= s1.t1 condition enforces order: a user's add<em>to</em>cart event must occur at or after their first page_view.

Window-Function Approach (Ordered Events per User)

For funnels with many steps, the self-join explodes. The window approach uses LEAD to look ahead in a user's event sequence:

WITH ordered_events AS (
  SELECT
    user_id,
    event_type,
    event_time,
    LEAD(event_type, 1) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
    LEAD(event_type, 2) OVER (PARTITION BY user_id ORDER BY event_time) AS event_2_ahead
  FROM events
)
SELECT
  COUNT(*) FILTER (WHERE event_type = 'page_view')                                          AS step1,
  COUNT(*) FILTER (WHERE event_type = 'page_view' AND next_event = 'add_to_cart')            AS step2,
  COUNT(*) FILTER (WHERE event_type = 'page_view'
                     AND next_event = 'add_to_cart'
                     AND event_2_ahead = 'purchase')                                          AS step3
FROM ordered_events;

Note: FILTER (WHERE ...) is a PostgreSQL aggregate filter clause. In other databases use SUM(CASE WHEN ... THEN 1 ELSE 0 END).

N-Day Retention

N-day retention asks: of users active on day 0, what fraction returned exactly N days later? This is distinct from cohort retention (which uses any activity in month N) and is the standard for mobile app analytics.

WITH day0_users AS (
  SELECT DISTINCT user_id,
                  DATE_TRUNC('day', event_time) AS day0
  FROM events
  WHERE event_type = 'app_open'
),
day1_retained AS (
  SELECT DISTINCT d0.user_id, d0.day0
  FROM day0_users d0
  JOIN events e ON e.user_id = d0.user_id
               AND DATE_TRUNC('day', e.event_time) = d0.day0 + INTERVAL '1 day'
               AND e.event_type = 'app_open'
)
SELECT
  day0,
  COUNT(DISTINCT d0.user_id)  AS dau,
  COUNT(DISTINCT dr.user_id)  AS day1_retained_users,
  ROUND(
    100.0 * COUNT(DISTINCT dr.user_id)
           / NULLIF(COUNT(DISTINCT d0.user_id), 0),
    1
  ) AS day1_retention_rate
FROM day0_users d0
LEFT JOIN day1_retained dr USING (user_id, day0)
GROUP BY day0
ORDER BY day0;

Putting It Together: A Complete Product Analytics Query

Combining CTEs, window functions, and the patterns above, a single SQL statement can produce a weekly retention table, a funnel breakdown, and a revenue-per-cohort waterfall — the kind of analysis that previously required a Python notebook with dozens of pandas operations, now running as a single push-down query on hundreds of millions of rows.

Understanding the Execution Pipeline

Before optimizing window-function queries, you must understand where they sit in the execution pipeline. SQL engines process a query in this logical order:

1. FROM + JOIN (build the base relation)
2. WHERE (filter rows)
3. GROUP BY + HAVING (aggregate)
4. Window functions (computed over result of step 3)
5. SELECT (project columns)
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET

Window functions therefore operate on potentially aggregated data, and their results cannot be filtered in the same query's WHERE clause. A CTE or subquery wrapper is always required.

Reading EXPLAIN Output

The first optimization step is reading the query plan. In PostgreSQL:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
  user_id,
  event_time,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
FROM events;

Look for:

• Sort nodes: each distinct window specification requires a sort. If you see three Sort nodes, you have three distinct windows.
• WindowAgg node: the actual window function execution. Its cost is usually dominated by the preceding sort.
• Seq Scan vs Index Scan: for large tables, an index on (user<em>id, event</em>time) can replace a sort with an index scan, dramatically reducing cost.
• Rows estimate accuracy: a large discrepancy between estimated and actual rows in the Sort node indicates stale statistics; run ANALYZE table<em>name.

Minimizing Sort Passes

Each distinct PARTITION BY / ORDER BY combination in your window functions requires a separate sort of the data. The optimizer can combine windows with identical specifications, but it cannot merge different ones.

Bad pattern — three separate sorts:

SELECT
  user_id, event_time,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time ASC)  AS rn_asc,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) AS rn_desc,
  SUM(amount)  OVER (PARTITION BY region  ORDER BY event_time)      AS region_total
FROM events;

Better pattern — consolidate into CTEs:

WITH user_ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time ASC)  AS rn_asc,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) AS rn_desc
  FROM events
  -- Both windows share PARTITION BY user_id — the engine needs one sort
),
region_totals AS (
  SELECT user_id,
         SUM(amount) OVER (PARTITION BY region ORDER BY event_time) AS region_total
  FROM events
  -- Separate CTE = separate pass, but now explicit
)
SELECT ur.*, rt.region_total
FROM user_ranked ur
JOIN region_totals rt USING (user_id);

Pre-Filtering and Pre-Aggregating

Window functions must see every row in their partition. If you only need the top-3 per partition, there is no way to tell the engine to stop partitioning early. However, reducing the row count before windowing dramatically cuts cost:

-- Only analyze events from the last 90 days
WITH recent_events AS (
  SELECT * FROM events
  WHERE event_time >= NOW() - INTERVAL '90 days'
),
ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) AS rn
  FROM recent_events
)
SELECT * FROM ranked WHERE rn = 1;

The WHERE clause in the CTE runs before the window function, so the sort operates on 90 days of data instead of the entire table.

Materialized CTEs vs Inline CTEs

In PostgreSQL 12+, the optimizer inlines non-recursive CTEs by default. This is usually good (it allows predicate push-down), but it can be harmful when a CTE is referenced multiple times — the optimizer may execute it multiple times.

Rule of thumb:

• Single reference, simple CTE: let the optimizer inline it.
• Expensive CTE referenced 2+ times: use WITH cte AS MATERIALIZED (...).
• Any recursive CTE: always materialized automatically.

Indexes for Window Functions

An index on (partition</em>column, order<em>column) lets the engine read rows in pre-sorted order, eliminating the Sort node:

CREATE INDEX CONCURRENTLY idx_events_user_time
ON events (user_id, event_time);

For a query like ROW</em>NUMBER() OVER (PARTITION BY user<em>id ORDER BY event</em>time), this index means the engine reads each user's events in order without a sort.

BigQuery and Columnar Engine Differences

In columnar warehouses (BigQuery, Redshift, Snowflake, DuckDB), the performance model differs:

• Partitioned tables: PARTITION BY in the DDL (distinct from SQL PARTITION BY) allows the engine to prune partitions before windowing. Always filter on the partition column.
• Clustering / sort keys: equivalent to an index; reduces data shuffled for PARTITION BY.
• QUALIFY clause (BigQuery, Snowflake): a convenient alternative to the CTE+WHERE pattern for filtering on window function results:

-- Snowflake / BigQuery: QUALIFY replaces the outer WHERE filter
SELECT *
FROM products
QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) = 1;

Note: QUALIFY is not standard SQL and is not supported in PostgreSQL.

Summary Checklist

• Pre-filter rows before window functions using CTEs or subqueries.
• Minimize the number of distinct window specifications in a single query.
• Use named windows (WINDOW w AS (...)) to reuse identical specifications.
• Add composite indexes on (partition<em>col, order</em>col) for frequently windowed tables.
• Use MATERIALIZED for expensive CTEs referenced multiple times.
• Always use NULLIF or FILTER to handle division-by-zero in retention rate calculations.
• Validate frame semantics explicitly — ROWS vs RANGE produce different results when ties exist.