My question has two parts:
. Firstly, how to adjust the code below to calculate 12 month moving avg, median etc.? As is, the query will return a list of daily, weekly, monthly or annual values (depending on what is specified) but only for a single 'period'. That is, if 'year' is specified, summary stats will be calculated for whole years 2019, 2018, 2017, etc. however back the data goes. If 'month' then summary stats will be only for a given month, like Oct-2019, Sep-2019, Aug-2019, etc... But how to calculate eg. single values for 3 months Aug-2019 to Oct-2019, then Jul-2019 to Sep-2019, etc.?
. Secondly, what would be a generic version of the code to be able to use any time interval + number of periods, like '4 weeks', '6 months', '12 months', '2 years'?
SELECT date_trunc('year', t.time2), -- or hour, day, week, month, year count(1), percentile_cont(0.25) within group (order by t.price) as Q1, percentile_cont(0.5) within group (order by t.price) as Q2, percentile_cont(0.75) within group (order by t.price) as Q3, avg(t.price) as A, min(t.price) as Mi, max(t.price) as Mx FROM my_table AS t GROUP BY 1 ORDER BY date_trunc
Data table consists of a list of individual transactions (date -> time2 as timestamp; and price as bigint).