Solving an Airbnb Data Science Coding Interview Question | SQL Interview [Fav Host Nationality]

[@miradizrakhmatov2159]

Thank you Nate, you have a great platform 1)Using correlated subquery: SELECT DISTINCT a.from_user, h.nationality FROM airbnb_reviews a JOIN airbnb_hosts h ON a.to_user = h.host_id WHERE a.from_type = 'guest' AND a.review_score = (SELECT MAX(b.review_score) FROM airbnb_reviews b WHERE a.from_user = b.from_user) 2) Using window function: SELECT DISTINCT a.from_user, b.nationality FROM (SELECT from_user, to_user, review_score, DENSE_RANK() OVER(PARTITION BY from_user ORDER BY review_score DESC) rk FROM airbnb_reviews WHERE from_type = 'guest') a JOIN airbnb_hosts b ON a.to_user = b.host_id WHERE rk = 1

[@AjayBhaskar]

Hi, I have a question. Shouldn’t the John in the correlated us query be a left join ? If you do inner join then you are reducing the number of users who have fav hosts ? There could be some missed users and nationality ?

[@umakanta7]

This is again another piece of mind blowing example..keep up the good work.

[@miraskhabibulla1345]

beautiful!!! Nate, you are awesome!

[@mohammadshahfaishal2747]

Why are you not doing it by window function max(rating) over (partition by guess_id) And then put a condition where max_rating = rating

[@stratascratch]

Because you can't use the aliases in the WHERE or HAVING clause. And if you tried to use the whole window function expression, it still wouldn't work. The window functions are not allowed in WHERE and HAVING.

[@saraali-ws9uv]

Your question selection is quite good..!! Keep going..!! I have a suggestion though...To keep the whole thing interesting you can maybe give a similar question as take-home question and answer it in the next video..! What say..? Good work..!!👏👏

[@stratascratch]

Thanks for watching. Yes, I can certainly do that. I can give a take-home question to do on your own and also give a preview of the next question I'll cover in the series. It won't work for every week since I change topics from time to time but for the weeks where I do 2 SQL questions in a row, I can pass on more questions. Thanks for the suggestion.

[@shobhamourya8396]

Here's my solution using CTE with cte1 as ( select *, dense_rank() over(partition by from_user order by review_score desc) host_rank from airbnb_reviews where from_type = 'guest' and to_type = 'host' ) select distinct from_user, h.nationality fav_host_nationality from cte1 c, airbnb_hosts h where c.host_rank = 1 and c.to_user = h.host_id order by from_user

[@elohim58]

+1 to above on both readability and run time efficiency

[@shobhamourya8396]

@@elohim58 Thank you!

[@anggipermanaharianja6122]

you are indeed correct, your SQL could be even easier to read like this one below: with cte as (select ar.from_user as user_id, ar.to_user, ar.review_score, ah.nationality as nationality, rank() over(partition by ar.from_user order by ar.review_score desc) as rank_ from airbnb_reviews as ar left join airbnb_hosts as ah on ar.to_user = ah.host_id where from_type='guest') select distinct user_id, nationality from cte where rank_ = 1

[@hassamkafeel]

@@anggipermanaharianja6122 with a as (select from_user, review_score, h.nat as natt, to_user, dense_rank() over (partition by from_user order by review_score desc) as rank_ed from airbnb_reviews inner join (select distinct(host_id) as id, nationality as nat from airbnb_hosts) as h on h.id = to_user where from_type = 'guest' and to_type = 'host') select distinct(natt),from_user, rank_ed, review_score from a where rank_ed = 1 order by 2

[@zarynooi5669]

Appreciate content like this !!

[@swayankashanu4542]

Hi, I do not have the premium feature to check whether my output is correct. Can anyone suggest my code is corrector not? Here's my code : with summary_data as ( select ar.from_user , ah.host_id, ar.review_score, ah.nationality, row_number() over (partition by ar.from_user order by ar.review_score desc) as rnk from airbnb_reviews ar inner join airbnb_hosts ah on ar.to_user = ah.host_id where ar.from_type ='guest' ) select distinct from_user , nationality from summary_data where rnk=1 ;

[@vigneshiyer2424]

Great question! Any alternative methods to solve this?

[@stratascratch]

Yeah, probably a better method is to use a CASE statement. It'll make the code cleaner. Try it in the link to the question in the description. Thanks for watching!

[@RaviSingh-xx2wq]

You could try my solution but I think Nate's is more efficient: SELECT b.from_user, b.nationality FROM (SELECT from_user, Dense_rank() OVER( partition BY from_user ORDER BY score DESC) AS rnk, score, nationality FROM (SELECT from_user, Max(review_score) AS score, nationality FROM airbnb_reviews r INNER JOIN airbnb_hosts h ON r.to_user = h.host_id WHERE from_type = 'guest' GROUP BY 1, 3) a)b WHERE rnk = 1

[@emadhussain1776]

I observed one anomaly in the data where host and guest user id is same in airbnb_reviews table, which means host himself acted as a guest and gave a max or higher rating. We should remove this in filter where from_user and to_user is not equal. Please comment

[@stratascratch]

Yea you're right. I didn't even notice that. Thanks!

[@hassamkafeel]

with a as (select from_user, review_score, h.nat as natt, to_user, dense_rank() over (partition by from_user order by review_score desc) as rank_ed from airbnb_reviews inner join (select distinct(host_id) as id, nationality as nat from airbnb_hosts) as h on h.id = to_user where from_type = 'guest' and to_type = 'host') select distinct(natt),from_user, rank_ed, review_score from a where rank_ed = 1 order by 2