Month on month growth for a region and city

user_7688698 0
Viewed 310

I have a table in the below format -

region  country   revenue  datetime
apac     japan     1000    2002-01-01
apac     china     2000    2001-02-01
apac     japan     1500    2001-01-01
apac     china     3000    2002-02-01
emea     germany   4000    2003-04-01
emea     france    5000    2004-09-01
emea     germany   2000    2004-04-01
emea     france    6000    2005-09-01

I'm trying to write a query which would give the month on month growth for each region and county

I'm using the datetrunc('month',datetime) and the lag() function in postgres to get the previous value, but unable to group by the region and country - when I do so the granularity of the table changes and I'm not getting the desired output.

Below is the query - 

select  region,
country,
  date_trunc('month',datetime) as datum,
  sum(revenue),
         round(1.0-(cast(revenue as NUMERIC)/lag(revenue,1) OVER())*100,1)
from mytable
  GROUP BY datum,region,country,revenue
ORDER BY datum ASC;
lag window-functions postgresql sql
1 Answers

You need partition by:

select region, country,
       date_trunc('month', datetime) as datum,
       sum(revenue),
       round(1.0-(cast(revenue as NUMERIC)/lag(sum(revenue), 1) over (partition by region, country order by date_trunc('month',datetime)))*100,1)
from mytable
group by datum, region, country
order by datum asc;

Also, revenue shouldnot be in the group by.

Or, this is more easily expressed by doing the aggregation first:

select region, county, datum, rev, 
       lag(rev) over (partition by region, country order by datum) as prev_rev
from (select region, country,
             date_trunc('month', datetime) as datum,
             sum(revenue) as rev
      from mytable
      group by datum, region, country
     ) drc
order by datum asc;
user_11440350
Related