DE Window Functions
Standard aggregations with GROUP BY collapse multiple rows into one summary row per group. Window functions take a different approach — they compute a value for each row based on a set of related rows, without collapsing the result. Both the individual row values and the computed aggregate appear together in the output. Window functions unlock a class of analytical queries that GROUP BY cannot handle.
What Makes Window Functions Special
With GROUP BY, if you calculate total revenue per city, each city becomes one row in the result. Window functions let you add "total revenue for this city" as an extra column on every individual order row, while keeping all original rows intact. This enables calculations like "what percentage of city revenue does this order represent" on a per-row basis.
The Scoreboard Analogy
Picture a class exam scoreboard. Standard aggregation gives you the class average — one number. A window function gives you each student's score alongside the class average in the same row. Every student's row shows their own score and the average of the class. You can instantly see who scored above and below average without a separate query. That is the power of window functions.
Window Function Syntax
SELECT
column1,
column2,
aggregate_function(column) OVER (
PARTITION BY partition_column
ORDER BY order_column
ROWS BETWEEN ... AND ...
) AS alias
FROM table;
The OVER clause defines the "window" — the set of rows
the function looks at for each row it computes.
PARTITION BY: Define Groups Without Collapsing
-- For each order, show the total revenue from that customer
SELECT
order_id,
customer_id,
amount,
SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;
Result:
+----------+-------------+--------+----------------+
| order_id | customer_id | amount | customer_total |
+----------+-------------+--------+----------------+
| ORD001 | C001 | 2500 | 3700 |
| ORD002 | C001 | 1200 | 3700 |
| ORD003 | C002 | 800 | 800 |
| ORD004 | C003 | 4100 | 6300 |
| ORD005 | C003 | 2200 | 6300 |
+----------+-------------+--------+----------------+
All rows remain. Each row shows its own amount AND
the total for its customer_id group.
ORDER BY Inside OVER: Running Calculations
Adding ORDER BY inside the OVER clause creates running (cumulative) calculations. The window function computes the result considering all rows up to and including the current row, ordered by the specified column.
-- Cumulative revenue over time per customer
SELECT
order_id,
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS running_total
FROM orders;
Result:
+----------+------+------------+--------+---------------+
| order_id | cust | order_date | amount | running_total |
+----------+------+------------+--------+---------------+
| ORD001 | C001 | 2024-01-10 | 2500 | 2500 |
| ORD002 | C001 | 2024-02-15 | 1200 | 3700 |
| ORD005 | C003 | 2024-01-05 | 4100 | 4100 |
| ORD006 | C003 | 2024-03-20 | 2200 | 6300 |
+----------+------+------------+--------+---------------+
Ranking Functions
Three window functions rank rows within a partition. Data engineers use these constantly to find top performers, identify the most recent records, and deduplicate data.
ROW_NUMBER()
Assigns a unique sequential number to each row within the partition. No ties — every row gets a unique number even if values are identical.
RANK()
Same as ROW_NUMBER but skips numbers after ties. Two rows tied at rank 1 both get rank 1, and the next row gets rank 3 (not 2).
DENSE_RANK()
Like RANK but does not skip numbers after ties. Two rows tied at rank 1 both get rank 1, and the next row gets rank 2.
-- Rank products by revenue within each category
SELECT
product_name,
category,
revenue,
RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank_in_cat,
DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rank,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS row_num
FROM product_sales;
Result:
+---------------+----------+--------+------------+------------+---------+
| product_name | category | revenue| rank_in_cat| dense_rank | row_num |
+---------------+----------+--------+------------+------------+---------+
| Laptop Pro | Laptops | 75000 | 1 | 1 | 1 |
| Laptop Air | Laptops | 75000 | 1 | 1 | 2 |
| Laptop Lite | Laptops | 40000 | 3 | 2 | 3 |
| Mouse Slim | Mice | 8000 | 1 | 1 | 1 |
+---------------+----------+--------+------------+------------+---------+
Deduplication with ROW_NUMBER()
ROW_NUMBER() solves one of the most common data engineering problems: removing duplicate records while keeping only the most recent version of each.
-- Keep only the latest record per customer
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY updated_at DESC
) AS rn
FROM customer_records
)
SELECT * FROM ranked WHERE rn = 1;
LAG() and LEAD(): Compare Across Rows
LAG() retrieves the value from the previous row. LEAD() retrieves the value from the next row. These functions calculate period-over-period changes without any self-joins.
-- Month-over-month revenue change
SELECT
month,
revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
revenue - LAG(revenue, 1) OVER (ORDER BY month) AS revenue_change
FROM monthly_revenue;
Result:
+-------+---------+--------------------+----------------+
| month | revenue | prev_month_revenue | revenue_change |
+-------+---------+--------------------+----------------+
| Jan | 120000 | NULL | NULL |
| Feb | 135000 | 120000 | +15000 |
| Mar | 128000 | 135000 | -7000 |
| Apr | 152000 | 128000 | +24000 |
+-------+---------+--------------------+----------------+
Summary
Window functions compute values for each row based on a related set of rows defined by the OVER clause. PARTITION BY groups rows without collapsing them. ORDER BY inside OVER creates running calculations. ROW_NUMBER, RANK, and DENSE_RANK assign ranking within partitions. LAG and LEAD compare values across adjacent rows. These functions handle analytical patterns that GROUP BY alone cannot express, making them indispensable in data engineering transformation work.