Analyzing Paris Airbnb Listings with SQL

Photo by Adrien on Unsplash

Airbnb is a popular online marketplace that connects people who want to rent out their homes or apartments with travelers who need a place to stay. As a host or a guest on Airbnb, you may be interested in analyzing the listings data to gain insights into the market and make informed decisions. In this post, we’ll explore how to use SQL to analyze Paris Airbnb listings data.

The Airbnb listings data contains information about the properties that are available for rent on the platform, such as the property type, location, availability, and pricing. The data is available in a CSV file that can be loaded into a SQL database for analysis. Here is the link to the data source — http://insideairbnb.com/get-the-data/

Photo by Rubaitul Azad on Unsplash

In this blog post, we will take a look at the SQL queries used in this project and understand how they work.

Query 1: Selecting all columns from a table

In the first query we will selects all columns from a table. Here’s the code:

select * from listings

Query 2: Calculating projected revenue

The second query is a more complex query that calculates projected revenue for a set of listings. Here’s the code:

SELECT TOP 20
  id,
  listing_url,
  name,
  30 - availability_30 AS booked_out_30,
  TRY_CAST(REPLACE(price, '$', '') AS FLOAT) AS price_clean,
  TRY_CAST(REPLACE(price, '$', '') AS FLOAT) * CAST(30 - availability_30 AS BIGINT) / beds AS proj_rev_30
FROM listings
WHERE ISNUMERIC(id) = 1
  AND CAST(id AS BIGINT) = id
  AND id NOT LIKE '%.%'
  AND id NOT LIKE '%e%'
  AND id NOT LIKE '%E%'
ORDER BY proj_rev_30 DESC;

This query selects the top 20 listings with the highest projected revenue for the next 30 days. It calculates the projected revenue by multiplying the price per night by the number of nights that are available in the next 30 days and dividing by the number of beds in the listing.

Query 3: Finding the most common value

In this query we will be able to find the top 10 popular Airbnb hosts in Paris. Here is the code:

SELECT TOP 10 host_name
FROM listings
GROUP BY host_name
ORDER BY count(*) DESC;

Conclusion:

In this blog post, we have explored some SQL queries on a table called “listings” to analyze and extract useful insights.

First, we selected all columns from the table using the simple SELECT * statement. Next, we calculated the projected revenue for the top 20 listings based on their availability, price, and beds. To achieve this, we used various SQL functions like TRY_CAST, REPLACE, and CAST.

In the third query, we found the top 10 popular hosts based on the number of listings they had. We used the GROUP BY clause to group the data by host name and the ORDER BY clause to sort the data in descending order of count.

By applying these queries on the “listings” table, we were able to extract useful insights and make informed decisions based on the data. SQL is a powerful tool for data analysis and can be used to uncover hidden patterns and trends in large datasets.

For detailed code and project details, please refer to the Github repository. If you have any suggestions on the project, please do not hesitate to reach out to me on LinkedIn

Follow Amulya Kulkarni, for more such blog posts. Happy Learning!