revenue average per current month, last 3, 6 and 12 months, PostgreSQL

revenue average per current month, last 3, 6 and 12 months

--PostgreSQL 9.6
--'\\' is a delimiter

select version() as postgresql_version;

-- revenue average per current month, last 3, 6 (and 12) months
-- like requested here:
-- http://www.dbforums.com/showthread.php?1711202-Trailing-Rolling-X-Month-Average-in-Current-Month-Query&p=6648593

-- create table
create table location_revenue_month (
    location varchar(20),
    revenue money,
    revenue_month date);         --date is more than month, very ok, 
                                 --since we use it for calculation of months
    
    
-- generate data
-- data model and data are not aware about uniqueness of location and time in this example, it's up to You
insert into location_revenue_month
values ('a', 20, '2017-06-01'),
       ('b', 40, '2017-06-01'),
       ('c', 60, '2017-06-01');
       
insert into location_revenue_month
select location, revenue+(random()*80)::numeric::money, revenue_month - interval '1 month'
from location_revenue_month;

insert into location_revenue_month
select location, revenue+(random()*80)::numeric::money, revenue_month - interval '2 month'
from location_revenue_month;

insert into location_revenue_month
select location, revenue+(random()*80)::numeric::money, revenue_month - interval '4 month'
from location_revenue_month;

insert into location_revenue_month
select location, revenue+(random()*80)::numeric::money, revenue_month - interval '8 month'
from location_revenue_month;

-- control select
select location, count(*),sum(revenue) from location_revenue_month group by location;
--select * from location_revenue_month ;

-- first select by current month
select location, revenue from location_revenue_month
 where revenue_month = date_trunc('month', current_date);

-- and it's a bad idea to just list "current" data, 
--     without giving the value of what was "current" when statement was run
--     so let's add this column too
-- first select by current month
select location, revenue, revenue_month::date as current_month from location_revenue_month
 where revenue_month = date_trunc('month', current_date); 
                                           -- use any other date parameter instead of current_date function

-- first select by current month plus 3 month average from before
-- !!! please consider left outer join if data isn't avalable for all location in time span !!!
select c.location, 
       c.revenue as current_revenue, 
       avg(c3.revenue::numeric)::money as three_month_revenue,
       c.revenue_month::date 
  from location_revenue_month c 
  join location_revenue_month c3      --<<< change to left join according to data "situation"
    on c.location = c3.location
 where c.revenue_month = date_trunc('month', current_date)
   and c3.revenue_month between  date_trunc('month', current_date)- interval '2 month'
                            and  date_trunc('month', current_date)
 group by c.location, c.revenue, c.revenue_month::date;

-- now let's build the next column
-- first select by current month, 3 month average, 6 month avarage
select c.location, 
       c.revenue as current_revenue, 
       avg(c3.revenue::numeric)::money as three_month_revenue,
       avg(c6.revenue::numeric)::money as six_month_revenue,
       c.revenue_month::date 
  from location_revenue_month c 
  join location_revenue_month c3
    on c.location = c3.location
  join location_revenue_month c6
    on c.location = c6.location    
 where c.revenue_month = date_trunc('month', current_date)
   and c3.revenue_month between  date_trunc('month', current_date)- interval '2 month'
                            and  date_trunc('month', current_date)
   and c3.revenue_month between  date_trunc('month', current_date)- interval '5 month'
                            and  date_trunc('month', current_date)
 group by c.location, c.revenue, c.revenue_month::date;

-- now You will be able to add 12 month column
-- first select by current month, 3 month average, 6 month avarage, finally 12 month avarage

-- drop table 
drop table location_revenue_month;

-- to ne honest, I didn't check back the numbers :)


run \| edit \| history \| help	0