SQL Window Functions

Visualize how calculations are performed across a set of table rows that are somehow related to the current row.

1. Load Dataset

2. Window Function

Assigns a unique integer to rows within a partition.

3. Partition By

4. Order By

Data Preview (Source + Result)

READY

Unlocking SQL Window Functions

Go beyond `GROUP BY` to perform calculations across sets of rows while keeping the original rows intact. This guide will make you an expert.

Quick Context

Window functions are a powerful feature in SQL that perform a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions (`SUM`, `COUNT`), which collapse rows into a single output row, window functions return a value for every single row.

The Core Idea: A "Window" into Your Data

The magic of window functions is the `OVER()` clause. This clause defines the "window" or set of rows the function should consider for its calculation. It has two key components:

`PARTITION BY`: This divides the rows into groups, or "partitions". The window function is applied independently to each partition. Think of it like a temporary `GROUP BY` that doesn't collapse the rows.
`ORDER BY`: This sorts the rows within each partition. This is crucial for functions that depend on order, like `RANK()`, `LAG()`, and `LEAD()`.

For each row, the function calculates its result based on the other rows in its "window" (its partition).

Guided Experiments with the Visualizer

The visualizer helps you see how the `PARTITION BY` and `ORDER BY` clauses define the calculation for each row.

Ranking Sales within Departments:
- Use the default 'Sales' dataset.
- Select the `RANK()` function.
- Partition By `Dept`.
- Order By `Sales (DESC)`.
- Click Run Visualizer. The animation first sorts the data by Department, then by Sales. It then processes each partition ('HR', 'Sales') separately, assigning a rank to each employee based on their sales within their own department.
`RANK()` vs. `DENSE_RANK()`:
- In the 'Sales' data, notice that 'David' and 'Eve' in HR have the same sales (5000). With `RANK()`, they both get rank 1. The next person, 'Frank', gets rank 3 because two people tied for first.
- Now, switch the function to `DENSE_RANK()` and run it again. David and Eve still get rank 1, but Frank now gets rank 2. `DENSE_RANK` doesn't skip numbers after a tie.
Finding the Previous Sale (`LAG`):
- Keep the same partition and order, but select the `LAG(Value)` function.
- Run the visualizer. For each row, the result is the `Sales` value from the row directly above it *within the same partition*. The first row of each partition (e.g., Bob in Sales, Frank in HR) will have a `NULL` result because there is no preceding row in their window.

Common Use Cases

Window functions are essential for advanced analysis. Here are some classic problems they solve:

-- Find the top 3 selling employees in each region
SELECT * FROM (
  SELECT Name, Region, Sales,
    RANK() OVER (PARTITION BY Region ORDER BY Sales DESC) as rank_num
  FROM SalesData
) AS ranked_sales
WHERE rank_num <= 3;

-- Calculate month-over-month sales growth for each department
SELECT Dept, Month, Sales,
  Sales - LAG(Sales, 1, 0) OVER (PARTITION BY Dept ORDER BY Month) as sales_growth
FROM MonthlySales;

Key Takeaways

Window functions compute a value for each row based on a "window" of related rows.
They do not collapse rows, preserving the original table's granularity.
`PARTITION BY` is like a temporary `GROUP BY` that defines the window.
`ORDER BY` is essential for ranking and sequential functions (`LAG`, `LEAD`).
Use them for ranking, running totals, moving averages, and comparing a row to its peers.

Cheat sheet