Skip to content
Advertisement

How to compare column combinations from two tables?

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;
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement