Data EngineeringAdvanced18 min readUpdated March 2025

Window Functions & CTEs

Window functions compute values across related rows without collapsing them, and Common Table Expressions make complex queries readable. Together they power ranking, running totals, deduplication, and sessionization.

What are Window Functions?

A window function performs a calculation across a set of rows related to the current row — without collapsing them like GROUP BY does. The window is defined with an OVER clause using PARTITION BY (grouping) and ORDER BY (ordering within the group).

Ranking and Running Totals

Common window functions include ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and aggregate functions used as windows:

sql

SELECT
  customer_id,
  order_date,
  amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq,
  SUM(amount)  OVER (PARTITION BY customer_id ORDER BY order_date
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  LAG(amount)  OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount
FROM orders;

Common Table Expressions (CTEs)

A CTE defines a named temporary result set using WITH. CTEs break complex logic into readable steps and can be referenced multiple times in the same query.

sql

-- Keep only the latest record per customer (deduplication)
WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM raw_customers
)
SELECT customer_id, full_name, country, updated_at
FROM ranked
WHERE rn = 1;

Where Data Engineers Use Them

These patterns appear constantly in pipeline code:

Deduplication — ROW_NUMBER() to keep the latest record per key.
Change detection — LAG() to compare a row with the previous one.
Top-N per group — RANK() filtered in an outer query.
Sessionization — Gaps between events computed with LAG and cumulative sums.

Key Takeaways

Window functions keep row-level detail while adding group-level context.
PARTITION BY defines groups; ORDER BY defines sequence within them.
CTEs improve readability and make multi-step logic testable.
ROW_NUMBER + CTE is the standard deduplication pattern.

Contact Us

Have a question or feedback? Fill out the form below or reach us directly at support@nvaitraining.com