Write a query using a CTE to calculate a 7-day moving average of website visits.

Answer

Use a window frame that spans the current row and the six preceding rows to average visits over a rolling 7-day window.

Solution

WITH daily AS (
  SELECT visit_date, COUNT(*) AS daily_visits
  FROM WebsiteVisits
  GROUP BY visit_date
)
SELECT visit_date, daily_visits,
       ROUND(AVG(daily_visits) OVER (
         ORDER BY visit_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg_7day
FROM daily
ORDER BY visit_date;

Step by Step

1. The daily CTE first collapses raw visit rows into one total per calendar day.
2. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW defines a frame of exactly 7 rows: the current day plus the six immediately before it.
3. AVG(daily_visits) computed over that frame gives a genuine rolling 7-day average that slides forward one day at a time.

Alternative: RANGE Instead of ROWS (Date-Based Instead of Row-Count-Based)

SELECT visit_date, daily_visits,
       ROUND(AVG(daily_visits) OVER (
         ORDER BY visit_date
         RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg_7day
FROM daily;

RANGE with a date interval includes every row whose date value falls within the last 6 days - correct even if some calendar days are entirely missing from the data (no visits that day). ROWS BETWEEN 6 PRECEDING always grabs exactly 6 prior rows regardless of whether there are date gaps between them, which can silently produce a window that spans more than 7 actual calendar days if data is missing for some days.

Key Exam Traps

• ROWS vs RANGE is the central trap in this question: If the underlying data has missing days (no visits recorded at all on some dates), ROWS BETWEEN 6 PRECEDING silently includes rows from further back in time than 7 actual calendar days - RANGE with a date interval is the more correct choice when gaps are possible.
• The first 6 days of data have fewer than 7 rows available in their window - AVG() still computes correctly over however many rows actually exist in the frame (it doesn't error or return NULL), which is expected, not a bug.
• Pre-aggregating before windowing: Applying the moving average window function directly to raw visit-level rows (instead of first aggregating to one row per day) would compute a rolling average of individual visits, not of daily totals - a completely different, incorrect result.