SQL Advanced Tutorial: Window Functions & Optimization

SQL Advanced Functions & Optimization Tutorial: Window Functions

📖 Introduction: Beyond Basic Queries

You've mastered filtering, joining, grouping, and even subqueries. But how do you answer questions like:

"Rank employees by salary within each department"
"Compare each sale to the previous day's sale"
"Categorize customers into spending tiers"
"Why is this query taking 5 minutes to run?"

Window functions perform calculations across a set of rows related to the current row — without collapsing results like GROUP BY does. Query optimization ensures your advanced queries run fast even on millions of rows.

Analogy: If GROUP BY is like summarizing an entire book into one paragraph, window functions are like adding margin notes that reference other pages — every row stays visible, but gains context from its neighbors.

🛠️ Setting Up Practice Data

sql
1CREATE DATABASE advanced_sql;
2USE advanced_sql;
3
4CREATE TABLE employees (
5    emp_id INT PRIMARY KEY AUTO_INCREMENT,
6    emp_name VARCHAR(100),
7    department VARCHAR(50),
8    salary DECIMAL(10,2),
9    hire_date DATE
10);
11
12INSERT INTO employees (emp_name, department, salary, hire_date) VALUES
13('Alice Johnson', 'IT', 90000, '2020-03-15'),
14('Bob Smith', 'HR', 55000, '2019-07-22'),
15('Carol White', 'IT', 80000, '2021-01-10'),
16('David Brown', 'Sales', 75000, '2018-11-05'),
17('Eve Davis', 'HR', 60000, '2022-06-18'),
18('Frank Miller', 'IT', 85000, '2020-09-30'),
19('Grace Lee', 'Sales', 72000, '2021-08-01'),
20('Henry Wilson', 'Marketing', 65000, '2023-02-14'),
21('Ivy Taylor', 'IT', 80000, '2019-05-20'),
22('Jack Anderson', 'Sales', 75000, '2020-12-01');
23
24CREATE TABLE sales (
25    sale_id INT PRIMARY KEY AUTO_INCREMENT,
26    sale_date DATE,
27    amount DECIMAL(10,2),
28    region VARCHAR(20)
29);
30
31INSERT INTO sales (sale_date, amount, region) VALUES
32('2026-08-01', 1200, 'North'),
33('2026-08-02', 800, 'South'),
34('2026-08-03', 1500, 'North'),
35('2026-08-04', 600, 'East'),
36('2026-08-05', 2000, 'North'),
37('2026-08-06', 900, 'South'),
38('2026-08-07', 1100, 'East');
39
40CREATE TABLE contractors (
41    contractor_name VARCHAR(100)
42);
43
44INSERT INTO contractors VALUES ('Alice Johnson'), ('Kevin Hart');

1️⃣ Window Functions — The OVER() Clause

Window functions calculate a value based on a window (set) of rows. Unlike GROUP BY, they don't collapse rows — every row remains in the output.

Basic Syntax:

sql
1function_name(expression) OVER (
2    [PARTITION BY column]
3    [ORDER BY column]
4    [frame_clause]
5)

Clause	Purpose
`PARTITION BY`	Divides data into groups (like GROUP BY but keeps all rows)
`ORDER BY`	Defines the order for ranking or running calculations
`frame_clause`	Specifies which rows relative to current row to include

2️⃣ ROW_NUMBER() — Unique Sequential Ranking

Purpose: Assigns a unique sequential number to each row, starting at 1.

Example 1: Rank All Employees by Salary

sql
1SELECT 
2    emp_name,
3    department,
4    salary,
5    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
6FROM employees;

Output:

+---------------+------------+----------+-------------+
| emp_name      | department | salary   | salary_rank |
+---------------+------------+----------+-------------+
| Alice Johnson | IT         | 90000.00 | 1           |
| Frank Miller  | IT         | 85000.00 | 2           |
| Carol White   | IT         | 80000.00 | 3           |
| Ivy Taylor    | IT         | 80000.00 | 4           |
| David Brown   | Sales      | 75000.00 | 5           |
| Jack Anderson | Sales      | 75000.00 | 6           |
| Grace Lee     | Sales      | 72000.00 | 7           |
| Henry Wilson  | Marketing  | 65000.00 | 8           |
| Eve Davis     | HR         | 60000.00 | 9           |
| Bob Smith     | HR         | 55000.00 | 10          |
+---------------+------------+----------+-------------+

