Month-to-Date (MTD) Rolling Sum

SQL coding challenge · Difficulty: medium · +200 XP

Problem

Show a Month-to-Date (MTD) running total of sales that resets at the start of every month.

Tables

Table: daily_sales

| sale_date | sales |
| --- | --- |
| 2024-01-30 | 100 |
| 2024-01-31 | 200 |
| 2024-02-01 | 50 |
| 2024-02-02 | 150 |
| 2024-02-03 | 300 |
| 2024-03-01 | 400 |

Expected Output

| sale_date | sales | mtd_total |
| --- | --- | --- |
| 2024-01-30 | 100 | 100 |
| 2024-01-31 | 200 | 300 |
| 2024-02-01 | 50 | 50 |
| 2024-02-02 | 150 | 200 |
| 2024-02-03 | 300 | 500 |
| 2024-03-01 | 400 | 400 |

• Return: sale_date, sales, mtd_total
• Sort by sale_date ascending
• Function to use: SUM() OVER (PARTITION BY YEAR(sale_date), MONTH(sale_date) ORDER BY sale_date)