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

Need a 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

Need a 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

Need a 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

Need a hint?

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