Key Point: Even though Carol and Ivy both earn $80,000, they get different row numbers (3 and 4). ROW_NUMBER() never ties.

Example 2: Rank Within Each Department (PARTITION BY)

sql
1SELECT 
2    emp_name,
3    department,
4    salary,
5    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
6FROM employees;

Output:

+---------------+------------+----------+-----------+
| emp_name      | department | salary   | dept_rank |
+---------------+------------+----------+-----------+
| Alice Johnson | IT         | 90000.00 | 1         |
| Frank Miller  | IT         | 85000.00 | 2         |
| Carol White   | IT         | 80000.00 | 3         |
| Ivy Taylor    | IT         | 80000.00 | 4         |
| Eve Davis     | HR         | 60000.00 | 1         |
| Bob Smith     | HR         | 55000.00 | 2         |
| David Brown   | Sales      | 75000.00 | 1         |
| Jack Anderson | Sales      | 75000.00 | 2         |
| Grace Lee     | Sales      | 72000.00 | 3         |
| Henry Wilson  | Marketing  | 65000.00 | 1         |
+---------------+------------+----------+-----------+

PARTITION BY department resets the counter for each department.

3️⃣ RANK() vs DENSE_RANK() — Handling Ties

Function	Behavior with Ties	Next Rank After Tie
`RANK()`	Same number for ties	Skips numbers (`1, 1, 3`)
`DENSE_RANK()`	Same number for ties	Consecutive (`1, 1, 2`)

Example 3: Compare RANK, DENSE_RANK, and ROW_NUMBER

sql
1SELECT 
2    emp_name,
3    department,
4    salary,
5    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
6    RANK() OVER (ORDER BY salary DESC) AS rank_num,
7    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
8FROM employees;

Output (relevant rows):

+---------------+------------+----------+---------+----------+----------------+
| emp_name      | department | salary   | row_num | rank_num | dense_rank_num |
+---------------+------------+----------+---------+----------+----------------+
| Alice Johnson | IT         | 90000.00 | 1       | 1        | 1              |
| Frank Miller  | IT         | 85000.00 | 2       | 2        | 2              |
| Carol White   | IT         | 80000.00 | 3       | 3        | 3              |
| Ivy Taylor    | IT         | 80000.00 | 4       | 3        | 3              |
| David Brown   | Sales      | 75000.00 | 5       | 5        | 4              |
| Jack Anderson | Sales      | 75000.00 | 6       | 5        | 4              |
| Grace Lee     | Sales      | 72000.00 | 7       | 7        | 5              |
+---------------+------------+----------+---------+----------+----------------+

Analysis:

Carol and Ivy tie at $80,000 → both get RANK() = 3
Next rank (RANK()) jumps to 5 (skips 4 because two people tied for 3rd)
DENSE_RANK() continues with 4 (no gaps)
ROW_NUMBER() arbitrarily assigns 3 and 4 (order depends on how database sorts ties)

4️⃣ LEAD() and LAG() — Accessing Other Rows

Purpose: Look at values from the next (LEAD) or previous (LAG) row without a self-join.

Function	Looks At
`LAG(column, offset)`	Previous row (offset rows back)
`LEAD(column, offset)`	Next row (offset rows forward)

Example 4: Compare Salary to Previous Employee

sql
1SELECT 
2    emp_name,
3    salary,
4    LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary,
5    salary - LAG(salary, 1) OVER (ORDER BY salary) AS difference
6FROM employees
7ORDER BY salary;

Output:

