Medium_SQL_modular_3.sql

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