-
Notifications
You must be signed in to change notification settings - Fork 0
/
airbnbanalysis.sql
43 lines (37 loc) · 1.3 KB
/
airbnbanalysis.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- SAN DIEGO AIRBNB ANALYSIS
-- Questions of Interest:
-- 1. Which neighborhoods have the highest and lowest listings?
-- 2. What are the most expensive and least expensive neighborhoods?
-- 3. Who is the host with the most listings?
-- 4. Which months are the most popular to book?
select * from listings;
-- which neighborhoods have the highest and lowest listings **
SELECT neighbourhood_cleansed, count(*) AS num_listings
FROM listings
GROUP BY neighbourhood_cleansed
ORDER BY num_listings DESC;
-- what are the most expensive and least expensive neighborhoods **
SELECT neighbourhood_cleansed,
AVG(CAST(REPLACE(REPLACE(price, '$', ''), ',', '') AS DECIMAL(10, 2))) AS avg_price
FROM listings
GROUP BY neighbourhood_cleansed
ORDER BY avg_price DESC;
-- hosts with most listings **
SELECT host_id, host_name, host_neighbourhood,
count(*) as num_listings
FROM listings
GROUP BY host_neighbourhood, host_id, host_name
ORDER BY num_listings DESC;
-- which months are the most popular to book in top 5 neighborhoods?
SELECT
MONTH(last_review) AS month,
COUNT(name) AS num_reviews
FROM
listings
WHERE
last_review IS NOT NULL
AND neighbourhood_cleansed IN ('La Jolla', 'Mission Bay', 'North Hills', 'Ocean Beach', 'Pacific Beach')
GROUP BY
MONTH(last_review)
ORDER BY
num_reviews DESC;