SQL · medium · Asked 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

Use SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

How to answer it

A rolling window over an ordered daily series. Aggregate to the day first, then apply the window; the frame is the part people get wrong.

WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  GROUP BY order_date
)
SELECT
  order_date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7d
FROM daily
ORDER BY order_date;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is the current row plus the six before it: seven rows. But rows are not days. If a date has no orders, there is no row for it, and the window quietly reaches back eight or nine calendar days. The honest version joins to a date spine first so every day exists (with 0 revenue), or uses RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW, which Postgres supports for date ordering.

• 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.

Related questions

• Write a query to find the second highest salary from an Employees table.
• Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
• Calculate the median order value using SQL.
• Find the top 3 products by revenue within each category.
• Compute month-over-month user retention from an events table.
• Find users who logged in 3 or more consecutive 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