SQL Window Functions

GROUP BY squeezes many rows into one. A window function does a calculation across a set of related rows but keeps every row in the result. That is what lets you show a rank, a running total or the previous row’s value right beside the original data.

Every window function has an OVER clause. Inside it, PARTITION BY splits the rows into groups (optional), and ORDER BY sets the order within each group.

Ranking Rows

The three ranking functions differ only in how they treat ties. Take an agent_sales table where two people in each region have the same score.

SELECT agent, region, sales,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC, agent) AS row_num,
       RANK()       OVER (PARTITION BY region ORDER BY sales DESC)        AS rnk,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY sales DESC)        AS dense_rnk
FROM agent_sales
ORDER BY region, sales DESC, agent;

Output:

Code Explanation:

• PARTITION BY region restarts the numbering inside each region.
• ROW_NUMBER gives every row a unique number. The agent name is added as a tie-breaker so the order is predictable.
• RANK gives tied rows the same rank and then skips ahead, so Asha is 3rd, not 2nd.
• DENSE_RANK also repeats the rank for ties but never skips, so Asha is 2nd.

A very common use is “top 1 per group”. Number the rows with ROW_NUMBER inside a CTE, then keep the rows where the number is 1.

Running Totals and Comparing with the Previous Row

Now take a monthly_orders table, where SUM becomes a running total, and LAG looks back one row.

SELECT month_start,
       orders,
       SUM(orders) OVER (ORDER BY month_start)            AS running_total,
       orders - LAG(orders) OVER (ORDER BY month_start)   AS change_from_prev
FROM monthly_orders;

Output:

Code Explanation:

• SUM(...) OVER (ORDER BY month_start) adds up the orders from the first month to the current one.
• LAG(orders) fetches the value from the row just before the current one.
• Subtracting it gives the month-on-month change. January has no earlier row, so its change is NULL.

LEAD works the same way but looks forward to the next row.