use examples; /* Method 3 - CTE */ with signed_up as ( select * from subscriptions where plan_id = 0 and year(subscription_start)*100+month(subscription_start) = 201411 ) , first_sub as ( select username , min(subscription_start) as first_sub from subscriptions where plan_id>0 group by username ) , paid as ( select subs.* from subscriptions subs join first_sub on subs.username = first_sub.username and subs.subscription_start = first_sub.first_sub where subs.plan_id > 0 ) , conv_rate as ( select signed_up.* , case when (paid.username is not null) then 1 else 0 end as paid from signed_up left join paid on signed_up.username = paid.username ) , was_active as ( select su.username , max(case when (se.session_start between su.subscription_start and dateadd(dd,14,su.subscription_start)) then 1 else 0 end) as active from signed_up su left join sessions se on su.username = se.username group by su.username ) , final_data as ( select conv_rate.*, active from conv_rate left join was_active on conv_rate.username = was_active.username ) select active, paid, count(*) as users from final_data group by active, paid order by active, paid