Scenario: Brightlane's pricing team is tracking how the true average order value has evolved over time. The running average must reflect the genuine average across all individual orders accumulated to date, not a simple average of daily averages — those two numbers diverge whenever the per-day order counts vary.
Task: Write a query to return each order day, the average order value for that day alone, and the running average order value across every individual order placed from the earliest day through that day.
Assumptions:
- An order day is identified by its date.
- A day's
daily_avg_order_valueis the averagetotal_amountacrossordersplaced on that day. - A day's
running_avg_order_valueis the combinedtotal_amountacross every order placed from the earliest day through that day, divided by the combined order count over the same window — which gives the true average across individual orders, not the average of daily averages.
Output:
- One row per order day present in the data.
- Columns in this order:
order_day,daily_avg_order_value,running_avg_order_value. - Sorted by
order_dayascending.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
DATE_TRUNC('day', ordered_at) AS order_day,
AVG(total_amount) AS daily_avg_order_value,
SUM(SUM(total_amount)) OVER (
ORDER BY
DATE_TRUNC('day', ordered_at) ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) / SUM(COUNT(*)) OVER (
ORDER BY
DATE_TRUNC('day', ordered_at) ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS running_avg_order_value
FROM
orders
GROUP BY
DATE_TRUNC('day', ordered_at)
ORDER BY
order_dayThe shape
A true running average across every individual order is the running total of revenue divided by the running total of order count. Computing AVG(SUM(...) OVER (...)) (an average of daily averages) would weight every day equally regardless of how many orders that day carried; the prompt rules that out. The cumulative numerator and cumulative denominator are computed separately with window functions, then divided once at the SELECT.
Clause by clause
SELECT DATE_TRUNC('day', ordered_at) AS order_day, AVG(total_amount) AS daily_avg_order_valueproduces one row per order day with that day's own average order value. This is the per-day metric the prompt calls for; it is not used to compute the running average.- The
running_avg_order_valueexpression has two windowed pieces.SUM(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('day', ordered_at) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)runs cumulative revenue forward;SUM(COUNT(*)) OVER (ORDER BY DATE_TRUNC('day', ordered_at) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)runs cumulative order count forward. Dividing the two gives the true average across every order placed from the earliest day through the current day. FROM orders GROUP BY DATE_TRUNC('day', ordered_at)aggregates the raw orders to one row per day. AfterGROUP BY, the innerSUM(total_amount)andCOUNT(*)produce the day's revenue and the day's count; the outer windowed sums accumulate those over the ordered day rows.ORDER BY order_daysorts the final output chronologically.
Why this and not AVG(total_amount) OVER (...)
AVG(total_amount) OVER (...) would not be legal here because the query is already aggregating to day level — every reference outside an aggregate or window has to be a grouping column. More importantly, even if rewritten to run on raw orders, AVG OVER with an unbounded-preceding frame would still produce the correct number on this data. The two-stage shape is required because the prompt is structured as "one row per day with both the day's average and the running average," which forces the aggregation to day level. Once aggregated, the average must be reconstructed from the running revenue and running count, because the day rows have lost the per-order detail AVG needs.
The trap
The natural-sounding "running average of daily averages" gives the wrong answer whenever the per-day order counts vary. A day with one $1000 order and the next day with ten $100 orders both have a daily average of $100 and $1000 respectively. Averaging those two daily averages gives $550. Averaging the eleven individual orders gives $182. The two numbers diverge because the second day carries ten times the weight in reality. Running totals of numerator and denominator divided once is what restores correct per-order weighting; averaging averages drops that weighting entirely.
You practiced building a true running average as cumulative-total-over-cumulative-count, rather than averaging daily averages — the two numbers diverge whenever the per-day order counts vary.