+---------------+----------+-------------+------------+
| emp_name      | salary   | prev_salary | difference |
+---------------+----------+-------------+------------+
| Bob Smith     | 55000.00 | NULL        | NULL       |
| Eve Davis     | 60000.00 | 55000.00    | 5000.00    |
| Henry Wilson  | 65000.00 | 60000.00    | 5000.00    |
| Grace Lee     | 72000.00 | 65000.00    | 7000.00    |
| David Brown   | 75000.00 | 72000.00    | 3000.00    |
| Jack Anderson | 75000.00 | 75000.00    | 0.00       |
| Carol White   | 80000.00 | 75000.00    | 5000.00    |
| Ivy Taylor    | 80000.00 | 80000.00    | 0.00       |
| Frank Miller  | 85000.00 | 80000.00    | 5000.00    |
| Alice Johnson | 90000.00 | 85000.00    | 5000.00    |
+---------------+----------+-------------+------------+

NULL for the first row: There's no previous row for Bob, so LAG returns NULL.

Example 5: Compare Daily Sales to Next Day

sql
1SELECT 
2    sale_date,
3    amount,
4    LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_day_amount,
5    amount - LEAD(amount, 1) OVER (ORDER BY sale_date) AS day_difference
6FROM sales;

Output:

+------------+---------+-----------------+---------------+
| sale_date  | amount  | next_day_amount | day_difference|
+------------+---------+-----------------+---------------+
| 2026-08-01 | 1200.00 | 800.00          | 400.00        |
| 2026-08-02 | 800.00  | 1500.00         | -700.00       |
| 2026-08-03 | 1500.00 | 600.00          | 900.00        |
| 2026-08-04 | 600.00  | 2000.00         | -1400.00      |
| 2026-08-05 | 2000.00 | 900.00          | 1100.00       |
| 2026-08-06 | 900.00  | 1100.00         | -200.00       |
| 2026-08-07 | 1100.00 | NULL            | NULL          |
+------------+---------+-----------------+---------------+

5️⃣ AVG() as a Window Function — Running Averages

You can use aggregate functions (SUM, AVG, COUNT, MAX, MIN) as window functions with OVER().

Example 6: Department Average vs. Individual Salary

sql
1SELECT 
2    emp_name,
3    department,
4    salary,
5    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
6    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
7FROM employees;

Output:

+---------------+------------+----------+-------------+---------------+
| emp_name      | department | salary   | dept_avg    | diff_from_avg |
+---------------+------------+----------+-------------+---------------+
| Alice Johnson | IT         | 90000.00 | 83750.000000| 6250.00       |
| Frank Miller  | IT         | 85000.00 | 83750.000000| 1250.00       |
| Carol White   | IT         | 80000.00 | 83750.000000| -3750.00      |
| Ivy Taylor    | IT         | 80000.00 | 83750.000000| -3750.00      |
| Eve Davis     | HR         | 60000.00 | 57500.000000| 2500.00       |
| Bob Smith     | HR         | 55000.00 | 57500.000000| -2500.00      |
| David Brown   | Sales      | 75000.00 | 74000.000000| 1000.00       |
| Jack Anderson | Sales      | 75000.00 | 74000.000000| 1000.00       |
| Grace Lee     | Sales      | 72000.00 | 74000.000000| -2000.00      |
| Henry Wilson  | Marketing  | 65000.00 | 65000.000000| 0.00          |
+---------------+------------+----------+-------------+---------------+

Every row shows the average of its own department. No grouping or collapsing!

6️⃣ CASE Expressions — Conditional Logic in SQL

Purpose: Add if-then-else logic directly in your queries.

Syntax:

sql
1CASE 
2    WHEN condition1 THEN result1
3    WHEN condition2 THEN result2
4    ELSE default_result
5END

Example 7: Categorize Employees by Salary

sql
1SELECT 
2    emp_name,
3    salary,
4    CASE 
5        WHEN salary < 60000 THEN 'Low'
6        WHEN salary BETWEEN 60000 AND 80000 THEN 'Medium'
7        ELSE 'High'
8    END AS salary_category
9FROM employees;

Output:

