Month-to-Date (MTD) Rolling Sum

SQL coding challenge · Difficulty: medium · Topic: Window Functions · +200 XP

Problem

A finance dashboard shows month-to-date sales: next to each day, everything

booked so far in that calendar month. On the first of the month the figure

starts again from zero — nobody wants January's total still climbing in March.

Engine: queries run on MySQL 8.0, so use MySQL-compatible syntax and functions.

Tables

daily_sales — one row per day.

| column | type | description |
| --- | --- | --- |
| sale_date | DATE | the day the sales were recorded |
| sales | INT | sales taken that day |

Return every row of daily_sales, with one extra column:

including that day

Sort the result by sale_date ascending.

The total restarts every month — and every year

This is a running total, not a fixed-width window: it accumulates from the start

of the month and keeps growing until the month ends, however many days that

takes. Nothing drops out of it.

What makes it reset is the grouping, and the grouping needs **both the month and

the year**. January 2025 is not a continuation of January 2024 — it is its own

month and starts from zero. Grouping on the month alone silently merges every

January in the table into one running total, which is correct-looking on a

dataset that happens to cover a single year and wrong on any real history.

A day with zero sales still gets a row, and the total simply does not move that

day.

Example input

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 |
| 2025-01-15 | 700 |
| 2025-01-16 | 50 |

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 |
| 2025-01-15 | 700 | 700 |
| 2025-01-16 | 50 | 750 |

January 2024 reaches 300 and then stops — 2024-02-01 begins again at 50,

its own first day. March restarts once more at 400.

2025-01-15 is the row that matters. It is a January, like the first two

rows, but a different year — so it starts from 700, not from January 2024's

300 plus 700. A total grouped on the month alone would report 1000 here.

What this SQL challenge teaches you

“Month-to-Date (MTD) Rolling Sum” is a medium-level SQL challenge focused on Window Functions. Working through it gives you hands-on practice with Window Functions — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.

How to approach it

If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:

  1. Every row stays in the result and each carries a total derived from its neighbours, so this is a window function rather than a GROUP BY.
  2. A running total is SUM(col) OVER (ORDER BY ...) with no frame clause -- it accumulates everything up to the current row. The only question left is what makes it start again.
  3. PARTITION BY is what resets it. The window restarts whenever the partition key changes, so the key has to identify the calendar month.
  4. Identify the month by BOTH its year and its month number. Keying on the month number alone merges January 2024 with January 2025 into one running total -- which looks right on data covering a single year, and is wrong on anything longer.
  5. Check your answer on a table spanning two years. If the first day of a January picks up the previous January's total, the year is missing from the partition.

Where this comes up

Variations of this problem have been reported in interviews at Amazon. Interviewers use it to check whether you can express the logic cleanly and reason about correctness on edge cases such as ties, nulls and empty groups.

How to practise it on PySpark.in

Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.

Related SQL challenges

Helpful resources

Frequently asked questions

Do I need to install Spark or a database to solve this?

No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.

Is this challenge free?

Yes - the problem, the sample dataset, the hints and unlimited test runs are free.

What level is it?

It is rated medium and covers Window Functions.

Solve this challenge free on PySpark.in