SQL Cohort Segment Analysis

segment_cohorts.sql

select 
    product_segment as Segment,
    round(avg(revenue_then)) as `Average Revenue`,
    count(distinct company_id) as n,
    round(power(power(sum(revenue_now) / sum(revenue_then),  1/period_diff(date_format(month_now, "%Y%m"), date_format(month_then, "%Y%m"))), 12), 2) as `Annual Revenue Retention`, 
    round(1 - power(1 - sum(churned)/count(*),  1/period_diff(date_format(month_now, "%Y%m"), date_format(month_then, "%Y%m"))), 3) as `Monthly Churn`
from (
select old.company_id, product_segment, size_segment, channel_segment, old.revenue as revenue_then, new.revenue as revenue_now, new.revenue is null as churned, new.revenue_month as month_now, old.revenue_month as month_then
from (
select 
    company_id,
    company_kind as product_segment,
    case
        when count(distinct employee_id) < 5 then 'A: 0-4'
        when count(distinct employee_id) < 10 then 'B: 5-9'
        when count(distinct employee_id) < 20 then 'C: 10-19'
        when count(distinct employee_id) < 50 then 'D: 20-49'
        when count(distinct employee_id) < 100 then 'E: 50-99'
        else 'F: 100+'
    end as size_segment,
    case
        when partner_id = 6 then 'Direct'
        when partner_id IS NULL then 'Organic'
        else 'Partner'
    end as channel_segment,
    revenue_month,
    sum(sum_to_vat_cents+sum_no_vat_cents)/100 as revenue
from revenues
where company_kind <> 'empty' and revenue_month = "2016-08-01"
group by 1) as old
left join (
select
    company_id,
    revenue_month,
    sum(sum_to_vat_cents+sum_no_vat_cents)/100 as revenue
from revenues
where revenue_month = "2017-08-01"
group by 1) as new on new.company_id = old.company_id) as t 
group by 1