I have two tables from different years, 2015 and 2016. Both tables have three columns as exampled below in table_2015:
customer_id || plan_type || most_recent_date 110 || monthly || 2015/10/01 111 || weekly || 2015/11/07
I want to see how many people had the same plan type between the years and how many people switched plan types. How do I go about doing this?
Advertisement
Answer
If customers are in each table only once and you only care about the overlaps, then:
select sum(t1.plan_type = t2.plan_type) as num_same, sum(t1.plan_type <> t2.plan_type) as different from t1 join t2 on t1.customer_id = t2.customer_id;
If you actually want to compare the plan types, then use aggregation:
select t1.plan_type, t2.plan_type, count(*) from t1 join t2 on t1.customer_id = t2.customer_id group by t1.plan_type, t2.plan_type;