Solving a Leetcode DSA Problem with SQL | Advanced SQL for Analytics | Aam vs Mentos Life
Вставка
- Опубліковано 7 лют 2024
- Here is the leetcode problem link:
leetcode.com/problems/destina...
Here is the script:
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');
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer
Guys it takes lots of effort to make these kinds of videos. Please hit the like button on the video. I will make more DSA SQL videos if I get 1000 likes. 🙏
I did by using sub-query,
select customer,
min(case when start_loc not in(select end_loc from travel_data) then start_loc end )as start_point
,max(case when end_loc not in(select start_loc from travel_data) then end_loc end )as destination
from travel_data group by customer ;
Well, Thanks for the challenge once again Ankit , I have used Except Clause in SQL as below :
select A.customer, start_loc,end_loc from
(select customer ,start_loc from cte
except
select customer ,end_loc from cte
)A,
(
select customer ,end_loc from cte
except
select customer ,start_loc from cte
)B
where A.customer = B.customer
Thank you again, and again. The principle of this solution can be applied to many solutions. Thanks for your inspiring work!
Glad it was helpful!
Faced this in an interview 30 mins back. Could not solve this. I solved the start and end location seperately but could not combine them.
Awesome video Ankit.
what an experience solving such problem!!! mindblowing!!!
Hi sir, thanks for accepting the Question. I liked the second approach. I thought it is not possible to solve this problem with window function, but you did it. Not only that, but I learned a lot from the first solution.
Thanks for the video…
Excellent
Great video as usual, thanks for covering such wonderful questions 🙏
Imo 1st option is mentos option and 2nd option requires great application of basic joins
Using different approach using the trick mentioned
with cte1 as (select customer,start_loc from travel_data
where start_loc not in (select end_loc from travel_data)),
cte2 as (select customer,end_loc from travel_data
where end_loc not in (select start_loc from travel_data))
select cte1.*,cte2.end_loc from cte1
inner join cte2 on cte1.customer=cte2.customer
Hi Ankit
I got emotional today after solving this question. Few months back i was not able to write even simple queries but now solving a Leet code hard problem .
it is possible all because of you.
My solution for the same.
;with start_location as (
select customer,start_loc
from travel_data
except
select customer,end_loc
from travel_data
)
,end_location as (
select customer,end_loc
from travel_data
except
select customer,start_loc
from travel_data
)
select sl.*,el.end_loc from start_location sl
inner join end_location el on sl.customer=el.customer
👏👏
select coalesce(a.customer,b.customer),max(a.start_loc),max(b.end_loc) from
travel_data a full join
travel_data b on a.customer=b.customer and a.start_loc=b.end_loc where a.start_loc is null or b.end_loc is null
group by coalesce(a.customer,b.customer)
Very, very good , Thank you for posting. I prefer Method 2. I found the explanation in Excel very helpful. Thanks again.
Hi Ankit, Great question really gives good exercise to SQL concepts.
Excellent Ankit, really appreciate, God Bless
It's my pleasure😊
Ankit you are really creating amazing videos. Please make such more videos. Thanks
I always learn something new from your videos, thank you so much for sharing such valuable contents
My pleasure!
Great video and great explanation 👏👏
Thank you! 👍
Thank you sir, great explanation
using inner join query
Select a.*,b.end_loc from (select customer, start_loc from travel_data where start_loc not in (select end_loc from travel_data)) as a join
(select customer, end_loc from travel_data where end_loc not in (select start_loc from travel_data)) as b
where a.customer=b.customer
with cte as(
select customer,start_loc from travel_data where
start_loc not in (select end_loc from travel_data))
,cte1 as(
select customer,end_loc from travel_data where
end_loc not in (select start_loc from travel_data))
select cte.customer,start_loc,end_loc from cte1,cte where cte.customer=cte1.customer
Very good explanation, keep it up and make more videos like this.
I will try my best
Thank you so much Sir 🙏
Most welcome
Hi Ankit...thanks for the content my approach
WITH ALL_LOCATION AS(
SELECT customer,start_loc AS location, 0 AS IND_Start
FROM travel_data
UNION ALL
SELECT customer,end_loc AS location, 1 AS IND_end
FROM travel_data),
times_visited AS(
SELECT customer,LOCATION,count(*) AS times_visited,max(IND_Start) AS start_end_IND
FROM ALL_LOCATION
GROUP BY customer,location
HAVING count(*)=1)
SELECT customer,max(CASE WHEN start_end_IND=0 THEN LOCATION END) AS start_loc,
max(CASE WHEN start_end_IND=1 THEN LOCATION END) AS end_loc
FROM times_visited
GROUP BY customer
SELECT a.customer,a.start_loc,b.end_loc
from
(
select a.customer,a.start_loc as start_loc from travel_data a
left join travel_data b on(a.start_loc=b.end_loc)
where b.end_loc IS NULL
)a
INNER JOIN
(
select a.customer,a.end_loc as end_loc from travel_data a
left join travel_data b on(a.end_loc=b.start_loc)
where b.start_loc IS NULL
)b on(a.customer=b.customer)
instead of doing left join two times we can simply do full join, see the solution below:
with cte as (
select l.customer as l_customer,l.start_loc as l_start_loc,l.end_loc as l_end_loc, r.customer as r_customer,r.start_loc as r_start_loc,r.end_loc as r_end_loc
from travel_data l
full join travel_data r
on l.customer=r.customer and l.start_loc=r.end_loc
)
select coalesce(l_customer,r_customer) as customer,
max(case when r_customer is null then l_start_loc end) as start_loc,
max(case when l_customer is null then r_end_loc end) as end_loc, count(1) as total
from cte group by coalesce(l_customer,r_customer)
1st method looks good.
Very helpful and interesting question. In my opinion 1st one is Aam and 2nd one is Mentos solution...
I am with you 💯
@@ankitbansal6
Can you check my query please,
Select t1.customer, t1.start_location , t2.end_location from travel_data t1 join travel_data t2 on t1.start_location t2.end_location and t1.customer=t2.customer
with cte as (
select a.customer,a.start_loc as start_point
from travel_data a
where a.start_loc not in (select end_loc from travel_data b where a.customer=b.customer )
) ,
cte1 as (
select a.customer,a.end_loc as end_point
from travel_data a
where a.end_loc not in (select start_loc from travel_data b where a.customer=b.customer ))
select c.customer,c.start_point,b.end_point from cte c join cte1 b
on c.customer=b.customer
Hi Sir
here's the my solution
select customer, max(case when start_loc not in (select end_loc from travel_data) then start_loc end) as start_location
, max(case when end_loc not in (select start_loc from travel_data) then end_loc end) as end_location
from travel_data
group by customer;
Great video, as per me I think first method is mentos solution 😉.
Second is easy..but first also nice
select
Max(case when td2.end_loc is null then td1.start_loc end) as 'Start Location'
,Max(case when td1.start_loc is null then td2.end_loc end) as 'end Location'
,Max(case when td1.customer is null then td2.customer else td1.customer end )as customer
from travel_data td1
full outer join travel_data td2 on td1.customer=td2.customer and td1.start_loc=td2.end_loc
group by td1.customer
I have done it like this, But I'm not sure if it will work for all test cases
with start_point as (select customer, start_loc
from travel_data
where start_loc not in (select end_loc from travel_data)),
end_point as (select end_loc, customer
from travel_data
where end_loc not in (select start_loc from travel_data))
select *
from start_point
join end_point using(customer)
same i have done
with cte_start
as(
select customer,start_loc
from travel_data
where start_loc
not in(select end_loc
from travel_data)),
cte_end as
(select customer,end_loc
from travel_data
where end_loc
not in(select start_loc
from travel_data))
select cs.customer,start_loc,end_loc
from cte_end ce join cte_start cs
on ce.customer = cs.customer;
Second soln is great
select A.*, B.coun from (
select customer,
max(case when loc = 'start' then start_loc else null end ) as start_loc,
max(case when loc = 'end' then start_loc else null end ) as end_loc
from (
select s.*, count(start_loc)over(partition by customer, start_loc ) as r
from (
select customer,start_loc,'start' as loc from travel_data
union all
select customer,end_loc,'end' from travel_data) s) where r = 1 group by customer) A
join(
select customer,count(distinct start_loc) as coun from (
select customer,start_loc,'start' as loc from travel_data
union
select customer,end_loc,'end' from travel_data) group by customer) B on A.customer= B.customer;
hii Ankith , hear is my solution
with cte as(
select customer,start_loc from travel where start_loc not in (select end_loc from travel)),en as(
select customer,end_loc from travel where end_loc not in (select start_loc from travel))
select c.customer,start_loc,end_loc from cte c
inner join en e on e.customer=c.customer
Just another approach ,
with cte as(
select customer, start_loc as Initial_loc, Null as end_loc from travel_data where start_loc not in (select end_loc from travel_data)
union
select customer, Null as Initial_loc, end_loc from travel_data where end_loc not in (select start_loc from travel_data)
)
select customer, MAX(Initial_loc) as Initial_loc,MAX(end_loc) as end_loc from cte
group by customer
wow!
Option 2 ❤❤
with cte as (select customer,start_loc from travel_data
where start_loc not in(select end_loc from travel_data)
union all
select customer,end_loc from travel_data
where end_loc not in(select start_loc from travel_data)
group by customer),
final_cte as(select *,row_number() over(partition by customer order by customer) as
rnk from cte c1)
select * ,coalesce(case when rnk=1 then start_loc end,'') as strt_dest,
coalesce(case when rnk=2 then start_loc end,'') as end_dest
from final_cte
with t as (
select customer,NULL as start_loc,end_loc from travel_data
where end_loc not in (select start_loc from travel_data)
UNION
select customer,start_loc,NULL as end_loc from travel_data
where start_loc not in (select end_loc from travel_data)
)
select customer,max(start_loc),max(end_loc) from t
group by customer
Please start python questions as well
with cte1 as
(select *
from travel_data
where start_loc not in (select end_loc from travel_data)
),
cte2 as
(
select *
from travel_data
where end_loc not in (select start_loc from travel_data)
)
select c1.customer,c1.start_loc,c2.end_loc from cte1 c1
join cte2 c2
on c1.customer = c2.customer;
with
start as (select customer,start_loc from travel_data a where not exists( select * from travel_data b where a.customer=b.customer and a.start_loc=b.end_loc)),
end as (select customer,end_loc from travel_data a where not exists( select * from travel_data b where a.customer=b.customer and b.start_loc=a.end_loc)),
cnt as(select customer,count(*)+1 as cnt from travel_data group by customer)
select start.customer,start_loc,end_loc,cnt as total_travelled from start,end,cnt where start.customer=end.customer and start.customer=cnt.customer;
select customer,
max(start_loc) as start_loc,
max(end_loc) as end_loc
from(
select customer,type,
case when type='start' then location else null end as start_loc,
case when type='end' then location else null end as end_loc
from(
select *,
case when location in (select start_loc from travel_data as td1 where td1.customer = t1.customer) then 'start' else 'end' end as type
from(
select customer,
location
from (
select customer,start_loc as location from travel_data
UNION ALL
select customer,end_loc from travel_data
order by 1,2)t
group by customer,2
having count(*)=1)t1) as t2
group by 1,2)as t3
group by 1
with start_loc as (
select customer,start_loc from travel_data a
where start_loc not in (
select end_loc from travel_data where customer = a.customer
)
),end_loc as (
select customer,end_loc from travel_data a
where end_loc not in (
select start_loc from travel_data where customer = a.customer
)
)
select start_loc.customer,start_loc.start_loc,end_loc.end_loc from start_loc
inner join end_loc
on start_loc.customer = end_loc.customer
My solution : with cte as (select customer,start_loc as a from travel_data
union all
select customer,end_loc as a from travel_data),
cte1 as (
select customer,a
from cte group by a
having count(a)=1),
cte2 as (
select c.customer, c.a as origin
from cte1 c
inner join travel_data t
on c.customer = t.customer and
c.a = t.start_loc),
cte3 as (
select c.customer, c.a as end
from cte1 c
inner join travel_data t
on c.customer = t.customer and
c.a = t.end_loc)
select c2.customer,c2.origin, c3.end
from cte2 c2
inner join cte3 c3
on c2.customer = c3.customer
with cte as (select customer,start_loc from travel_data
where start_loc not in (select end_loc from travel_data)),
temp as (select customer,end_loc from travel_data
where end_loc not in (select start_loc from travel_data))
select cte.customer,cte.start_loc,temp.end_loc from cte join temp on cte.customer = temp.customer
Hi, Ankit, I've done that by using recursive cte. What do you think?
with a as (
select customer, start_loc start
from travel_data
except
select customer, end_loc start
from travel_data),
bb as (
select b.customer, b.start_loc, b.end_loc, start
from travel_data b left join a on b.customer=a.customer and b.start_loc=a.start
)
,rec as (
select customer, 1 level, start_loc, end_loc
from bb where start is not null
union all
select bb.customer, level +1 level, bb.start_loc, bb.end_loc
from rec
inner join bb on rec.customer=bb.customer and rec.end_loc=bb.start_loc
)
select distinct customer
, first_value(start_loc) over(partition by customer order by level) sl
,first_value(end_loc) over(partition by customer order by level desc) fl
from rec
order by 1, 2
Became a bit complex but a good attempt 👍
with cte as (
select customer, start_loc as loc from travel_data
union all
select customer, end_loc as loc from travel_data
order by customer), bcte as (
select customer, loc
from cte
group by customer, loc
having count(*) = 1)
select a.customer, max(b.start_loc) as initial_loc, max(c.end_loc) as final_loc
from bcte as A
left join travel_data as b
on a.customer = b.customer
and a.loc = b.start_loc
left join travel_data as c
on a.customer = c.customer
and a.loc = c.end_loc
group by a.customer;
I just solved it before watching it, and then watched the video, brilliant solutions as always!
Complete solution with count :
select A.*,B.unique_cities_traveled from
(select customer,
min(case when start_loc not in(select end_loc from travel_data) then start_loc end )as start_point
,max(case when end_loc not in(select start_loc from travel_data) then end_loc end )as destination
from travel_data group by customer ) A
join
(SELECT
customer,
COUNT(DISTINCT city) AS unique_cities_traveled
FROM (
SELECT customer, start_loc AS city FROM travel_data
UNION ALL
SELECT customer, end_loc AS city FROM travel_data
) AS cities
GROUP BY
customer) B
on A.customer=b.customer
My Approach using 2 cte and left join:
with start_point as (
select s1.customer,s1.start_loc,e1.end_loc
from travel_data s1
left join travel_data e1 on s1.customer=e1.customer and s1.start_loc=e1.end_loc
where e1.end_loc is null
),
end_point as (
select s1.customer,e1.start_loc,s1.end_loc
from travel_data s1
left join travel_data e1 on s1.customer=e1.customer and s1.end_loc=e1.start_loc
where e1.start_loc is null
)
select sp.customer,sp.start_loc,ep.end_loc from start_point sp join end_point ep using(customer);
PySpark Step By Step Approach and Solution Explanation Video for this Problem :
ua-cam.com/video/t1Uryyi8JiE/v-deo.html
Hi ankit today I have written tiger analytics hacker earth exam. One of the coding question is
If the first salary is 100rs for January and need to fill 12 columns infinitely with cumulative sum, we need to find the month for the given input salary. Example input is 2500 output is January. Can you do a video for this
Didn't get it. Please send sample input data and expected output on sql.namaste@gmail.com
My solution:-
with cte as (
select t1.*, t2.start_loc as f1, t2.end_loc as f2, t3.start_loc as f3, t3.end_loc as f4
from travel_data t1
left join travel_data t2 on t1.customer = t2.customer and t1.end_loc = t2.start_loc
left join travel_data t3 on t1.customer = t3.customer and t1.start_loc = t3.end_loc
)
, cust_start_loc as (
select customer, start_loc as initial_loc
from cte
where f3 is null and f4 is null
)
, cust_end_loc as (
select customer, end_loc as last_loc
from cte
where f1 is null and f2 is null
)
select cust_start_loc.customer, initial_loc, last_loc
from cust_start_loc join cust_end_loc on cust_end_loc.customer = cust_start_loc.customer;
I liked method 2 more.
Another approach i tried to solve the problem.
# get each customer start locations and end locations.
with travel_contact as(
select customer,
group_concat(start_loc) as cus_startlocations,
group_concat(end_loc) as cus_endlocations
from travel_data
group by customer),
# join customer travel data and all start locations and end locations.
cte2 as(
select a.customer,a.start_loc,a.end_loc, b.cus_startlocations,b.cus_endlocations
from travel_data a
join travel_contact b
on a.customer=b.customer),
# check customer start location is present in end location and end location present in start location
cte3 as
(
select
customer,
case when LOCATE (start_loc,cus_endlocations) then null else start_loc end as firstlocation,
case when LOCATE (end_loc,cus_startlocations) then null else end_loc end as lastlocation
from cte2)
select customer,max(firstlocation) as Initiallocation,max(lastlocation) as finallocation
from cte3
group by customer
Here's my approach:
WITH CTE AS
(
SELECT customer,
CASE WHEN start_loc ALL(SELECT end_loc FROM travel_data AS t2 WHERE t1.customer = t2.customer)
THEN t1.start_loc END AS actual_start,
CASE WHEN end_loc ALL(SELECT start_loc FROM travel_data AS t2 WHERE t1.customer = t2.customer)
THEN t1.end_loc END AS actual_end
FROM travel_data AS t1
)
SELECT customer,
MAX(actual_start) AS origin,
MAX(actual_end) AS destination
FROM CTE
GROUP BY customer;
Another approach:
WITH CTE AS
(
SELECT customer, start_loc AS origin
FROM travel_data
EXCEPT
SELECT customer, end_loc
FROM travel_data
),
CTE2 AS
(
SELECT customer, end_loc AS destination
FROM travel_data
EXCEPT
SELECT customer, start_loc
FROM travel_data
)
SELECT c1.customer, origin, destination
FROM CTE AS c1
INNER JOIN CTE2 AS c2
ON c1.customer = c2.customer
method 2
Second method
2nd one is Ankit 1st one is me
Method 2
Simplest Solution:
with cte1 as
(select start_loc,customer from travel_data where start_loc not in (select end_loc from travel_data)),
cte2 as
(select end_loc,customer from travel_data where end_loc not in (select start_loc from travel_data))
select cte1.customer,cte1.start_loc,cte2.end_loc from cte1 join cte2 on cte1.customer=cte2.customer
Please start batch for sql
Soon
I think second method is 👍
I think so too
with cte1 as
(select customer,start_loc
from travel_data where
start_loc not in(select end_loc from travel_data)),
cte2 as
(select customer,end_loc
from travel_data where
end_loc not in(select start_loc from travel_data))
select c1.customer,c1.start_loc,c2.end_loc
from cte1 c1
join cte2 c2 on
c1.customer=c2.customer
I think 2nd solution is more efficient than 1st.
In 2nd solution there is no ctes, no subqueries so for me 2nd one is a mentos zindagi
Agree
@@ankitbansal6 please make a video of this question using pandas 🙏🏻
Hi all please find my solution .
with cte1 as(
select customer,start_loc as loc from travel_data
union all
select customer,end_loc as loc from travel_data
),
cte2 as(
select TOP 100 PERCENT customer,loc,count(*) as start_or_end from cte1
group by customer,loc
having count(*)=1
order by customer,loc
),
cte3 as(
select a.*,b.loc,
case when a.start_loc=b.loc then b.loc else null end as starting_find_location,
case when a.end_loc=b.loc then b.loc else null end as ending_find_location
from travel_data a join cte2 b on a.customer=b.customer
)
SELECT customer,max(starting_find_location) as start_loc,max(ending_find_location) as end_loc
FROM cte3
group by customer
My solution: WITH CTE_END AS(
Select A.end_loc as ending_point,A.customer from
travel_data A left join travel_data B
on A.customer=B.customer and A.end_loc=b.start_loc
where B.end_loc is NULL
),
CTE_START AS(
Select A.start_loc as starting_point,A.customer from
travel_data A left join travel_data B
on A.customer=B.customer and A.start_loc=b.end_loc
where B.end_loc is NULL)
SELECT CTE_START.CUSTOMER,CTE_START.starting_point,CTE_END.ending_point
FROM CTE_END INNER JOIN CTE_START ON CTE_END.CUSTOMER=CTE_START.CUSTOMER
My solution(using co-related subqueries):
with cte1 as(
select customer, start_loc as initial_loc
from travel_data t1
where t1.start_loc not in (select end_loc from travel_data t2 where t1.customer = t2.customer)),
cte2 as(
select customer, end_loc as last_loc
from travel_data t1
where t1.end_loc not in (select start_loc from travel_data t2 where t1.customer = t2.customer))
select cte1.customer, cte1.initial_loc, cte2.last_loc
from cte1 join cte2 on cte1.customer = cte2.customer
with s as (
select t1.customer,t1.start_loc
from travel_data t1
left outer join travel_data t2
on t1.customer=t2.customer
and t1.start_loc =t2.end_loc
where t2.start_loc is null)
, e as (
select t1.customer,t1.end_loc
from travel_data t1
left outer join travel_data t2
on t1.customer=t2.customer
and t1.end_loc =t2.start_loc
where t2.end_loc is null
)
select s.customer, s.start_loc, e.end_loc from s join e on s.customer = e.customer
with recursive cte1 as
(select customer,start_loc,end_loc,0 as sot from travel_data
union all
SELECT cte1.customer,cte1.start_loc,t.end_loc,sot+1 as sot from cte1 join travel_data t on cte1.customer = t.customer
and cte1.end_loc = t.start_loc)
,cte2 as
(select customer,max(sot) st from cte1 group by customer)
select customer,start_loc,end_loc from cte1 join cte2 using(customer) where sot=st order by customer
my unique solution 😅😅😊😊
with cte as (
select c.customer as start_customer, c.start_loc, c1.customer as end_customer, c1.end_loc
from
(
select customer, start_loc
from travel_data
) c
full outer join
(
select customer, end_loc
from travel_data
) c1
on
c.customer = c1.customer
and
c.start_loc = c1.end_loc
)
select coalesce(start_customer, end_customer) as customer, max(start_loc) as start_loc, max(end_loc) as end_loc
from cte
where (end_customer is null or start_customer is null)
group by coalesce(start_customer, end_customer);
with cte_start_loc as
(
select customer,start_loc from travel_data
where (customer,start_loc) not in(select customer,end_loc from travel_data )
), cte_end_loc as(
select customer,end_loc from travel_data
where (customer,end_loc) not in(select customer,start_loc from travel_data )
)
select cte_start_loc.customer,start_loc,end_loc
from cte_start_loc left join cte_end_loc
on cte_start_loc.customer=cte_end_loc.customer
WITH cte as
(SELECT customer, start_loc FROM travel_data
WHERE start_loc NOT IN (SELECT end_loc FROM travel_data)),
cte2 as
(SELECT customer, end_loc FROM travel_data
WHERE end_loc NOT IN (SELECT start_loc FROM travel_data))
SELECT a.customer, start_loc, end_loc
FROM cte a
JOIN cte2 b
ON a.customer = b.customer
;WITH CTE
as
(
select Td.start_loc as StartLocation,T.End_loc as EndLocation,td.customer as TDCustomer ,t.customer as Tcustomer
from travel_data TD full join travel_data T ON TD.start_loc=T.end_loc and td.customer=t.customer
where td.start_loc is null or t.end_loc is null
)
select c.TDCustomer as Customer,C.StartLocation,c2.EndLocation
from CTE C join CTE C2
on c.TDCustomer=C2.Tcustomer
PLEASE CHECK MY SOLUTION, SIR...
with start_loc as(
select t.customer,
t.start_loc
from travel_data as t
left join
travel_data as t2
on t.customer=t2.customer and t.start_loc=t2.end_loc and t.start_loct.end_loc
where t2.customer is null)
,end_loc as (
select t2.customer,
t2.end_loc
from travel_data as t
right join
travel_data as t2
on t.customer=t2.customer and t.start_loc=t2.end_loc and t.start_loct.end_loc
where t.customer is null)
select st.customer,
st.start_loc,
en.end_loc
from start_loc as st
join
end_loc as en
on st.customer=en.customer
my solution
with c1 as (
SELECT customer,start_loc from
travel_data t1 where start_loc not in (
select end_loc from travel_data t2 where t1.customer = t2.customer) ),
c2 as (
SELECT customer,end_loc from
travel_data t1 where end_loc not in (
SELECT start_loc from travel_data t2 where t1.customer = t2.customer) )
SELECT c1.customer,start_loc,end_loc from c1
join c2 on c1.customer = c2.customer
;with travel_cte as
(
select customer, start_loc, count(*) start_end_count
from
(
select customer, start_loc from travel_data
union all
select customer, end_loc from travel_data
)tbl
group by
customer,
start_loc
having count(*) = 1
)
select ct.customer,
max(case
when ct.start_loc != td.end_loc and ct.customer = td.customer then ct.start_loc
end) start_loc
,max(case
when ct.start_loc = td.end_loc and ct.customer = td.customer then td.end_loc
end) end_loc
from travel_cte ct
join travel_data td on ct.customer = td.customer
and (ct.start_loc = td.start_loc or ct.start_loc = td.end_loc)
group by ct.customer
Please check it out:
with cte as (
select td1.customer, td1.start_loc, td1.end_loc, td1.customer as td1_customer, td2.customer as td2_customer, td3.customer as td3_customer
from travel_data td1
left join travel_data td2 on td1.start_loc = td2.end_loc and td1.customer = td2.customer
left join travel_data td3 on td1.end_loc = td3.start_loc and td1.customer = td3.customer
where td2.customer is null or td3.customer is null
)
select customer, start_loc, last_end_loc as end_loc from (
select customer, start_loc, lead(end_loc, 1) over (partition by customer order by td2_customer, td3_customer) as last_end_loc from cte
) ab where last_end_loc is not null
my solution with cte
WITH cte AS (
SELECT customer, start_loc, end_loc
FROM travel_data
WHERE start_loc NOT IN (SELECT end_loc FROM travel_data)
), cte2 AS (
SELECT customer, start_loc, end_loc
FROM travel_data
WHERE end_loc NOT IN (SELECT start_loc FROM travel_data)
),
cte3 as (
select customer,count(*) as total_visited from
(select customer,start_loc from travel_data
UNION
select customer,end_loc from travel_data) as t1 group by customer)
SELECT cte.customer, cte.start_loc, cte2.end_loc,cte3.total_visited
FROM cte
JOIN cte2 ON cte.customer = cte2.customer join cte3 on cte.customer=cte3.customer
I have solved by below approach. Plese spare some time for having a look.
with cte as (
select t1.customer, t1.start_loc from travel_data t1 left join travel_data t2 on t1.customer = t2.customer and t1.start_loc = t2.end_loc
where t2.end_loc is NULL )
, cte2 as (
select t1.customer, t1.end_loc from travel_data t1 left join travel_data t2 on t1.customer = t2.customer and t1.end_loc = t2.start_loc
where t2.start_loc is NULL )
select cte.customer, start_loc, end_loc from cte inner join cte2 on cte.customer = cte2.customer
with start_loc as (SELECT t1.customer, t1.start_loc
FROM travel_data t1
LEFT JOIN (
SELECT DISTINCT customer, end_loc
FROM travel_data
) t2 ON t1.customer = t2.customer AND t1.start_loc = t2.end_loc
WHERE t2.customer IS NULL),
end_loc as (SELECT t1.customer,t1.end_loc
FROM travel_data t1
LEFT JOIN (
SELECT DISTINCT customer, start_loc
FROM travel_data
) t2 ON t1.customer = t2.customer AND t2.start_loc = t1.end_loc
WHERE t2.customer IS NULL)
select sl.customer,sl.start_loc,el.end_loc from
start_loc sl left join end_loc el
on sl.customer=el.customer;
Select t1.customer, t1.start_location , t2.end_location from travel_data t1 join travel_data t2 on t1.start_location t2.end_location and t1.customer=t2.customer
I did using sub-query.
select customer,
min(case when start_loc not in(select end_loc from travel_data) then start_loc end )as start_point
,max(case when end_loc not in(select start_loc from travel_data) then end_loc end )as destination
from travel_data group by customer ;
SELECT a.customer,a.start_loc,b.end_loc
from
(
select a.customer,a.start_loc as start_loc from travel_data a
left join travel_data b on(a.start_loc=b.end_loc)
where b.end_loc IS NULL
)a
INNER JOIN
(
select a.customer,a.end_loc as end_loc from travel_data a
left join travel_data b on(a.end_loc=b.start_loc)
where b.start_loc IS NULL
)b on(a.customer=b.customer)