+---------------+----------+-----------------+
| emp_name      | salary   | salary_category |
+---------------+----------+-----------------+
| Alice Johnson | 90000.00 | High            |
| Bob Smith     | 55000.00 | Low             |
| Carol White   | 80000.00 | Medium          |
| David Brown   | 75000.00 | Medium          |
| Eve Davis     | 60000.00 | Medium          |
| Frank Miller  | 85000.00 | High            |
| Grace Lee     | 72000.00 | Medium          |
| Henry Wilson  | 65000.00 | Medium          |
| Ivy Taylor    | 80000.00 | Medium          |
| Jack Anderson | 75000.00 | Medium          |
+---------------+----------+-----------------+

Example 8: CASE with Aggregate Functions

sql
1SELECT 
2    department,
3    COUNT(*) AS total,
4    SUM(CASE WHEN salary > 80000 THEN 1 ELSE 0 END) AS high_earners,
5    SUM(CASE WHEN salary <= 60000 THEN 1 ELSE 0 END) AS low_earners
6FROM employees
7GROUP BY department;

Output:

+------------+-------+--------------+-------------+
| department | total | high_earners | low_earners |
+------------+-------+--------------+-------------+
| HR         | 2     | 0            | 1           |
| IT         | 4     | 2            | 0           |
| Marketing  | 1     | 0            | 0           |
| Sales      | 3     | 0            | 0           |
+------------+-------+--------------+-------------+

7️⃣ PIVOT — Turning Rows into Columns

Purpose: Transform row-based data into a cross-tabular (spreadsheet-like) format.

Note: MySQL doesn't have a native PIVOT keyword. You simulate it using CASE + GROUP BY.

Example 9: Pivot Sales by Region

sql
1SELECT 
2    sale_date,
3    SUM(CASE WHEN region = 'North' THEN amount ELSE 0 END) AS North,
4    SUM(CASE WHEN region = 'South' THEN amount ELSE 0 END) AS South,
5    SUM(CASE WHEN region = 'East' THEN amount ELSE 0 END) AS East
6FROM sales
7GROUP BY sale_date
8ORDER BY sale_date;

Output:

+------------+---------+--------+--------+
| sale_date  | North   | South  | East   |
+------------+---------+--------+--------+
| 2026-08-01 | 1200.00 | 0.00   | 0.00   |
| 2026-08-02 | 0.00    | 800.00 | 0.00   |
| 2026-08-03 | 1500.00 | 0.00   | 0.00   |
| 2026-08-04 | 0.00    | 0.00   | 600.00 |
| 2026-08-05 | 2000.00 | 0.00   | 0.00   |
| 2026-08-06 | 0.00    | 900.00 | 0.00   |
| 2026-08-07 | 0.00    | 0.00   | 1100.00|
+------------+---------+--------+--------+

Example 10: Pivot Total Sales by Region

sql
1SELECT 
2    SUM(CASE WHEN region = 'North' THEN amount ELSE 0 END) AS North_Total,
3    SUM(CASE WHEN region = 'South' THEN amount ELSE 0 END) AS South_Total,
4    SUM(CASE WHEN region = 'East' THEN amount ELSE 0 END) AS East_Total,
5    SUM(amount) AS Grand_Total
6FROM sales;

8️⃣ UNION vs. UNION ALL — Combining Results

Operator	Behavior	Performance	Use When
`UNION`	Combines results, removes duplicates	Slower (sorts to find duplicates)	You need unique rows only
`UNION ALL`	Combines results, keeps all rows	Faster (no sorting)	You know there are no duplicates, or you want to keep duplicates

Example 11: UNION (Removes Duplicates)

sql
1SELECT emp_name AS name FROM employees
2UNION
3SELECT contractor_name AS name FROM contractors;

Output: Alice appears only once.

+----------------+
| name           |
+----------------+
| Alice Johnson  |
| Bob Smith      |
| Carol White    |
| David Brown    |
| Eve Davis      |
| Frank Miller   |
| Grace Lee      |
| Henry Wilson   |
| Ivy Taylor     |
| Jack Anderson  |
| Kevin Hart     |
+----------------+

Example 12: UNION ALL (Keeps Duplicates)

sql
1SELECT emp_name AS name FROM employees
2UNION ALL
3SELECT contractor_name AS name FROM contractors;

Output: Alice appears twice (once from each table).

