Installs by Month_ Rahil

set @start := '2018-12-31 00:00';
set @start2 := '2019-11-1 00:00';

select
month(d.created_at + interval '8' hour) as month,
count(distinct case when d.state = 'active' then d.uuid end) as Active_installs_2019,
count(distinct case when d.state = 'badToken' then d.uuid end) as badToken_installs_2019,
count(distinct case when d.state = 'duplicate' then d.uuid end) as duplicate_installs_2019,
count(distinct case when d.state = 'logout' then d.uuid end) as logout_installs_2019

from devices d
where d.created_at + interval '8' hour >= @start
and d.created_at + interval '8' hour <= @start2
group by 1 ;

set @start3 := '2017-12-31 00:00';
set @start4 := '2019-1-1 00:00';

select
month(d.created_at + interval '8' hour) as month,
count(distinct case when d.state = 'active' then d.uuid end) as Active_installs_2018,
count(distinct case when d.state = 'badToken' then d.uuid end) as badToken_installs_2018,
count(distinct case when d.state = 'duplicate' then d.uuid end) as duplicate_installs_2018,
count(distinct case when d.state = 'logout' then d.uuid end) as logout_installs_2018
from devices d
where d.created_at + interval '8' hour >= @start3
and d.created_at + interval '8' hour <= @start4
group by 1 ;