Leetcode DSA Problem solving using SQL – Travel Data of Customer

CREATE TABLE travel_data (
    customer VARCHAR(10),
    start_loc VARCHAR(50),
    end_loc VARCHAR(50)
);

INSERT INTO travel_data (customer, start_loc, end_loc)
VALUES
    ('c1', 'New York', 'Lima'),
    ('c1', 'London', 'New York'),
    ('c1', 'Lima', 'Sao Paulo'),
    ('c1', 'Sao Paulo', 'New Delhi'),
    ('c2', 'Mumbai', 'Hyderabad'),
    ('c2', 'Surat', 'Pune'),
    ('c2', 'Hyderabad', 'Surat'),
    ('c3', 'Kochi', 'Kurnool'),
    ('c3', 'Lucknow', 'Agra'),
    ('c3', 'Agra', 'Jaipur'),
    ('c3', 'Jaipur', 'Kochi');

with t1 as(select
customer,
start_loc
from travel_data
where start_loc not in (select end_loc from travel_data)),

t2 as (select
customer,
end_loc
from travel_data
where end_loc not in (select start_loc from travel_data)),

t3 as (select customer, start_loc from travel_data union
select customer, end_loc from travel_data )

select t1.customer, t1.start_loc, t2.end_loc, count(t3.start_loc)
from t3
inner join t1 on t3.customer=t1.customer
inner join t2 on t3.customer=t2.customer
group by 1,2,3
order by customer