Show the 4 months moving average
If you include the City in the GROUP BY in your solution, you will receive the average sold number of the products on the city and the quarter level. So the AVGs are calculated separately over the quarters. I mean, each sold number data will belong to one row of your result, but you need something else.
The rolling means that a data need to belong to more than 1 row of the result somehow. So it should be considered in other records of the result.
E.g. You have the following data:
| Month | numberSold |
|---|---|
| January | 1 |
| February | 2 |
| March | 3 |
| April | 4 |
| May | 5 |
| June | 6 |
The rolling AVG for the first 4 months (J, F, M, A) will be: 2.5.
The rolling AVG for the second 4 months (F, M, A, M) will be: 3.5.
The rolling AVG for the third 4 months (M, A, M, J) will be: 4.5
Hope I helped you to understand the situation better.
My answers:
- If you see "rolling" then that is a typical use case of
window functions. I don't know the reason why you need a "double AVG" usage. -
OVERrepresents that we are talking about awindow function. We need to add it, this is how it works. - Maybe with some sub-queries, but I don't think that is a good approach.
Best!