Ranking Hosts by Beds

“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 🏗️

Table Schema: Airbnb Apartments

CREATE TABLE airbnb_apartments (
  host_id int,
  apartment_id varchar,
  apartment_type varchar,
  n_beds int, 
  n_bedrooms int, 
  country varchar,
  city varchar
);

Data 📊

airbnb_apartments.csv

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 🐘

Postgresql a database query language

Step 1: SELECT host id FROM Airbnb apartments table.

Step 1 in Postgresql

SELECT host_id FROM airbnb_apartments; -- ⬅ Step 1️⃣

Step 2: SUM of the number of beds column and GROUP BY host id.

Step 2 in Postgresql

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

Step 3 in Postgresql

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

Step 4 in Postgresql

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 pd
result = 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 pd
result = 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 pd
result = 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 pd
result = 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!