+----------------+
| name           |
+----------------+
| Alice Johnson  |
| Bob Smith      |
| ...            |
| Alice Johnson  |
| Kevin Hart     |
+----------------+

Best Practice: Use UNION ALL unless you specifically need to remove duplicates. It's significantly faster because the database doesn't have to sort and compare every row.

9️⃣ Query Optimization Techniques

Even the best-written query can be slow. Here's how to diagnose and fix performance issues.

A) Use EXPLAIN to Analyze Queries

sql
1EXPLAIN SELECT * FROM employees WHERE department = 'IT';

What to look for:

Column	Meaning	Good Value
`type`	How table is accessed	`const`, `eq_ref`, `ref` (avoid `ALL`)
`key`	Index used	Should show an index name
`rows`	Rows examined	Lower is better
`Extra`	Additional info	Avoid `Using filesort`, `Using temporary`

B) Optimization Rules

Rule	Why It Helps
*Avoid `SELECT `**	Fetches unnecessary columns; use specific columns
Use indexes on WHERE, JOIN, ORDER BY columns	Reduces rows scanned from millions to dozens
Use `LIMIT` for large result sets	Stops scanning after finding enough rows
Prefer `JOIN` over subqueries	Optimizers handle joins more efficiently
Use `UNION ALL` over `UNION`	Skips expensive duplicate-removal step
Filter early with WHERE	Reduces data before grouping or sorting
Avoid functions on indexed columns	`WHERE YEAR(date) = 2024` prevents index use; use `WHERE date BETWEEN '2024-01-01' AND '2024-12-31'`

🚀 Hands-On Project: Leaderboard & Data Analysis

Project 1: Employee Salary Leaderboard

sql
1SELECT 
2    emp_name,
3    department,
4    salary,
5    RANK() OVER (ORDER BY salary DESC) AS overall_rank,
6    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
7    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
8    CASE 
9        WHEN RANK() OVER (ORDER BY salary DESC) <= 3 THEN '🥇 Top 3'
10        WHEN RANK() OVER (ORDER BY salary DESC) <= 5 THEN '🥈 Top 5'
11        ELSE 'Others'
12    END AS tier,
13    salary - LAG(salary, 1) OVER (ORDER BY salary DESC) AS gap_from_above
14FROM employees;

Output:

+---------------+------------+----------+--------------+-----------+------------+--------+---------------+
| emp_name      | department | salary   | overall_rank | dept_rank | dense_rank | tier   | gap_from_above|
+---------------+------------+----------+--------------+-----------+------------+--------+---------------+
| Alice Johnson | IT         | 90000.00 | 1            | 1         | 1          | 🥇 Top 3| NULL          |
| Frank Miller  | IT         | 85000.00 | 2            | 2         | 2          | 🥇 Top 3| 5000.00       |
| Carol White   | IT         | 80000.00 | 3            | 3         | 3          | 🥇 Top 3| 5000.00       |
| Ivy Taylor    | IT         | 80000.00 | 3            | 3         | 3          | 🥇 Top 3| 0.00          |
| David Brown   | Sales      | 75000.00 | 5            | 1         | 4          | 🥈 Top 5| -5000.00      |
| ...           | ...        | ...      | ...          | ...       | ...        | ...    | ...           |
+---------------+------------+----------+--------------+-----------+------------+--------+---------------+

Project 2: Sales Trend Analysis

sql
1WITH daily_sales AS (
2    SELECT 
3        sale_date,
4        amount,
5        SUM(amount) OVER (ORDER BY sale_date) AS running_total,
6        AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3day,
7        LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_day,
8        amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS day_change
9    FROM sales
10)
11SELECT 
12    sale_date,
13    amount,
14    running_total,
15    ROUND(moving_avg_3day, 2) AS moving_avg_3day,
16    prev_day,
17    day_change,
18    CASE 
19        WHEN day_change > 0 THEN '📈 Up'
20        WHEN day_change < 0 THEN '📉 Down'
21        ELSE '➡️ Same'
22    END AS trend
23FROM daily_sales;

Project 3: Department Performance Pivot

