I have two tables from different years, 2015 and 2016. Both tables have three columns as exampled below in table_2015:
x
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;