Why advanced window functions matter in SQL - See It in Action

Why Advanced Window Functions Matter

📖 Scenario: You work as a data analyst for an online retail company. Your manager wants to understand customer purchase patterns over time to improve marketing strategies. You have a sales table with customer purchases, and you need to analyze running totals, rankings, and moving averages to gain insights.

🎯 Goal: Build SQL queries using advanced window functions to calculate running totals of sales per customer, rank customers by total sales, and compute moving averages of daily sales.

📋 What You'll Learn

Create a table called sales with columns customer_id (integer), purchase_date (date), and amount (decimal).

Insert sample data with multiple customers and purchase dates.

Write a query to calculate the running total of amount for each customer_id ordered by purchase_date using a window function.

Write a query to rank customers by their total sales amount using a window function.

Write a query to calculate a 3-day moving average of daily total sales using a window function.

💡 Why This Matters

🌍 Real World

Window functions are essential for analyzing time series data, ranking, and cumulative calculations in business reports.

💼 Career

Data analysts and database developers use advanced window functions to write efficient queries that provide deep insights without complex code.

1

Create the sales table and insert sample data

Create a table called sales with columns customer_id (integer), purchase_date (date), and amount (decimal). Then insert these exact rows: (1, '2024-01-01', 100.00), (1, '2024-01-03', 150.00), (2, '2024-01-01', 200.00), (2, '2024-01-02', 50.00), (3, '2024-01-03', 300.00).

SQL

-- Create the sales table and insert sample data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Add a query to calculate running total per customer

Write a SQL query that selects customer_id, purchase_date, amount, and a new column running_total which is the running total of amount for each customer_id ordered by purchase_date. Use the window function SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date).

SQL