sql
1SELECT 
2    CASE 
3        WHEN salary < 60000 THEN 'Under 60K'
4        WHEN salary BETWEEN 60000 AND 80000 THEN '60K-80K'
5        ELSE 'Over 80K'
6    END AS salary_bracket,
7    SUM(CASE WHEN department = 'IT' THEN 1 ELSE 0 END) AS IT_Count,
8    SUM(CASE WHEN department = 'HR' THEN 1 ELSE 0 END) AS HR_Count,
9    SUM(CASE WHEN department = 'Sales' THEN 1 ELSE 0 END) AS Sales_Count,
10    SUM(CASE WHEN department = 'Marketing' THEN 1 ELSE 0 END) AS Marketing_Count,
11    COUNT(*) AS Total
12FROM employees
13GROUP BY salary_bracket
14ORDER BY MIN(salary);

📝 Complete Advanced SQL Reference

Function/Feature	Purpose	Example
`ROW_NUMBER()`	Unique rank per row	`ROW_NUMBER() OVER (ORDER BY salary DESC)`
`RANK()`	Rank with gaps for ties	`RANK() OVER (ORDER BY salary DESC)`
`DENSE_RANK()`	Rank without gaps	`DENSE_RANK() OVER (ORDER BY salary DESC)`
`LEAD(col, n)`	Next nth row value	`LEAD(salary, 1) OVER (ORDER BY date)`
`LAG(col, n)`	Previous nth row value	`LAG(salary, 1) OVER (ORDER BY date)`
`AVG() OVER()`	Running/group average	`AVG(salary) OVER (PARTITION BY dept)`
`CASE WHEN`	Conditional logic	`CASE WHEN x > 0 THEN 'Positive' ELSE 'Zero' END`
`UNION`	Combine, remove duplicates	`SELECT a FROM t1 UNION SELECT a FROM t2`
`UNION ALL`

✅ Module 12 Summary

Concept	What It Does	Use When
`ROW_NUMBER()`	Unique sequential ID	You need distinct ranks
`RANK()`	Rank with gaps	Ties should share rank, next skips
`DENSE_RANK()`	Rank without gaps	Ties share rank, next is consecutive
`LEAD()/LAG()`	Access adjacent rows	Comparing to previous/next period
Window `AVG()`	Running averages	Showing context without collapsing
`CASE`	If-then-else in SQL	Categorizing, conditional calculations
Pivot (CASE+GROUP)	Rows → Columns	Cross-tab reports
`UNION ALL`	Fast combination	You don't need duplicate removal
`EXPLAIN`	Performance diagnosis	Query is running slowly

🎯 Practice Exercises

Rank employees by hire_date (oldest first) using ROW_NUMBER().
Find the salary difference between each employee and the highest salary in their department using window functions.
Use NTILE(4) to divide employees into 4 salary quartiles.
Write a CASE expression that assigns letter grades: A (salary > 85000), B (70000-85000), C (< 70000).
Create a pivot showing total salary by department as columns.
Explain why UNION ALL is faster than UNION.
Run EXPLAIN on a query and identify if it's using an index.

🎓 Course Completion: What You've Mastered

Over 12 modules, you've progressed from absolute beginner to advanced SQL practitioner:

Module	Skills Gained
1	Database creation, `SHOW`, `USE`, `DESCRIBE`
2	`SELECT`, column selection, aliases, `DISTINCT`
3	`WHERE`, operators, `LIKE`, `BETWEEN`, `IN`, `ORDER BY`, `LIMIT`
4	Aggregates (`COUNT`, `SUM`, `AVG`, `MAX`, `MIN`), `GROUP BY`, `HAVING`
5	DML: `INSERT`, `UPDATE`, `DELETE`
6	DDL: `CREATE TABLE`, `ALTER TABLE`, `DROP`, data types
7	`INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN`, `FULL JOIN`,

You are now ready to:

Design production database schemas
Write complex analytical queries
Optimize slow-performing systems
Build secure, reliable data applications

Keep practicing. SQL fluency comes from solving real problems with real data. Build projects, explore public datasets, and never stop querying! 🚀