“Ranking Hosts By Beds” — Airbnb Data Interview Question for Hospitable Data Scientist🎖️🛏️
“Ranking Hosts By Beds” — Airbnb Data Interview Question using Python, Pandas, and Postgresql
Dear 👋💻🌎 Hospitable Data Scientist,
MAKING LEARNING HOW TO CODE
✧・゚:* CUTE(◕‿◕✿) and INFORMATIVEᕙ(⇀‸↼‶)ᕗ!!!
Interview Question: Rank🎖️ each host based on the number of beds 🔢 🛏️ they have listed📝.
The host with the most beds 🛏️ should be:
- Ranked 1 🥇
The host with the least number of beds 🛏️ should be:
- Ranked last 🥉
- Hosts that have the same number of beds 🔢 🛏️ should have the same rank 🎖️.
- A host can also own multiple properties ✖️🏠.
- Output the host ID, number of beds, and rank from highest rank to lowest 🥇⮕🥉.
Airbnb Apartments Data Schema 🏗️
CREATE TABLE airbnb_apartments (
host_id int,
apartment_id varchar,
apartment_type varchar,
n_beds int,
n_bedrooms int,
country varchar,
city varchar
);
Data 📊
Expected Output ✅
All required columns ✅🏛️ and the first 5 rows 5️⃣🚣
of the solution are shown below:
Hints in Postgresql 🐘
- Use a combination of DENSE_RANK( ) and OVER( ) functions to order records by the number of beds 🔢 🛏 ️ and retrieve the rank 🎖️.
- Summarize total beds number by the host as there are hosts who own more than one property 🏠
Using Postgresql 🐘
Step 1: SELECT host id FROM Airbnb apartments table.
SELECT host_id FROM airbnb_apartments; -- ⬅ Step 1️⃣
Step 2: SUM of the number of beds column and GROUP BY host id.
SELECT host_id,
SUM(n_beds) as number_of_beds -- ⬅ Step 2️⃣
FROM airbnb_apartments
GROUP BY host_id; -- ⬅ Step 2️⃣
Step 3: DENSE_RANK OVER the SUM of the number of beds in DESCending ORDER name column as rank
SELECT host_id, SUM(n_beds) as number_of_beds,
DENSE_RANK( ) OVER(ORDER BY SUM(n_beds) DESC) as rank -- ⬅ Step 3️⃣
FROM airbnb_apartments
GROUP BY host_id;
Step 4: ORDER BY number of beds in DESCending ORDER
SELECT
host_id,
SUM(n_beds) as number_of_beds,
DENSE_RANK() OVER(ORDER BY SUM(n_beds) DESC) as rank
FROM airbnb_apartments
GROUP BY host_id
ORDER BY number_of_beds DESC; -- ⬅ Step 4️⃣
Code Solution 💻✅:
SELECT
host_id,
SUM(n_beds) as number_of_beds,
DENSE_RANK() OVER(ORDER BY SUM(n_beds) DESC) as rank
FROM airbnb_apartments
GROUP BY host_id
ORDER BY number_of_beds DESC;
Hints in Python with Pandas 🐍🐼
- Use .rank(method = ‘dense’, order) to compute for the numerical dense ranking in a certain order of the column values
- Use sort_values(column_name, order) to sort along a specified column
Using Pandas 🐼
Step 1 import pandas and Airbnb apartments data frame .groupby() host id then .sum() the number of beds.
import pandas as pd # ⬅ Step 1️⃣result = airbnb_apartments.groupby('host_id')['n_beds'].sum() # ⬅ Step 1️⃣
Step 2 Rename a number of beds and reset the index.
import pandas as pdresult = airbnb_apartments.groupby('host_id')['n_beds'].sum().to_frame('number_of_beds').reset_index()
Step 3 Rank method to compute for the numerical dense ranking for number of beds
import pandas as pdresult = airbnb_apartments.groupby('host_id')['n_beds'].sum().to_frame('number_of_beds').reset_index()result['rank'] = result['number_of_beds'].rank(method = 'dense',ascending = False) # ⬅ Step
Step 4 Sort values by rank
import pandas as pdresult = airbnb_apartments.groupby('host_id')['n_beds'].sum().to_frame('number_of_beds').reset_index()result['rank'] = result['number_of_beds'].rank(method = 'dense',ascending = False)result = result.sort_values(by='rank') # ⬅ Step 4️⃣
Code Solution 💻 ✅:
import pandas as pdresult = airbnb_apartments.groupby('host_id')['n_beds'].sum().to_frame('number_of_beds').reset_index()result['rank'] = result['number_of_beds'].rank(method='dense',ascending=False)result = result.sort_values(by='rank')
Comparison PostgreSQL and Pandas Together
Thank you for reading my Data Journey ❤ ,
Kody the Coding Corgi & Bits the Adorable A.I.
P.S.
If you enjoyed this comic strip and could help you in any way, sign up for our newsletter, or buy me a boba, which means a lot, and send your thoughts and feelings about this work.
Are you interested in collaborating? Follow us on LinkedIn.
D.M. us on Instagram or tweet us on Twitter or connect us on LinkedIn.
Please share this with your data friends, corgis friends, and coding corgis friends so we can make more comics in the future with your support. Thank You!