CREATE TABLE sales (
    customer_id INTEGER,
    purchase_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (customer_id, purchase_date, amount) VALUES
(1, '2024-01-01', 100.00),
(1, '2024-01-03', 150.00),
(2, '2024-01-01', 200.00),
(2, '2024-01-02', 50.00),
(3, '2024-01-03', 300.00);

-- Write a query to calculate running total per customer
-- Your code here

Hint

Use SUM() OVER (PARTITION BY ... ORDER BY ...) to get running totals.

3

Rank customers by total sales amount

Write a SQL query that selects customer_id, total sales as total_sales, and a rank column that ranks customers by their total sales in descending order. Use RANK() OVER (ORDER BY total_sales DESC). Calculate total_sales using SUM(amount) grouped by customer_id.

SQL

CREATE TABLE sales (
    customer_id INTEGER,
    purchase_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (customer_id, purchase_date, amount) VALUES
(1, '2024-01-01', 100.00),
(1, '2024-01-03', 150.00),
(2, '2024-01-01', 200.00),
(2, '2024-01-02', 50.00),
(3, '2024-01-03', 300.00);

SELECT customer_id, purchase_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS running_total
FROM sales
ORDER BY customer_id, purchase_date;

-- Write a query to rank customers by total sales
-- Your code here

Hint

Use GROUP BY with SUM() and RANK() OVER (ORDER BY ...) to rank customers.

4

Calculate 3-day moving average of daily total sales

Write a SQL query that calculates the daily total sales as daily_total and a 3-day moving average of daily_total as moving_avg. Use AVG(daily_total) OVER (ORDER BY purchase_date ROWS 2 PRECEDING). The query should select purchase_date, daily_total, and moving_avg from the sales table.

SQL

CREATE TABLE sales (
    customer_id INTEGER,
    purchase_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (customer_id, purchase_date, amount) VALUES
(1, '2024-01-01', 100.00),
(1, '2024-01-03', 150.00),
(2, '2024-01-01', 200.00),
(2, '2024-01-02', 50.00),
(3, '2024-01-03', 300.00);

SELECT customer_id, purchase_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS running_total
FROM sales
ORDER BY customer_id, purchase_date;

SELECT customer_id, SUM(amount) AS total_sales,
       RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
FROM sales
GROUP BY customer_id
ORDER BY rank;

-- Write a query to calculate 3-day moving average of daily total sales
-- Your code here

Hint

Use a common table expression (CTE) to get daily totals, then apply AVG() OVER (ORDER BY ... ROWS 2 PRECEDING) for the moving average.

Practice

1. What is the main advantage of using advanced window functions in SQL compared to simple aggregate functions?

easy

A. They only work on single rows without considering others.

B. They automatically create indexes for faster queries.

C. They allow calculations across rows without collapsing the result into groups.

D. They replace the need for JOIN operations.

Solution

1. Step 1: Understand aggregate functions behavior

   Aggregate functions like SUM or AVG group rows and return one result per group, collapsing data.

2. Step 2: Understand window functions behavior

   Window functions perform calculations across rows but keep each row visible, without grouping them.

3. Final Answer:

   They allow calculations across rows without collapsing the result into groups. -> Option C

4. Quick Check:

   Window functions = calculations without grouping [OK]

Hint: Window functions keep rows, aggregates collapse them [OK]

Common Mistakes:

• Thinking window functions group rows like aggregates
• Confusing window functions with JOINs
• Assuming window functions only work on single rows

2. Which of the following is the correct syntax to calculate a running total of sales using a window function?

easy

A. SELECT SUM(sales) OVER (ORDER BY date) AS running_total FROM sales_data;

B. SELECT SUM(sales) GROUP BY date ORDER BY running_total FROM sales_data;

C. SELECT RUNNING_TOTAL(sales) FROM sales_data ORDER BY date;

D. SELECT sales + PREVIOUS(sales) FROM sales_data;

Solution

1. Step 1: Identify correct window function syntax

   SUM() OVER (ORDER BY column) computes running total ordered by that column.

2. Step 2: Check other options for syntax errors

   The other options use invalid SQL syntax or functions that don't exist.

3. Final Answer:

   SELECT SUM(sales) OVER (ORDER BY date) AS running_total FROM sales_data; -> Option A

4. Quick Check:

   SUM() OVER (ORDER BY ...) = running total [OK]

Hint: Use SUM() OVER (ORDER BY ...) for running totals [OK]

Common Mistakes:

• Using GROUP BY instead of OVER()
• Trying non-existent functions like RUNNING_TOTAL()
• Adding values manually without window syntax

3. Given the table employees with columns department, salary, and name, what will the following query return?

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

medium

A. Calculates total salary per department.

B. Ranks employees by salary across all departments combined.

C. Returns employees sorted by name with no ranking.

D. Ranks employees by salary within each department, highest salary gets rank 1.

Solution

1. Step 1: Understand PARTITION BY usage

   PARTITION BY department groups rows by department for the window function.

2. Step 2: Understand RANK() OVER (ORDER BY salary DESC)

   Ranks salaries in descending order within each department, highest salary gets rank 1.

3. Final Answer:

   Ranks employees by salary within each department, highest salary gets rank 1. -> Option D

4. Quick Check:

   RANK() + PARTITION BY = rank within groups [OK]

Hint: PARTITION BY groups, ORDER BY sets rank order [OK]

Common Mistakes:

• Thinking RANK() ignores PARTITION BY
• Confusing RANK() with total aggregation
• Assuming sorting by name affects rank

4. Identify the error in this SQL query that tries to calculate a cumulative sum of sales per region:

SELECT region, sales, SUM(sales) OVER (PARTITION region ORDER BY date) AS cumulative_sales FROM sales_data;

medium

A. Missing parentheses around PARTITION BY clause.

B. Incorrect keyword; should be PARTITION BY instead of PARTITION.

C. ORDER BY cannot be used inside window functions.

D. SUM() cannot be used as a window function.

Solution

1. Step 1: Check window function syntax

   The correct syntax requires PARTITION BY, not just PARTITION.

2. Step 2: Validate other parts

   ORDER BY inside OVER() is valid for running totals; SUM() is valid as window function.

3. Final Answer:

   Incorrect keyword; should be PARTITION BY instead of PARTITION. -> Option B

4. Quick Check:

   Use PARTITION BY, not PARTITION alone [OK]

Hint: Use PARTITION BY, not just PARTITION [OK]

Common Mistakes:

• Omitting BY after PARTITION
• Thinking ORDER BY is invalid in window functions
• Believing SUM() can't be a window function

5. You want to find the difference in salary between each employee and the highest salary in their department using window functions. Which query correctly achieves this?

hard

A. SELECT name, department, salary, MAX(salary) OVER (PARTITION BY department) - salary AS diff FROM employees;

B. SELECT name, department, salary, salary - MAX(salary) OVER (PARTITION BY department) AS diff FROM employees;

C. SELECT name, department, salary, salary - MAX(salary) FROM employees GROUP BY department;

D. SELECT name, department, salary, salary - MAX(salary) FROM employees;

Solution

1. Step 1: Use MAX() as window function partitioned by department

   MAX(salary) OVER (PARTITION BY department) gives highest salary per department for each row.

2. Step 2: Calculate difference as highest salary minus employee salary

   Subtract employee salary from max salary to get positive difference.

3. Step 3: Check options for correct calculation and syntax

   SELECT name, department, salary, MAX(salary) OVER (PARTITION BY department) - salary AS diff FROM employees; correctly subtracts salary from max salary per department; SELECT name, department, salary, salary - MAX(salary) OVER (PARTITION BY department) AS diff FROM employees; reverses subtraction; The remaining options misuse GROUP BY or lack window function.

4. Final Answer:

   SELECT name, department, salary, MAX(salary) OVER (PARTITION BY department) - salary AS diff FROM employees; -> Option A

5. Quick Check:

   MAX() OVER PARTITION BY + subtraction = difference [OK]

Hint: Subtract salary from MAX() OVER partition for difference [OK]

Common Mistakes:

• Reversing subtraction order
• Using GROUP BY instead of window functions
• Omitting PARTITION BY in MAX()