Introduction to Window Functions

The Ranking Dilemma

The CEO asked for a report of all orders, but with each order showing its rank compared to other orders from the same customer. With GROUP BY, I'd lose the individual order details. I needed to calculate *across* rows without collapsing them.

The Quest: The Window Function

A **Window Function** performs a calculation across a set of rows that are related to the current row. Unlike GROUP BY, it doesn't collapse the result into one row—it adds a new column while keeping every original row.

The Anatomy of a Window Function

SELECT

order_id,

customer_id,

amount,

SUM(amount) OVER (PARTITION BY customer_id) AS customer_total

FROM orders;

Breaking It Down

`SUM(amount)`: The function to apply.

`OVER (...)`: The keyword that makes it a window function.

`PARTITION BY customer_id`: The "Groups" to calculate within (similar to GROUP BY).

The "Oops" Moment

I tried to use a window function in the WHERE clause: `WHERE RANK() OVER ... = 1`. SQL threw an error. Window functions can only be in `SELECT` or `ORDER BY`.

**Pro Tip**: To filter on a window function result, wrap your query in a CTE or subquery first.

The Victory

Every order row remained intact, but now each had a new column showing the customer's total spend. Window functions are the bridge between detailed data and calculated insights.

*Day 62: Ranking with ROW_NUMBER, RANK, and DENSE_RANK.*