Another Airbnb SQL Interview Question for Data Scientists and Data Analysts (StrataScratch 9636)

Solution and walkthrough of a real SQL interview question for Data Scientist and Data Analyst technical coding interviews. This question was asked by Airbnb and is called "Cheapest Neighborhoods With Real Beds And Internet".

Playlists:

[frederikmuller]

Solution with LIMIT:

SELECT neighbourhood FROM airbnb_search_details
WHERE bed_type = 'Real Bed'
AND property_type = 'Villa'
AND lower(amenities) LIKE '%internet%'
ORDER BY price
LIMIT 1

[frederikmuller]

Solution with MIN() subquery:

SELECT neighbourhood FROM airbnb_search_details
WHERE price =

(SELECT MIN(price) FROM airbnb_search_details
WHERE bed_type = 'Real Bed'
AND property_type = 'Villa'
AND LOWER(amenities) LIKE '%internet%')

AND bed_type = 'Real Bed'
AND property_type = 'Villa'
AND LOWER(amenities) LIKE '%internet%'

[shobhamourya]

Using cte:
with cte1
as
(
select price, bed_type, property_type, amenities, neighbourhood
from airbnb_search_details
where bed_type ilike 'Real Bed'
and property_type ilike 'Villa'
and amenities ilike '%internet%'
order by price
),
cte2
as
(
select neighbourhood, price, amenities,
dense_rank() over(order by price) cheapest
from cte1
order by cheapest
)
select distinct neighbourhood
from cte2
where cheapest = 1

[chillvibe]

The question is misleading. It says "Find neighbourhoods" but the solution is the ONE neighbourhood with the cheapest villa.What I did was I output neighbourhoods with the cheapest villa with a bed and internet in each.