SQL Interview Question - 5

Consider a table named sales that stores information about sales transactions. The table has the following columns:

• transaction_id: The unique identifier for each transaction.
• transaction_date: The date when the transaction occurred.
• amount: The amount of the transaction.

Write a SQL query to calculate the cumulative sum of sales amounts for each day, ordered by transaction date.

Sample Table:

CREATE TABLE sales (
    transaction_id INT,
    transaction_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (transaction_id, transaction_date, amount) VALUES
(1, '2024-03-01', 100),
(2, '2024-03-01', 150),
(3, '2024-03-02', 200),
(4, '2024-03-03', 50),
(5, '2024-03-03', 300),
(6, '2024-03-04', 100);

Solution:

SELECT 
    transaction_date,
    amount,
    SUM(amount) OVER (ORDER BY transaction_date) AS cumulative_sum
FROM 
    sales
ORDER BY 
    transaction_date;

 

Share your alternate solution in comments.