Demo versus no-demo query

with most_recent_subscription as (
  select
    user_id,
    max(id) as subscription_id
  from alooma.subscriptions
  group by 1
),
weekly_minutes as (
  select
    a.user_id,
    a.summarized_reporting_cleaned_source,
    case when a.is_qualified is True then 'yes' else 'no' end as is_qualified_flag,
    a.active_days_week_one || '-days' as days_active_week_one,
    a.subscription_created_at,
    -- we round the subscription week so that at most we are +/- three days out of sync
    max(round(f_time_interval('month', a.subscription_created_at, getdate()))) as most_recent_subscription_week,
    sum(u.billable_minutes) as total_minutes
  from user_stats_by_subscription_day u
  join analytics.acquisition_credit_card_source_tracking a
    on a.user_id = u.user_id
    and a.subscription_id = u.subscription_id
  join most_recent_subscription m
    on m.user_id = u.user_id
    and m.subscription_id = u.subscription_id
  where
    subscription_started_at between '2018-07-01' and getdate() - '2 week'::interval  -- at least two weeks old
  group by 1,2,3,4,5
),
expected_billable_minutes as (
  select
    w.user_id,
    w.summarized_reporting_cleaned_source,
    w.subscription_created_at,
    -- Observed revenue so far / percent of cumulative revenue expected for this cohort
    -- Cohorts are based on tenure week, acquisition soutce, qualified, active days in week one
    round(w.total_minutes / rev_cum.cumulative_percent_of_year_one_expected_revenue) as total_expected_year_one_billable_minutes
  from weekly_minutes w
  join analytics.weekly_revenue_stats_by_tenure_week rev_cum
  on rev_cum.payment_tenure_week = w.most_recent_subscription_week
  and rev_cum.cleaned_source = w.summarized_reporting_cleaned_source
  and rev_cum.week_one_qualified_bucket = w.is_qualified_flag
  and rev_cum.active_days_week_one_string = w.days_active_week_one
),
demo_users as (
	select
		u.id as user_id,
		max(case when d.email is not null then 1 else 0 end) as had_demo
	from users u
	left join sandbox.emails_from_sales_demos d
	on lower(d.email) = lower(u.email)
	group by 1
),
user_employment_title as (
	select
		user_id,
		max(identity_employment_title) as employment_title
	from analytics.identity_info_and_state i
	group by 1
)
select
	case when d.had_demo then 'had_demo' else 'no_demo' end as demo_flag,
	avg(w.total_expected_year_one_billable_minutes)  as avg_first_year_expected_billable_minutes_overall,
	avg(case when w.summarized_reporting_cleaned_source = 'invite_cold' then  w.total_expected_year_one_billable_minutes else null end) as avg_invite_cold_first_year_expected_billable_minutes,
	avg(case when w.summarized_reporting_cleaned_source = 'direct' then  w.total_expected_year_one_billable_minutes else null end) as avg_direct_first_year_expected_billable_minutes,
	avg(case when i.employment_title ilike '%ceo%' or i.employment_title ilike '%founder%' then w.total_expected_year_one_billable_minutes else null end) as avg_founder_or_ceo_first_year_expected_billable_minutes,

  	count(distinct case when w.summarized_reporting_cleaned_source = 'direct' then  w.user_id else null end) as n_direct_users,
  	count(distinct case when w.summarized_reporting_cleaned_source = 'invite_cold' then  w.user_id else null end) as n_invite_cold_users,
  count(distinct case when i.employment_title ilike '%ceo%' or i.employment_title ilike '%founder%' then w.user_id else null end) as n_users_founder_or_ceo,
	count(distinct w.user_id) as n_users_overall
from expected_billable_minutes w
left join user_employment_title i
on i.user_id = w.user_id
left join demo_users d
on d.user_id = w.user_id
where total_expected_year_one_billable_minutes is not null
group by 1
;