7-Day Rolling Purchase Amount by Customer

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

Problem

A retail team wants to see how much each customer has been spending lately,

not in total. For every order, report the customer's rolling spend across that

order and their six previous orders.

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

Tables

orders — one row per order. For any one customer, order_date is unique.

| column | type | description |
| --- | --- | --- |
| customer_id | INT | the customer who placed the order |
| order_date | DATE | the day the order was placed |
| amount | INT | order value; negative on a refund |

Return every row of orders, with one extra column:

6 orders before it, for that same customer

Sort the result by customer_id, then order_date.

Seven orders, not seven days

This is the part worth slowing down on. The window is 7 rows wide, and rows

are that customer's orders — it is *not* a seven-day stretch of the calendar.

If a customer orders on the 1st, then not again until the 20th, then again in

April, those are still three consecutive rows and they all sit in the same

window. The gap between them does not matter and does not empty the window. A

calendar-based window would give a different answer on data like that, and the

tests include exactly that case.

Until a customer has seven orders the window is simply shorter — their first

order reports its own amount, and nothing is padded or left NULL.

Each customer is counted alone

The window resets per customer. One customer's orders never enter another's

total, however the rows happen to be interleaved in the table.

Refunds subtract

A negative amount is a refund and it lowers the rolling total normally — this

is a straight sum, so the rolling figure can go negative.

Example input

orders

| customer_id | order_date | amount |
| --- | --- | --- |
| 1 | 2024-01-01 | 100 |
| 1 | 2024-01-02 | 200 |
| 1 | 2024-01-03 | 150 |
| 1 | 2024-01-04 | 300 |
| 1 | 2024-01-05 | 250 |
| 1 | 2024-01-06 | 180 |
| 1 | 2024-01-07 | 220 |
| 1 | 2024-01-08 | 90 |
| 2 | 2024-01-02 | 300 |
| 2 | 2024-01-04 | 250 |
| 2 | 2024-01-06 | 100 |

Expected output

| customer_id | order_date | amount | rolling_7day_amount |
| --- | --- | --- | --- |
| 1 | 2024-01-01 | 100 | 100 |
| 1 | 2024-01-02 | 200 | 300 |
| 1 | 2024-01-03 | 150 | 450 |
| 1 | 2024-01-04 | 300 | 750 |
| 1 | 2024-01-05 | 250 | 1000 |
| 1 | 2024-01-06 | 180 | 1180 |
| 1 | 2024-01-07 | 220 | 1400 |
| 1 | 2024-01-08 | 90 | 1390 |
| 2 | 2024-01-02 | 300 | 300 |
| 2 | 2024-01-04 | 250 | 550 |
| 2 | 2024-01-06 | 100 | 650 |

Customer 1's 8th order is where the window first forgets something. The

total goes from 1400 down to 1390 even though a 90 order was just added,

because the opening 100 has now fallen out the back. A total that never

forgets would read 1490 and could only ever climb.

Customer 2 orders every other day and only three times. Their window is never

full, so their figures are just a running total — 300, 550, 650 — and the

two-day gaps change nothing.

What this SQL challenge teaches you

“7-Day Rolling Purchase Amount by Customer” 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 order stays in the result and gains a column, so this is a window function over a partition -- not a GROUP BY, which would collapse the rows you are being asked to return.
  2. Partition by customer so each customer's window is built from their own orders only, and order that partition by date so 'the previous six orders' means something.
  3. Without a frame, a SUM() window over an ordered partition runs from the start of the partition to the current row -- a total that never forgets. You need to bound the back edge of it.
  4. Count the rows in the frame carefully: seven orders means the current one plus six behind it, so the offset is 6, not 7. Getting this wrong shifts every total from the eighth order onward.
  5. Use a ROWS frame, not a RANGE with a day interval. The window is seven ORDERS wide -- calendar gaps between them are irrelevant, and a customer who goes quiet for a month still has their previous orders in the window.
  6. Short windows need no special handling: before a customer has seven orders the frame is just shorter, and the sum of what is there is the right answer.

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