Skip to content

Instantly share code, notes, and snippets.

@rfeers
Last active September 25, 2022 12:57
Show Gist options
  • Save rfeers/9ab947378826cf8b6d5acec863ed996f to your computer and use it in GitHub Desktop.
Save rfeers/9ab947378826cf8b6d5acec863ed996f to your computer and use it in GitHub Desktop.
SELECT
T1.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
(
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
) AS T1
CROSS JOIN
(
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
) AS T2
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment