3-month rolling revenue
SQL
Hard
3 views
Problem Description
Create month-wise revenue and also show a rolling 3-month total. First aggregate by month, then apply a window sum over the last 3 months.
Sample Test Case
Input:
Orders(order_date, total_amount)
Constraints
Assume PostgreSQL-style DATE_TRUNC
Official Solution
SELECT month, revenue, SUM(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3m FROM (SELECT DATE_TRUNC('month', order_date) AS month, SUM(total_amount) AS revenue FROM Orders GROUP BY DATE_TRUNC('month', order_date)) m ORDER BY month;
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!