SQL · medium · Reported at Amazon

Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.

Asked in Data Analyst interviews, in the SQL round.

Short answer

Aggregate orders to one row per day, then use SUM(daily_revenue) over a six-day date interval plus the current day. Use a date spine when the SQL engine cannot frame by interval.

Sample data

These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.

orders

How to answer it

Definition

For every date, the total revenue over that day and the six days before it. The trap is that a window frame counts ROWS, and a table of orders does not have exactly one row per day.

Approach

• Aggregate to one row per day first. Now a row genuinely means a day.
• Window over that daily table with an explicit frame.
• If a day can be missing entirely, join a date spine first, or use a RANGE frame on the date.

Example

WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM   orders
  GROUP BY order_date
)
SELECT order_date,
       SUM(revenue) OVER (
         ORDER BY order_date
         RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
       ) AS rolling_7d
FROM daily
ORDER BY order_date;

Output

order_date | rolling_7d
-----------+-----------
2026-03-01 |        65
2026-03-02 |       125
2026-03-03 |       155
2026-03-04 |       200
2026-03-05 |       280
2026-03-06 |       300
2026-03-08 |       290
2026-03-09 |       265
2026-03-10 |       340
2026-03-11 |       345

ROWS versus RANGE

ROWS BETWEEN 6 PRECEDING means the six previous ROWS. If 3 January has no orders and is missing from the table, that window silently reaches back to 28 December and the number is a seven-row total, not a seven-day one.

-- gap-proof: an interval frame, on engines that support it
SUM(revenue) OVER (
  ORDER BY order_date
  RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
)

Key points

• Aggregate to a day grain before windowing, or you get a rolling sum over the last seven ORDERS.
• The first six days are partial by definition. Decide whether to show them or NULL them, and say which.
• ROWS counts rows, RANGE counts values. With gaps in the dates those are different answers.
• A date spine plus ROWS is the portable fix when RANGE with an interval is unavailable.

FAQ

• Do I need PARTITION BY? Only for a rolling total per user or per region. For a single company-wide series, no.
• Should the current day be included? "7-day rolling" almost always includes today. Confirm it rather than assume.
• What if two orders share a timestamp? Aggregating to the day first makes that irrelevant, which is another reason to do it.

The 30-second answer

I would aggregate to one row per day first, then take a windowed SUM ordered by date with a RANGE frame covering the prior six days and the current day.

The part I would call out is why the daily aggregation is not optional. A ROWS frame counts rows, not days, so windowing straight over the orders table gives the last seven orders, and if a day has no orders the window quietly reaches back further than a week. If the engine supports a RANGE frame with an interval I would use that instead, since it is defined in days and is immune to gaps. I would also say the first six days are partial and decide whether to show them or null them out.

In the interview

• What they are checking: that you aggregate before windowing, and that you notice the gap between "7 rows" and "7 days".
• Common mistake: skipping the daily CTE and windowing over raw orders, which gives a rolling sum over the last seven orders, not the last seven days.

Practice this for real

• Check your resume against a Data Analyst job description, free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews, two free, no card