Taco Truck Weekly Rolling Revenue

A taco truck logs every order in truck_orders. The owner wants a smoothed view of revenue: for each day, the revenue of the 7-day window ending on that day (that day plus the six calendar days before it).

• A day can have many orders: combine them first. Days with no orders (the truck was closed) count as zero revenue inside a window.
• Report a row only for days that have at least one order and fall at least 6 days after the truck's first order date, so every reported window is fully covered by history.

Return order_date, week_revenue (window total, rounded to 2 decimals) and week_avg (window total divided by 7, rounded to 2 decimals). Order by order_date ascending. If no day qualifies, return no rows.

Tables

truck_orders — One row per order taken at the truck window.

Example

truck_orders

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Subqueries & CTEs · problem 33 of 70 · SQLite

The idea

First aggregate to one row per day with GROUP BY order_date. Without this step a day with five orders occupies five slots in any row-based window.

Next, build each window by joining the daily table to itself: for a report day d, the matching rows w are those with w.order_date BETWEEN date(d.order_date, '-6 days') AND d.order_date. Because the condition is on dates, closed days simply contribute nothing, which is exactly a zero. Summing per report day gives the window total, and dividing by 7.0 gives a true seven-day average (the .0 also avoids integer division).

Finally keep only report days on or after the first order date plus six days. The alternative does the same with a window function: RANGE BETWEEN 6 PRECEDING AND CURRENT ROW ordered by julianday(order_date) measures the frame in days rather than rows. A ROWS frame would silently count the last seven trading days instead.

Solution

WITH daily AS (
  SELECT order_date, SUM(total) AS revenue
  FROM truck_orders
  GROUP BY order_date
)
SELECT
  d.order_date,
  ROUND(SUM(w.revenue), 2)       AS week_revenue,
  ROUND(SUM(w.revenue) / 7.0, 2) AS week_avg
FROM daily AS d
JOIN daily AS w
  ON w.order_date BETWEEN date(d.order_date, '-6 days') AND d.order_date
WHERE d.order_date >= (SELECT date(MIN(order_date), '+6 days') FROM truck_orders)
GROUP BY d.order_date
ORDER BY d.order_date;

Another way

WITH daily AS (
  SELECT order_date, SUM(total) AS revenue
  FROM truck_orders
  GROUP BY order_date
),
windowed AS (
  SELECT
    order_date,
    SUM(revenue) OVER (
      ORDER BY julianday(order_date)
      RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS week_sum,
    MIN(order_date) OVER () AS first_day
  FROM daily
)
SELECT order_date,
       ROUND(week_sum, 2)       AS week_revenue,
       ROUND(week_sum / 7.0, 2) AS week_avg
FROM windowed
WHERE order_date >= date(first_day, '+6 days')
ORDER BY order_date;

Why common answers fail

Slides the window over individual orders instead of daily totals, so busy days push real days out of the window and dates repeat in the output.

WITH w AS (
  SELECT order_date,
         SUM(total) OVER (ORDER BY order_date, order_id ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS s,
         ROW_NUMBER() OVER (ORDER BY order_date, order_id) AS rn
  FROM truck_orders
)
SELECT order_date, ROUND(s, 2) AS week_revenue, ROUND(s / 7.0, 2) AS week_avg
FROM w WHERE rn >= 7 ORDER BY order_date, rn;

Uses a ROWS frame over daily totals, which counts the last seven days that had sales rather than seven calendar days, so closed days stretch the window.

WITH daily AS (
  SELECT order_date, SUM(total) AS revenue FROM truck_orders GROUP BY order_date
), w AS (
  SELECT order_date,
         SUM(revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS s,
         ROW_NUMBER() OVER (ORDER BY order_date) AS rn
  FROM daily
)
SELECT order_date, ROUND(s, 2) AS week_revenue, ROUND(s / 7.0, 2) AS week_avg
FROM w WHERE rn >= 7 ORDER BY order_date;

Uses AVG over the days present in the window, which divides by fewer than 7 whenever the truck was closed on some of those days.

WITH daily AS (
  SELECT order_date, SUM(total) AS revenue FROM truck_orders GROUP BY order_date
)
SELECT d.order_date,
       ROUND(SUM(w.revenue), 2) AS week_revenue,
       ROUND(AVG(w.revenue), 2) AS week_avg
FROM daily AS d
JOIN daily AS w ON w.order_date BETWEEN date(d.order_date, '-6 days') AND d.order_date
WHERE d.order_date >= (SELECT date(MIN(order_date), '+6 days') FROM truck_orders)
GROUP BY d.order_date ORDER BY d.order_date;

What the interviewer is checking

The interviewer checks whether you aggregate to the right grain before windowing and whether your window is measured in calendar days or in rows.

Mistakes to avoid

• Windowing over raw orders instead of daily totals.
• Using ROWS BETWEEN 6 PRECEDING, which ignores missing days.
• Averaging over the days that had sales instead of dividing by 7.
• Starting the report at the 7th row instead of the 7th calendar day.

Follow-ups to expect

• Show a 28-day window as well in the same query (a second join condition or a second RANGE window).
• Include closed days in the output with zero revenue (generate a calendar with a recursive CTE and LEFT JOIN).
• Compare each window to the same window a week earlier (LAG over the windowed result, 7 rows back on a full calendar).

In real work

Rolling 7-day revenue and active-user counts are the standard way to smooth out weekday effects on dashboards; getting the frame wrong on missing days is a common reporting bug.

Practice next