plan;

gistfile1.sql

select plan_type,
count(*) num_accounts, sum(mrr*12) total_arr, avg(mrr*12) avg_arr, sum(max_agents) total_agents, avg(max_agents) avg_agents, std(max_agents) std_agents
from accounts a
inner join roles r using (account_id, run_at, timespan)
inner join zuora_mrr mrr using (account_id, run_at, timespan)
where run_at= 20150304 
and max_agents between 10 and 50 
and plan_type != 1
and mrr > 0
group by plan_type;

select 
case 	when max_agents <= 5 then 5
		when max_agents <= 10 then 10
        when max_agents <= 50 then 50
        when max_agents <= 100 then 100
        when max_agents <= 200 then 200
        when max_agents <= 300 then 300
        when max_agents <= 400 then 400
        when max_agents <= 500 then 500
        when max_agents > 500 then '501+' 
        end agents,
count(*) num_accounts,
sum(if(has_insights = 1, 1, 0)) num_account_with_insights,
sum(max_agents) num_agents,
sum(if(has_insights = 1, max_agents, 0)) num_agents_with_insights,
avg(max_agents) avg_agents,
sum(mrr*12) arr,
sum(if(has_insights = 1, mrr*12, 0)) arr_with_insights
from accounts a 
inner join zuora_mrr mrr using (account_id, run_at, timespan)
left outer join accounts_features af using (account_id, run_at, timespan)
where run_at = 20150305
and mrr.mrr > 0
and max_agents between 10 and 49
and plan_type != 1
group by 
case 	when max_agents <= 5 then 5
		when max_agents <= 10 then 10
        when max_agents <= 50 then 50
        when max_agents <= 100 then 100
        when max_agents <= 200 then 200
        when max_agents <= 300 then 300
        when max_agents <= 400 then 400
        when max_agents <= 500 then 500
        when max_agents > 500 then '500+'
end
order by max_agents desc;