Medium_SQL_modular_4.sql

 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