Difficulty: Hard | Concepts: Window Functions, CASE, SUM OVER(), PARTITION BY, ORDER BY

Introduction

Running totals are one of the most common SQL interview topics for Data Engineers and Data Analysts. This problem tests whether you understand how window functions process data while preserving individual rows.

Problem Statement

You are given a table containing customer transactions. Each transaction is either a Credit or a Debit. Calculate the running account balance after every transaction for each customer. If multiple transactions occur on the same day, process them using the Transaction_ID.

Dataset

CREATE TABLE Transactions
(
Transaction_ID INT,
Customer_ID INT,
Transaction_Date DATE,
Transaction_Type VARCHAR(20),
Amount INT
);

INSERT INTO Transactions VALUES
(1,101,'2025-01-01','Credit',500),
(2,101,'2025-01-02','Debit',100),
(3,101,'2025-01-03','Credit',200),
(4,101,'2025-01-03','Debit',50),
(5,102,'2025-01-01','Credit',1000),
(6,102,'2025-01-02','Debit',300),
(7,102,'2025-01-05','Credit',150),
(8,103,'2025-01-04','Credit',400),
(9,103,'2025-01-05','Debit',120);

Expected Output

Transaction ID	Customer	Balance
1	101	500
2	101	400
3	101	600
4	101	550

SQL Solution

SELECT
    Transaction_ID,
    Customer_ID,
    Transaction_Date,
    Transaction_Type,
    Amount,
    SUM(
        CASE
            WHEN Transaction_Type='Credit' THEN Amount
            ELSE -Amount
        END
    ) OVER (
        PARTITION BY Customer_ID
        ORDER BY Transaction_Date, Transaction_ID
    ) AS Running_Balance
FROM Transactions
ORDER BY Customer_ID, Transaction_Date, Transaction_ID;

Explanation

The CASE expression converts debit transactions into negative values while keeping credit transactions positive. The window function calculates a cumulative sum separately for every customer using PARTITION BY Customer_ID. Within each customer, transactions are processed in chronological order using ORDER BY Transaction_Date, Transaction_ID, ensuring deterministic results even when multiple transactions occur on the same day.

Visual Representation

Conclusion

Running totals are a practical application of SQL window functions and are frequently used in banking, finance, inventory, and reporting systems. Understanding how SUM() OVER(), PARTITION BY, and ORDER BY work together is essential for solving these types of interview questions efficiently.