use examples; /* Method 2 - Result Tables */ drop table signed_up select * into signed_up from subscriptions where plan_id = 0 and year(subscription_start)*100+month(subscription_start) = 201411 drop table first_sub select username , min(subscription_start) as first_sub into first_sub from subscriptions where plan_id>0 group by username drop table paid select subs.* into paid 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 drop table conv_rate select signed_up.* , case when (paid.username is not null) then 1 else 0 end as paid into conv_rate from signed_up left join paid on signed_up.username = paid.username drop table was_active 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 into was_active from signed_up su left join sessions se on su.username = se.username group by su.username drop table final_data select conv_rate.*, active into final_data 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