Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
select free.month, free.Free, business.Business, team.Team,
pro.Pro, legacy.Legacy
from
(select month,
count(distinct user_id) as Free
from hive.aggregations.arr_changes_per_user_per_month
where month >= date_parse('2017-09-01', '%Y-%m-%d')
and month < date_parse('2018-07-01', '%Y-%m-%d')
and plan_simple = 'Free'
group by month
order by month asc) free
inner join
(select month,
count(distinct user_id) as Business
from hive.aggregations.arr_changes_per_user_per_month
where month >= date_parse('2017-09-01', '%Y-%m-%d')
and month < date_parse('2018-07-01', '%Y-%m-%d')
and plan_simple = 'Business'
group by month
order by month asc) business
on free.month = business.month
inner join
(select month,
count(distinct user_id) as Team
from hive.aggregations.arr_changes_per_user_per_month
where month >= date_parse('2017-09-01', '%Y-%m-%d')
and month < date_parse('2018-07-01', '%Y-%m-%d')
and plan_simple = 'Team'
group by month
order by month asc) team
on free.month = team.month
inner join (select month,
count(distinct user_id) as Pro
from hive.aggregations.arr_changes_per_user_per_month
where month >= date_parse('2017-09-01', '%Y-%m-%d')
and month < date_parse('2018-07-01', '%Y-%m-%d')
and plan_simple = 'Pro'
group by month
order by month asc) Pro
on free.month = pro.month
inner join
(select month,
count(distinct user_id) as Legacy
from hive.aggregations.arr_changes_per_user_per_month
where month >= date_parse('2017-09-01', '%Y-%m-%d')
and month < date_parse('2018-07-01', '%Y-%m-%d')
and plan_simple = 'Legacy'
group by month
order by month asc) legacy
on free.month = legacy.month
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment