SQL Window Functions

When I first learned SQL, I used to write long subqueries and messy joins just to do simple things like calculating running totals or ranking rows.

Then I learned window functions, and everything became much simpler.

Unlike standard GROUP BY (which squashes multiple rows into one summary row), a window function lets you calculate across rows while keeping every original row visible.

1. Ranking Rows

If you want to rank items—like finding the highest-paid person in each team—use RANK() or DENSE_RANK():

SELECT 
    name,
    department,
    salary,
    DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

PARTITION BY splits your data into groups (like departments), and ORDER BY sorts them so SQL knows who comes first.

2. Running Totals

Want to see cumulative sales day by day? Instead of joining the table to itself, write:

SELECT 
    order_date,
    amount,
    SUM(amount) OVER(ORDER BY order_date) as running_total
FROM sales;

SQL steps through each day in order and adds the amount to the running total.

3. Comparing with the Previous Row

If you want to calculate month-over-month growth, you need to look at the previous month's number. That is what LAG() is for:

SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER(ORDER BY month) as last_month_revenue
FROM monthly_sales;

Takeaway

Window functions look intimidating at first glance with OVER(...), but they solve common data problems with clean, fast, readable code. Once you try them, you will never want to go back to complex self-joins.