Running totals with SUM OVER in SQL - Mini Project: Build & Apply

Calculate Running Totals with SUM OVER

📖 Scenario: You work at a small bookstore that keeps track of daily sales. You want to see how sales add up day by day to understand the total revenue over time.

🎯 Goal: Create a SQL query that calculates the running total of sales amounts by date using the SUM OVER window function.

📋 What You'll Learn

Create a table called sales with columns sale_date (date) and amount (integer).

Insert the exact sales data for these dates and amounts: 2024-01-01 (100), 2024-01-02 (150), 2024-01-03 (200), 2024-01-04 (50), 2024-01-05 (300).

Write a query that selects sale_date, amount, and a running total column called running_total.

Use the SUM(amount) OVER (ORDER BY sale_date) syntax to calculate the running total.

💡 Why This Matters

🌍 Real World

Running totals help businesses track cumulative sales, expenses, or other metrics over time to understand trends and progress.

💼 Career

Knowing how to use window functions like SUM OVER is valuable 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 sale_date as DATE and amount as INTEGER. Then insert these exact rows: (2024-01-01, 100), (2024-01-02, 150), (2024-01-03, 200), (2024-01-04, 50), (2024-01-05, 300).

SQL

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

Need a hint?

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

2

Set up the basic SELECT query

Write a SELECT query that retrieves the columns sale_date and amount from the sales table.

SQL

CREATE TABLE sales (
    sale_date DATE,
    amount INTEGER
);

INSERT INTO sales (sale_date, amount) VALUES
('2024-01-01', 100),
('2024-01-02', 150),
('2024-01-03', 200),
('2024-01-04', 50),
('2024-01-05', 300);

-- Select sale_date and amount from sales
-- Your code here

Need a hint?

Use SELECT sale_date, amount FROM sales to get the data.

3

Add the running total column using SUM OVER

Modify the SELECT query to add a new column called running_total that calculates the running total of amount ordered by sale_date using SUM(amount) OVER (ORDER BY sale_date).

SQL

CREATE TABLE sales (
    sale_date DATE,
    amount INTEGER
);

INSERT INTO sales (sale_date, amount) VALUES
('2024-01-01', 100),
('2024-01-02', 150),
('2024-01-03', 200),
('2024-01-04', 50),
('2024-01-05', 300);

-- Add running_total column using SUM OVER
-- Your code here

Need a hint?

Use the window function SUM(amount) OVER (ORDER BY sale_date) to get the running total.

4

Order the results by sale_date

Add an ORDER BY sale_date clause at the end of the SELECT query to show the results sorted by date.

SQL

CREATE TABLE sales (
    sale_date DATE,
    amount INTEGER
);

INSERT INTO sales (sale_date, amount) VALUES
('2024-01-01', 100),
('2024-01-02', 150),
('2024-01-03', 200),
('2024-01-04', 50),
('2024-01-05', 300);

SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date) AS running_total FROM sales
-- Add ORDER BY sale_date here
-- Your code here

Need a hint?

Use ORDER BY sale_date to sort the results by date.