Sydney Airbnb Market Analysis - SQL Skills Demonstration

import sqlite3
import pandas as pd

# Load the dataset
df = pd.read_csv('listings.csv')

# Create SQLite database connection
conn = sqlite3.connect('mytable.db')

# Save dataframe as SQL table
df.to_sql(name="listings", con=conn, if_exists='replace', index=False)

# Commit changes
conn.commit()

# Connect to database for SQL queries
%load_ext sql
%sql sqlite:///mytable.db

-- Explore different room types available
SELECT DISTINCT room_type
FROM listings;

-- Count total listings
SELECT COUNT(DISTINCT id) as total_listings
FROM listings;

-- Room type distribution
SELECT DISTINCT room_type, count(room_type) as listing_count
FROM listings
GROUP BY room_type;

-- Average price and reviews by room type
SELECT DISTINCT room_type, 
       count(room_type) as listing_count,
       ROUND(avg(price), 2) as avg_price,
       ROUND(avg(number_of_reviews), 1) as avg_reviews
FROM listings
GROUP BY room_type
ORDER BY avg_price DESC;

-- Top 10 most expensive neighborhoods
SELECT DISTINCT neighbourhood, 
       count(id) as listing_count,
       ROUND(avg(price), 2) as avg_price,
       count(distinct host_id) as unique_hosts
FROM listings
GROUP BY neighbourhood
ORDER BY avg_price DESC
LIMIT 10;

-- Property type percentages by neighborhood (top 10)
SELECT neighbourhood,
    ROUND((COUNT(CASE WHEN room_type = 'Entire home/apt' THEN 1 END) * 100.0 / COUNT(*)),1) AS pct_entire_home,
    ROUND((COUNT(CASE WHEN room_type = 'Private room' THEN 1 END) * 100.0 / COUNT(*)),1) AS pct_private_room
FROM listings
GROUP BY neighbourhood
ORDER BY pct_entire_home DESC
LIMIT 10;

-- Load reviews data and join with listings
-- Find hosts with cleanliness complaints
SELECT host_id, host_name, name, 
       SUBSTR(comments, 1, 100) || '...' as comment_preview
FROM reviews 
INNER JOIN listings ON listings.id = reviews.listing_id
WHERE comments LIKE '%dirty%'
LIMIT 5;