Named windows with WINDOW clause in PostgreSQL - Mini Project: Build & Apply

Using Named Windows with WINDOW Clause in PostgreSQL

📖 Scenario: You work in a sales department and want to analyze monthly sales data for different salespeople. You want to calculate running totals and averages over specific time frames without repeating the same window definitions multiple times.

🎯 Goal: Build a SQL query that uses the WINDOW clause to define named windows and then use those named windows in OVER clauses to calculate running totals and averages.

📋 What You'll Learn

Create a table called sales with columns salesperson (text), month (integer), and amount (integer).

Insert the exact sales data for three salespeople over three months.

Define a named window called monthly_window that partitions by salesperson and orders by month.

Use the named window monthly_window to calculate a running total of amount.

Use the named window monthly_window to calculate a running average of amount.

💡 Why This Matters

🌍 Real World

Sales analysts often need to calculate running totals and averages over time for each salesperson to track performance trends.

💼 Career

Knowing how to use named windows in SQL is useful for writing efficient and readable analytical queries in business intelligence and data analysis roles.

1

Create the sales table and insert data

Create a table called sales with columns salesperson (text), month (integer), and amount (integer). Then insert these exact rows: ('Alice', 1, 100), ('Alice', 2, 150), ('Alice', 3, 200), ('Bob', 1, 80), ('Bob', 2, 120), ('Bob', 3, 160), ('Charlie', 1, 90), ('Charlie', 2, 110), ('Charlie', 3, 130).

PostgreSQL

-- Create the sales table
-- Insert the sales data

Need a hint?

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

2

Define a named window with WINDOW clause

Write a SELECT statement from sales and add a WINDOW clause that defines a named window called monthly_window which partitions by salesperson and orders by month.

PostgreSQL

CREATE TABLE sales (
    salesperson TEXT,
    month INTEGER,
    amount INTEGER
);

INSERT INTO sales (salesperson, month, amount) VALUES
('Alice', 1, 100),
('Alice', 2, 150),
('Alice', 3, 200),
('Bob', 1, 80),
('Bob', 2, 120),
('Bob', 3, 160),
('Charlie', 1, 90),
('Charlie', 2, 110),
('Charlie', 3, 130);

SELECT salesperson, month, amount
FROM sales
WINDOW monthly_window AS (PARTITION BY salesperson ORDER BY month);
-- Your code here to define the named window

Need a hint?

Use the WINDOW clause after the FROM clause to define monthly_window.

3

Calculate running total using the named window

Extend the SELECT statement to include a column called running_total that calculates the running total of amount using SUM(amount) OVER monthly_window.

PostgreSQL

CREATE TABLE sales (
    salesperson TEXT,
    month INTEGER,
    amount INTEGER
);

INSERT INTO sales (salesperson, month, amount) VALUES
('Alice', 1, 100),
('Alice', 2, 150),
('Alice', 3, 200),
('Bob', 1, 80),
('Bob', 2, 120),
('Bob', 3, 160),
('Charlie', 1, 90),
('Charlie', 2, 110),
('Charlie', 3, 130);

SELECT salesperson, month, amount, SUM(amount) OVER monthly_window AS running_total
FROM sales
WINDOW monthly_window AS (PARTITION BY salesperson ORDER BY month);
-- Your code here to add running total

Need a hint?

Add SUM(amount) OVER monthly_window AS running_total to the SELECT list.

4

Calculate running average using the named window

Add another column called running_avg that calculates the running average of amount using AVG(amount) OVER monthly_window in the same SELECT statement.

PostgreSQL

CREATE TABLE sales (
    salesperson TEXT,
    month INTEGER,
    amount INTEGER
);

INSERT INTO sales (salesperson, month, amount) VALUES
('Alice', 1, 100),
('Alice', 2, 150),
('Alice', 3, 200),
('Bob', 1, 80),
('Bob', 2, 120),
('Bob', 3, 160),
('Charlie', 1, 90),
('Charlie', 2, 110),
('Charlie', 3, 130);

SELECT salesperson, month, amount, SUM(amount) OVER monthly_window AS running_total, AVG(amount) OVER monthly_window AS running_avg
FROM sales
WINDOW monthly_window AS (PARTITION BY salesperson ORDER BY month);
-- Your code here to add running average

Need a hint?

Add AVG(amount) OVER monthly_window AS running_avg to the SELECT list.