| USER_ID | DATE | PRICE |
|---|---|---|
| 123456 | 2017-10-08 14:23:56 | 7 |
| 152648 | 2017-10-15 18:14:08 | 2 |
| 958462 | 2017-11-15 11:30:29 | 8 |
| 112233 | 2017-11-15 11:33:11 | 6 |
| 124578 | 2017-12-18 10:13:09 | 6 |
| 326598 | 2017-12-20 8:56:20 | 3 |
I'm trying to get an output of average price and rolling average price by month.
I can seem to get one or the other but not both.
Output I want:
| Month | Price | Rolling Price |
|---|---|---|
| 12/1/2017 | Number | Number |
| 11/1/2017 | Number | Number |
CodePudding user response：
You can get the average for each month in a derived table and use it to get the rolling average:
select Month,
price,
avg(price) over(order by Month rows unbounded preceding)
from
(select date_trunc('month', DATE) as Month,
avg(PRICE) as Price
from table_name
group by date_trunc('month', DATE)) t ;