Calculating quarterly moving average of revenue in SQL
Question
Suppose you're working with a large conglomerate and are trying to aggregate revenue across all divisions to highlight the overall trend. The table you're querying off of is called allRevenue, sample schema is shown below.
Table: allRevenue
| date | revenue | division |
|---|---|---|
| 2018-01-20 | 15000000 | chemicals |
| 2019-02-21 | 120000 | agriculture |
| 2019-04-09 | 390900 | pharma |
Given the above, can you write a SQL query that calculates 4-quarter rolling average revenue, cut by each quarter? The output query should only have 2 columns: 'quarter' and 'revenue' (where revenue is the trailing 4-quarter average). You can assume you have access to a function similar to this.