|
WITH |
|
|
|
-- Structure------------------------------------------------------------------------------------------ |
|
Neighbourhood_List AS |
|
( |
|
SELECT DISTINCT |
|
neighbourhood |
|
FROM bcn_airbnb_dataset |
|
), |
|
|
|
-- Info BY neighbourhood ------------------------------------------------------------------------------------------ |
|
Data_by_neighbourhood_BCN AS |
|
( |
|
SELECT DISTINCT |
|
neighbourhood, |
|
COUNT(DISTINCT id) AS num_apartments, |
|
COUNT(DISTINCT host_id) AS num_hosts, |
|
ROUND(CAST(COUNT(DISTINCT id) AS FLOAT)/COUNT(DISTINCT host_id),2) AS mean_apartments_by_host, |
|
ROUND(AVG(price),2) AS AVG_price, |
|
ROUND(AVG(review_cleanliness),2) AS AVG_cleanliness, |
|
ROUND(AVG(review_location),2) AS AVG_location |
|
FROM bcn_airbnb_dataset |
|
GROUP BY 1 |
|
ORDER BY 2 DESC |
|
), |
|
|
|
-- Global Info------------------------------------------------------------------------------------------ |
|
|
|
Data_global_BCN AS |
|
( |
|
SELECT DISTINCT |
|
COUNT(DISTINCT id) AS num_apartments, |
|
COUNT(DISTINCT host_id) AS num_hosts, |
|
ROUND(CAST(COUNT(DISTINCT id) AS FLOAT)/COUNT(DISTINCT host_id),2) AS mean_apartments_by_host, |
|
ROUND(AVG(price),2) AS AVG_price, |
|
ROUND(AVG(review_cleanliness),2) AS AVG_cleanliness, |
|
ROUND(AVG(review_location),2) AS AVG_location |
|
FROM bcn_airbnb_dataset |
|
ORDER BY 1 DESC |
|
) |
|
|
|
-- Final Query---------------------------------------------------------------------------------------- |
|
SELECT |
|
Structure.neighbourhood, |
|
T1.num_apartments, |
|
100*T1.num_apartments/T2.num_apartments AS comp_apartments, |
|
T1.num_hosts, |
|
100*T1.num_hosts/T2.num_hosts AS comp_hosts, |
|
T1.mean_apartments_by_host, |
|
T1.AVG_price, |
|
CASE WHEN T1.AVG_price > T2.AVG_price THEN "Above" |
|
WHEN T1.AVG_price = T2.AVG_price THEN "Equals" |
|
WHEN T1.AVG_price < T2.AVG_price THEN "Below" |
|
END AS "Price_comparison_to_city", |
|
T1.AVG_cleanliness, |
|
CASE WHEN T1.AVG_cleanliness > T2.AVG_cleanliness THEN "Above" |
|
WHEN T1.AVG_cleanliness = T2.AVG_cleanliness THEN "Equals" |
|
WHEN T1.AVG_cleanliness < T2.AVG_cleanliness THEN "Below" |
|
END AS "Clean_Comparison_to_city", |
|
T1.AVG_location, |
|
CASE WHEN T1.AVG_location > T2.AVG_location THEN "Above" |
|
WHEN T1.AVG_location = T2.AVG_location THEN "Equals" |
|
WHEN T1.AVG_location < T2.AVG_location THEN "Below" |
|
END AS "Location_Comparison_to_city" |
|
|
|
FROM Neighbourhood_List AS Structure |
|
LEFT JOIN Data_by_neighbourhood_BCN AS T1 |
|
ON Structure.neighbourhood = T1.neighbourhood |
|
CROSS JOIN Data_global_BCN AS T2 |