Window Functions Every Data Engineer Uses

Deduplication, latest-record-per-key, running totals and gaps — the window patterns that show up in every pipeline and interview.

SQLBeginner3 min readUpdated 11 Oct 2026

~/notes/sql/window-functions

The anatomy

function_name(...) OVER (
  PARTITION BY <group>      -- restart the calculation per group
  ORDER BY <sequence>       -- order inside the group
  ROWS BETWEEN ...          -- optional frame
)

Unlike GROUP BY, a window function keeps every row and adds a computed column.

Pattern 1 — latest record per key (deduplication)

SELECT *
FROM (
  SELECT c.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM raw_customers c
) t
WHERE rn = 1;

Snowflake, BigQuery and Databricks support QUALIFY, which avoids the subquery:

SELECT * FROM raw_customers
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;

Pattern 2 — ROW_NUMBER vs RANK vs DENSE_RANK

value	ROW_NUMBER	RANK	DENSE_RANK
100	1	1	1
100	2	1	1
90	3	3	2

Pattern 3 — running totals and moving averages

SELECT order_date,
       daily_sales,
       SUM(daily_sales) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total,
       AVG(daily_sales) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM daily_sales;

Use ROWS (physical rows) rather than the default RANGE frame when dates can repeat.

Pattern 4 — change detection with LAG

SELECT customer_id, loyalty_tier, observed_at,
       LAG(loyalty_tier) OVER (PARTITION BY customer_id ORDER BY observed_at) AS previous_tier
FROM customer_observations;
-- rows where loyalty_tier <> previous_tier are tier changes (the basis of SCD2)

Finished this note?

LinkedIn X WhatsApp