Description
Write a SQL query to calculate the cumulative sum of sales ordered by date. Return each date with its daily amount and the running total up to that date. **Output columns (in order):** sale_date, amount, cumulative_sum
Table:
sales| Column | Type |
|---|---|
| id | INT |
| sale_date | TEXT (YYYY-MM-DD format) |
| amount | INT |
Examples
Input:
CREATE TABLE sales (id INT, sale_date TEXT, amount INT); INSERT INTO sales VALUES (1, '2023-01-01', 100);Output:
2023-01-01|100|100Explanation:
With only one sale on 2023-01-01 for amount 100, the cumulative sum equals the daily amount (100). The running total starts and ends at 100 since there are no prior sales to add.
Input:
CREATE TABLE sales (id INT, sale_date TEXT, amount INT); INSERT INTO sales VALUES (1, '2023-03-15', 75), (2, '2023-03-10', 200), (3, '2023-03-20', 125), (4, '2023-03-12', 300);Output:
2023-03-10|200|200
2023-03-12|300|500
2023-03-15|75|575
2023-03-20|125|700Explanation:
Sales records inserted out of chronological order must be sorted by date first. The cumulative sum builds sequentially: 200, then 200+300=500, then 500+75=575, finally 575+125=700.
Input:
CREATE TABLE sales (id INT, sale_date TEXT, amount INT); INSERT INTO sales VALUES (1, '2023-05-01', 0), (2, '2023-05-02', 250);Output:
2023-05-01|0|0
2023-05-02|250|250Explanation:
Handles the edge case where a sale amount is zero. The cumulative sum starts at 0 and then adds 250, demonstrating that zero values are included in the calculation.
Constraints
- •
Order by date