OVER clause with ORDER BY in SQL - Mini Project: Build & Apply

Using the OVER Clause with ORDER BY in SQL

📖 Scenario: You work in a sales department and have a table of monthly sales data for different salespeople. You want to calculate a running total of sales for each salesperson ordered by month.

🎯 Goal: Build a SQL query that uses the OVER clause with ORDER BY to calculate a running total of sales per salesperson ordered by month.

📋 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.

Write a query that selects salesperson, month, amount, and a running total of amount using SUM(amount) OVER (PARTITION BY salesperson ORDER BY month).

Order the final query result by salesperson and month.

💡 Why This Matters

🌍 Real World

Running totals are common in sales reports, financial statements, and time series analysis to track cumulative progress over time.

💼 Career

Knowing how to use window functions like OVER with ORDER BY is essential for data analysts and database developers to write efficient and insightful queries.

1

Create the sales table and insert data

Create a table called sales with columns salesperson (text), month (integer), and amount (integer). 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).

SQL

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

Need a hint?

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

2

Add a helper variable for partitioning

Define a variable or alias called partition_column that will represent the salesperson column for use in the window function partitioning.

SQL

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);

-- Define partition_column as salesperson
-- Your code here

Need a hint?

In SQL, you don't create variables like in programming languages. Instead, you use the column name directly in the PARTITION BY clause.

3

Write the query with OVER clause and ORDER BY

Write a SQL query that selects salesperson, month, amount, and a running total called running_total which uses SUM(amount) OVER (PARTITION BY salesperson ORDER BY month).

SQL

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);

-- Write the query selecting salesperson, month, amount, and running_total
-- Your code here

Need a hint?

Use SUM(amount) OVER (PARTITION BY salesperson ORDER BY month) to calculate the running total.

4

Order the query result by salesperson and month

Add an ORDER BY clause to the query to sort the results by salesperson and then by month.

SQL

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 (PARTITION BY salesperson ORDER BY month) AS running_total
FROM sales
-- Add ORDER BY salesperson, month
-- Your code here

Need a hint?

Use ORDER BY salesperson, month at the end of the query to sort the results.