Cloud Kitchen Seven-Day Moving Average — SQL Medium Problem

Problem statement

A cloud kitchen reviews its sales as a 7-day moving window, which smooths out the weekend rush. The kitchen is open every day, and every date from its first order to its last has at least one order.

For each date from the seventh day on (the first order date plus six days), return order_date, week_total — the total amount of all orders on that date and the six dates before it — and week_average — that total divided by 7. Round both to 2 decimal places. Order the rows by order_date. If the orders span fewer than seven days, return no rows.

Tables

Table: KitchenOrder

Primary key: order_id.

One row per order; a date usually has several. amount is the bill in rupees.

Examples

Example 1

KitchenOrder

Output

How to solve Cloud Kitchen Seven-Day Moving Average

There are usually several orders per date, and the window must move by date, not by order. So the first step aggregates to one row per date (daily). Since every date between the first and last order is present, the seven dates ending at a given date are exactly that row and the six rows before it, which is the frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. SUM over that frame is the week's total, and dividing by 7 gives the average — always 7, because the statement defines the average over seven days.

The first six dates have fewer than six days behind them, and their frame is silently shorter, so they are dropped with ROW_NUMBER() … >= 7, which is the same as dates from MIN(order_date) + 6 days on. Rounding happens last, on the exact sums.

Without windows, join the daily totals to themselves on DATEDIFF(d1, d2) BETWEEN 0 AND 6 and sum the matches, or use a correlated SUM over BETWEEN DATE_SUB(date, INTERVAL 6 DAY) AND date. These calendar-based versions would stay correct even if some dates were missing, where ROWS would reach back too far; a RANGE frame over a day number has the same property. The window plan is one sort of the daily rows; the join compares every pair of dates.

Reference solution (MySQL)

WITH daily AS (
  SELECT order_date, SUM(amount) AS day_total
  FROM KitchenOrder
  GROUP BY order_date
), rolling AS (
  SELECT order_date,
         SUM(day_total) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS week_total,
         ROW_NUMBER() OVER (ORDER BY order_date) AS day_no
  FROM daily
)
SELECT order_date, ROUND(week_total, 2) AS week_total, ROUND(week_total / 7, 2) AS week_average
FROM rolling
WHERE day_no >= 7
ORDER BY order_date

Another way

SELECT d1.order_date,
       ROUND(SUM(d2.day_total), 2) AS week_total,
       ROUND(SUM(d2.day_total) / 7, 2) AS week_average
FROM (SELECT order_date, SUM(amount) AS day_total FROM KitchenOrder GROUP BY order_date) d1
JOIN (SELECT order_date, SUM(amount) AS day_total FROM KitchenOrder GROUP BY order_date) d2
  ON DATEDIFF(d1.order_date, d2.order_date) BETWEEN 0 AND 6
WHERE d1.order_date >= DATE_ADD((SELECT MIN(order_date) FROM KitchenOrder), INTERVAL 6 DAY)
GROUP BY d1.order_date
ORDER BY d1.order_date

Another way

SELECT k.order_date,
       ROUND((SELECT SUM(x.amount) FROM KitchenOrder x
              WHERE x.order_date BETWEEN DATE_SUB(k.order_date, INTERVAL 6 DAY) AND k.order_date), 2) AS week_total,
       ROUND((SELECT SUM(x.amount) FROM KitchenOrder x
              WHERE x.order_date BETWEEN DATE_SUB(k.order_date, INTERVAL 6 DAY) AND k.order_date) / 7, 2) AS week_average
FROM (SELECT DISTINCT order_date FROM KitchenOrder) k
WHERE k.order_date >= DATE_ADD((SELECT MIN(order_date) FROM KitchenOrder), INTERVAL 6 DAY)
ORDER BY k.order_date