Monthly Sales with Running Total
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given a table of sales records. Each record has an id, a sale date, and an amount. Write a SQL query to return for each calendar month: the total sales amount for that month (monthly_total), and the cumulative running total of all sales up to and including that month (running_total). Return the result ordered by month ascending. Table: Sales
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| sale_date | DATE | Date of the sale |
| amount | DECIMAL | Sale amount |
Database Schema (Inferred)
Sales
| Column Name | Example Value |
|---|---|
| id | 1 |
| sale_date | 2023-01-10 |
| amount | 500 |
Example
Sales
| id | sale_date | amount |
|---|---|---|
| 1 | 2023-01-10 | 500 |
| 2 | 2023-01-25 | 300 |
| 3 | 2023-02-05 | 700 |
| 4 | 2023-02-20 | 200 |
| 5 | 2023-03-15 | 900 |
Output
| month | monthly_total | running_total |
|---|---|---|
| 2023-01 | 800 | 800 |
| 2023-02 | 900 | 1700 |
| 2023-03 | 900 | 2600 |
Explanation:
January has two sales totalling 800. February has two totalling 900; running total becomes 1700. March has 900; running total becomes 2600.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
Sales
| id | sale_date | amount |
|---|---|---|
| 1 | 2023-01-10 | 500 |
| 2 | 2023-01-25 | 300 |
| 3 | 2023-02-05 | 700 |
| 4 | 2023-02-20 | 200 |
| 5 | 2023-03-15 | 900 |
Output
| month | monthly_total | running_total |
|---|---|---|
| 2023-01 | 800 | 800 |
| 2023-02 | 900 | 1700 |
| 2023-03 | 900 | 2600 |