Another Google SQL Interview Question for Data Scientists and Data Analysts (StrataScratch 2098)

Solution and walkthrough of a real SQL interview question for Data Scientist and Data Analyst technical coding interviews. This question was asked by Google and is called "World Tours".

Playlists:

[sachin-bcm]

thank you
you are doing an amazing job !
without your videos I find it really hard to study this language

[chillvibe]

Great video! I didn't know about the first_value function so I did this :
WITH a AS (
SELECT traveler, start_city, date, RANK () OVER (PARTITION BY traveler ORDER BY date ) AS rnk
FROM travel_history),
b AS (
SELECT traveler, end_city, date, RANK () OVER (PARTITION BY traveler ORDER BY date DESC ) AS rnk2
FROM travel_history)
SELECT COUNT(DISTINCT b.traveler)
FROM a
JOIN b ON a.rnk = b.rnk2
WHERE a.rnk = 1 and b.rnk2 = 1 and a.start_city = b.end_city

[helvinharry]

Thanks, your videos helped me a lot to get better at SQL

[VedanttMehtaa]

My Solution using first_value and last_value
select count(distinct traveler) as n_travelers_returned from (
select *
, first_value(start_city) over (partition by traveler order by date) as first_city
, last_value(end_city) over (partition by traveler order by date range between unbounded preceding and unbounded following) as last_city
from travel_history
) a where first_city=last_city

[karangohil]

can you solve/share someone solution with lead & lag window functions