3-Day Moving Average of Pages Viewed

A web analytics pipeline ingests raw page-view events into a `visits` table. Each row represents a single visitor session on a given date with a count of pages viewed (`num_pages`). To smooth out daily volatility in engagement metrics, the team wants a 3-day moving average of total pages viewed. **Context:** Moving averages are a standard smoothing technique in time-series reporting for dashboards. The key engineering decision here is whether to use a calendar-day window (actual date ranges) or a row-based window (preceding N rows). This problem uses a **row-based window** — if dates have gaps, the window still spans the previous 2 data points plus the current row, not 3 calendar days. **Calculation:** 1. Aggregate `SUM(num_pages)` per `visit_date` across all visitors 2. Apply `AVG(daily_pages) OVER (ORDER BY visit_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)` to compute the moving average 3. Round the result to 2 decimal places **Key Rules:** - The moving average window is row-based: day 1 uses 1 row, day 2 uses 2 rows, day 3+ uses 3 rows - Multiple visitors on the same date are aggregated first (daily total), then the window function is applied - Dates with no visits are not backfilled — they simply won’t appear in the output - Round `moving_avg_3day` to 2 decimal places **Output:** Return columns: `visit_date`, `daily_pages` (total pages for that day), `moving_avg_3day` (rounded to 2 decimal places), ordered by `visit_date` ascending.