This lesson on Window Functions for Analysts is hands-on and example-driven. You will calculate analytical aggregate metrics and positional rankings across subsets of data without collapsing individual records. By mastering the OVER, PARTITION BY, and ORDER BY clauses, you can seamlessly interweave group-level calculations directly alongside row-level data.
What You'll Be Able To Do
- Differentiate between row-collapsing GROUP BY queries and row-preserving window calculations.
- Compute table-wide metrics alongside individual records using an empty OVER clause.
- Divide datasets into calculation windows using PARTITION BY to evaluate department-level aggregates.
- Calculate positional order and salaries using the RANK function with ORDER BY.
- Combine PARTITION BY and ORDER BY within an OVER clause to generate scoped intra-group rankings.
Detailed Concept Walkthrough
1. Row-Preserving Aggregations vs GROUP BY
Standard GROUP BY queries collapse multiple rows into a single summary row per category, whereas window functions compute aggregate values across groups while retaining every individual row in the output.
- Mechanism: Standard aggregate functions evaluate an entire group and emit only one row per distinct group key, discarding individual record attributes. Window functions calculate the same aggregate mathematics across defined row sets but reintegrate the resulting value into every contributing row.
- Under the Hood: The SQL engine processes the base table rows first, applies the window calculation across the specified subset of records in memory, and appends the calculated scalar to each corresponding row buffer.
- Nuance: Because window functions do not collapse rows, you can project non-aggregated row identifiers (like employee numbers or names) alongside group metrics in the same SELECT statement without causing grouping errors.
-- Compare row-level salary with department-level average
SELECT
emp_no,
department,
salary,
AVG(salary) OVER(PARTITION BY department) AS dept_avg_salary
FROM employees;
Key Takeaway: Window functions append aggregate calculations directly to individual rows instead of reducing the result set.
2. Global Windows with Empty OVER
An empty OVER clause defines a single window encompassing all selected rows, calculating an overall aggregate metric that is displayed across every individual record.
- Mechanism: Appending OVER () to an aggregate function signals the query engine to treat the entire result set as a single, unpartitioned window.
- Execution Flow: The database scans all qualifying rows, calculates the global aggregate metric once, and populates that exact identical value across every returned row.
- Best Practice: Use empty OVER clauses when you need to compute relative comparisons, such as showing each employee's salary against the company-wide average salary.
-- Compute company-wide average alongside individual employee data
SELECT
emp_no,
department,
salary,
AVG(salary) OVER () AS overall_avg_salary
FROM employees;
Key Takeaway: An empty OVER clause treats the entire dataset as one continuous window for global calculations.
3. Scoped Windows via PARTITION BY
The PARTITION BY clause subdivides the complete dataset into distinct, non-overlapping windows based on column values, calculating metrics independently within each partition.
- Mechanism: PARTITION BY acts like an inline group specification, breaking the dataset into logical slices where aggregate functions compute isolated metrics per slice.
- Execution Flow: The query engine groups records sharing identical partition keys, calculates the required aggregate (such as MIN, MAX, or SUM) within that boundary, and attaches the result to each row in that group.
- Syntax Rule: Multiple aggregate window functions with identical or different PARTITION BY clauses can be specified within the same SELECT list to produce multi-metric summaries.
-- Calculate department minimum, maximum, and average on each row
SELECT
emp_no,
department,
salary,
MIN(salary) OVER (PARTITION BY department) AS dept_min_salary,
MAX(salary) OVER (PARTITION BY department) AS dept_max_salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;
Key Takeaway: PARTITION BY isolates aggregate calculations to specific subsets without collapsing individual records.
4. Positional Ranking with ORDER BY
Window-only ranking functions assign relative positional values to rows based on a specified sort order across global or partitioned scopes.
- Mechanism: Dedicated window functions like RANK() evaluate the sequential order of rows determined by an ORDER BY sub-clause inside the OVER definition.
- Under the Hood: The engine sorts the records within each partition according to the ORDER BY specification and assigns integer ranks based on sorted values.
- Syntax Rule: ORDER BY can be used alone inside OVER for global table ranking, or combined with PARTITION BY to restart rank numbering from 1 within each distinct group.
-- Calculate both company-wide and departmental salary ranks
SELECT
emp_no,
department,
salary,
RANK() OVER (ORDER BY salary DESC) AS overall_rank,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;
Key Takeaway: The ORDER BY sub-clause within OVER establishes the evaluation sequence required for positional window functions.
Topics Covered in Window Functions for Analysts
- GROUP BY Aggregation Review (0:00 - 1:15) — Review of standard GROUP BY queries collapsing multiple employee rows into single departmental summary records.
- Window Function Mechanics (1:16 - 2:35) — Demonstration of calculating department average salary while preserving individual employee rows in the final output.
- Global Windows with OVER (2:36 - 3:55) — Explanation of the OVER keyword and computing company-wide averages across all rows using empty parentheses.
- Partitioning Window Calculations (3:56 - 5:20) — Applying PARTITION BY to calculate department minimums, maximums, and averages simultaneously alongside employee records.
- Ranking Functions and ORDER BY (5:21 - 7:00) — Introduction of the RANK function combined with global and partitioned ORDER BY clauses to evaluate salary positions.
- Window Functions Summary (7:01 - 8:15) — Recap of OVER, PARTITION BY, and ORDER BY mechanics with a preview of advanced functions like LEAD and LAG.
SQL Cheat Sheet
-
AVG(col) OVER ()— Calculates overall column average across all rowsSELECT emp_no, AVG(salary) OVER () AS overall_avg FROM employees; -
AVG(col) OVER (PARTITION BY group_col)— Calculates group average alongside each individual rowSELECT emp_no, AVG(salary) OVER (PARTITION BY department) FROM employees; -
MIN(col) OVER (PARTITION BY group_col)— Finds partition minimum value for every rowSELECT emp_no, MIN(salary) OVER (PARTITION BY department) FROM employees; -
MAX(col) OVER (PARTITION BY group_col)— Finds partition maximum value for every rowSELECT emp_no, MAX(salary) OVER (PARTITION BY department) FROM employees; -
RANK() OVER (ORDER BY col DESC)— Ranks all rows globally by sorted column valuesSELECT emp_no, RANK() OVER (ORDER BY salary DESC) FROM employees; -
RANK() OVER (PARTITION BY group_col ORDER BY col DESC)— Ranks rows within each partition independentlySELECT emp_no, RANK() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;
Comparison Table
| Feature | GROUP BY | OVER (PARTITION BY) |
|---|---|---|
| Output Row Count | Collapsed to one per group | Preserves every original row |
| Detail Visibility | Hides individual row attributes | Retains individual row attributes |
| Ranking Capabilities | Cannot generate inline ranks | Supports ranking functions like RANK |
| Simultaneous Scopes | Single aggregation granularity | Allows multiple partition granularities |
Common Pitfalls
- Mistake: Using GROUP BY when row-level details and group aggregates are both needed. Avoid: Use an aggregate function with an OVER clause to retain record granularity.
- Mistake: Omitting ORDER BY inside OVER when calling ranking functions. Avoid: Always include ORDER BY inside the OVER clause so rank ordering criteria is defined.
- Mistake: Assuming empty OVER partitions rows automatically by selected dimensions. Avoid: Explicitly specify PARTITION BY inside the OVER clause to calculate subset metrics.
FAQs
- What happens if I omit PARTITION BY inside the OVER clause? The function treats the entire result set as one single window and calculates the metric across all rows.
- Can I combine different window partitions in the same SELECT statement? Yes, you can calculate overall metrics using empty OVER alongside department-level metrics using PARTITION BY in a single query.
- Can ranking functions like RANK() run without an OVER clause? No, window-specific functions like RANK, LEAD, and LAG require an OVER clause to define their calculation scope.