Calculating YoY revenue when not all accounts existed last year

stackoverflow-68322927-calculating-yoy-revenue-when-not-all-accounts-existed-last-year-snowflake.sql

-- https://stackoverflow.com/questions/68322927/calculating-yoy-revenue-when-not-all-accounts-existed-last-year


create or replace table arr_base(account_id varchar, account_name varchar, activity_date date, arr number(32,4));


insert into arr_base (account_id, account_name, activity_date, arr)
values
 ('A','ACCOUNT A','2021-01-31',50)
,('B','ACCOUNT B','2021-01-31',40)
,('A','ACCOUNT A','2020-01-31',40)
,('B','ACCOUNT B','2020-01-31',	35)
,('C','ACCOUNT C','2020-01-31',	30)
,('D','ACCOUNT D','2020-01-31',	30)
;


/*
-- add more data if you want 


insert into arr_base (account_id, account_name, activity_date, arr)
select
n.account_id, n.account_name, n.activity_date, n.arr * uniform(1,4, random()) as arr
from 
(
-- lets add some more data
select   b.account_id, b.account_name, dateadd(year,-(num + 1),  b.activity_date) as activity_date, b.arr
from
arr_base b cross join 
(select seq4() as num from table(generator(rowcount=>10))) t
) n
where n.activity_date not in (select distinct activity_date from arr_base)
order by 1,2,3,4
;



*/



set start_date = '2020-01-01'::timestamp_ltz;
set end_date  = current_date();
set years = (select datediff(years,$start_date, $end_date) +1 );
  
with cte_accounts as (select distinct account_id, account_name from arr_base where activity_date between $start_date and $end_date )
,cte_dates as (select distinct year(activity_Date) as activity_year, activity_Date from arr_base where activity_date between $start_date and $end_date)
,cte_years as ( select seq4() + year($start_date) as txn_year from table(generator(rowcount=> $years)))
,cte_arr as (select account_id, account_name, year(activity_date) as activity_year, SUM( arr) as arr  from arr_base  where activity_date between $start_date and $end_date  group by account_id, account_name, year(activity_date)) 

-- cartesian product
select
dim.activity_year
,dim.account_id
,dim.account_name
,f1.arr as arr_current_year
,f2.arr as arr_next_year
,case when f2.arr is not null and f1.arr is not null then ((f2.arr - f1.arr) / f1.arr) else null end as yoy_arr
from 
(
select 
a.account_id
,a.account_name
,d.activity_year
from 
cte_accounts a cross join
cte_dates d 
) as dim left outer join 
cte_arr  as f1 on dim.account_id = f1.account_id and dim.activity_year = f1.activity_year left outer join 
cte_arr  as f2 on dim.account_id = f2.account_id and (dim.activity_year+1) = f2.activity_year
order by dim.activity_year
,dim.account_id
;