BBampaa

Lessons › SQL › Go further in SQL: Business questions

SQL lesson

What's the 7-day average of daily sales?

What's the 7-day average of daily sales? New tool: a window frame. OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) means: this row and the 6 rows before it. AVG over that frame is a 7-day average. Early days have fewer than 6 before them, so they average what there is.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

A window frame: this row and the 6 before it

SELECT sale_date,
       revenue,
       AVG(revenue) OVER (
         ORDER BY sale_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS avg_7_days
FROM daily_sales

Result:

sale_daterevenueavg_7_days
2026-03-07720497.86
2026-03-08450502.14

Step by step

  1. One row per day.
    SELECT sale_date, revenue,   AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_days FROM daily_sales
  2. Every day counts.
    SELECT sale_date, revenue,   AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_days FROM daily_sales
  3. AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
    SELECT sale_date, revenue,   AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_days FROM daily_sales

Practice

Try each one in your head first, then open the answer.

1. What's the 7-day average of daily sales? Show sale_date, revenue and avg_7_days.
SELECT sale_date, revenue, AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_days FROM daily_sales
2. What's the 3-day average of daily sales? Show sale_date, revenue and avg_3_days.
SELECT sale_date, revenue, AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3_days FROM daily_sales
3. What's the 7-day total of daily sales? Show sale_date and total_7_days.
SELECT sale_date, SUM(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS total_7_days FROM daily_sales
4. What's the highest daily revenue in the last 5 days, for each day? Show sale_date and best_5_days.
SELECT sale_date, MAX(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS best_5_days FROM daily_sales
5. On which days was revenue above its own 7-day average? Show sale_date, revenue and avg_7_days.
WITH a AS (SELECT sale_date, revenue, AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_days FROM daily_sales) SELECT sale_date, revenue, avg_7_days FROM a WHERE revenue > avg_7_days

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed