intermediateTop 30 Scenario-Based Questions
Write a query to calculate a 3-month moving average and year-to-date cumulative total alongside each monthly sales row.
SELECT month_date, revenue, AVG(revenue) OVER (ORDER BY month_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3m, SUM(revenue) OVER (PARTITION BY DATE_PART('year', month_date) ORDER BY month_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ytd_cumulative FROM monthly_sales ORDER BY month_date; Moving average: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = current + 2 prior rows (
Ready to master this question?
Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.
Sign in to generate a response