churn

-- churned subscribers by month (only if they've paid once)
select thismonth.first_date as from_date, 
	thismonth.first_date + INTERVAL '1 month' as to_date,
	count(*) as churned
from month thismonth

	join app_history cancelled
	on cancelled.event = 'Recurring charge cancelled'
	and cancelled.date >= thismonth.first_date
	and cancelled.date < thismonth.first_date + INTERVAL '1 month'
	
where exists (select 1 from app_history activated
	where activated.event = 'Recurring charge activated'
	and activated.shop_domain = cancelled.shop_domain
	and coalesce(activated.billing_on,activated.date) <= cancelled.date
)
	
group by thismonth.first_date, thismonth.first_date + INTERVAL '1 month'
order by thismonth.first_date