Running total without window functions in SQL - Mini Project: Build & Apply

Running Total Without Window Functions

📖 Scenario: You work at a small retail store that keeps sales data in a simple table. You want to calculate the running total of sales amounts for each day, but your database does not support window functions.

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

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

Write a SQL query that calculates the running total of amount ordered by sale_date without using window functions.

Use a correlated subquery to sum amounts up to the current date.

💡 Why This Matters

🌍 Real World

Calculating running totals is common in sales reports, financial summaries, and inventory tracking when advanced SQL features are not available.

💼 Career

Understanding how to write running totals without window functions helps in working with legacy databases or simpler SQL engines, a useful skill for data analysts and database developers.

1

Create the sales table and insert data

Create a table called sales with columns sale_date of type DATE and amount of type INTEGER. Then insert these exact rows: ('2024-01-01', 100), ('2024-01-02', 150), ('2024-01-03', 200), and ('2024-01-04', 50).

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 the rows.

2

Add a variable for the current date in the query

Define a variable or alias called current_date in your query to represent the date you want to calculate the running total for. This will help in the next step when you sum amounts up to this 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);

-- Define current_date alias in the query
-- Your code here

Need a hint?

Use AS current_date to rename sale_date in the SELECT clause.

3

Write the correlated subquery for running total

Write a SQL query that selects sale_date, amount, and a running total called running_total. Use a correlated subquery that sums amount from sales where sale_date is less than or equal to the outer query's sale_date. Use the alias s1 for the outer query and s2 for the subquery.

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

-- Write the query with correlated subquery for running total
-- Your code here

Need a hint?

Use a subquery inside the SELECT clause that sums amounts from sales s2 where s2.sale_date <= s1.sale_date.

4

Complete the query with ordering and aliasing

Ensure the final query orders the results by sale_date ascending and includes the aliases s1 and s2 as specified. This completes the running total calculation without window functions.

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

-- Finalize the query with ordering and aliases
-- Your code here

Need a hint?

Use ORDER BY s1.sale_date ASC to sort the results by date.