Expert

Window Functions: The Spreadsheet-Killer of SQL

SQL Mastery Team
March 23, 2026
5 min read

Welcome to **Phase 5: Window Functions**! Today is **Day 61**, and we're learning the most advanced analytical tool in the SQL language.

Why "Spreadsheet-Killer"?

In Excel, you can look at Row 5 and say, "Give me the sum of cells B1 through B5." In basic SQL (`GROUP BY`), you can't do that. Once you group, the individual rows are gone.

**Window Functions** give you the best of both worlds. They perform a calculation across a set of rows (the "Window") but **keep the individual rows intact**.

The Syntax: OVER()

The keyword that signals a window function is `OVER`.

SELECT

name,

salary,

AVG(salary) OVER() AS company_avg

FROM employees;

In this query, every employee row will show their own salary AND the company-wide average right next to it. No grouping required!

What's coming in Phase 5

  • **Ranking**: `ROW_NUMBER`, `RANK`, `DENSE_RANK`.
  • **Comparison**: `LAG` and `LEAD` (compare to previous/next row).
  • **Aggregates**: Running totals and moving averages.
  • **Partitioning**: Doing all of the above *per group* (e.g., avg salary per department).

    • Your Task for Today

    Write a query that shows every product name, its price, and the maximum price in the entire table using `MAX(price) OVER()`.

    *Day 62: ROW_NUMBER vs RANK vs DENSE_RANK.*

    Ready to put your knowledge into practice?

    Join SQL Mastery and learn through interactive exercises.