Modular_SQL_Final_Query.sql

WITH 

--****************************************************************************************************
--INFO BY neighbourhood
--****************************************************************************************************
-- Structure------------------------------------------------------------------------------------------
Neighbourhood_List AS 
(
SELECT DISTINCT 
       neighbourhood
FROM bcn_airbnb_dataset
),

--MODULE 1: MOST COMMON APARTMENT TYPE BY neighbourhood---------------------------------------------------------
Apartment_Property_Type_By_Neighbourhood AS 
(
SELECT DISTINCT 
       neighbourhood,
       property_type,
       COUNT(DISTINCT id)                                                  AS num_apartments

FROM bcn_airbnb_dataset 
GROUP BY 1,2
ORDER BY 3 DESC
),

Most_Common_Apartment_Property_Type_By_Neighbourhood AS 
(
SELECT neighbourhood,
       property_type,
       MAX(num_apartments) AS max_num
FROM Apartment_Property_Type_By_Neighbourhood
GROUP BY 1
ORDER BY 3 DESC
),

--MODULE 2: All Info about Hosts in Barcelona-------------------------------------------------------------------
Hosts_BCN AS 
(
SELECT DISTINCT 
       neighbourhood,
       COUNT(DISTINCT host_id)                                             AS num_hosts,
       COUNT(DISTINCT id)                                                  AS num_apartments,
       ROUND(CAST(COUNT(DISTINCT id) AS FLOAT)/COUNT(DISTINCT host_id),2)  AS mean_apartments_by_host
FROM bcn_airbnb_dataset 
GROUP BY 1
ORDER BY 2 DESC
),

--MODULE 3: Average price and reviews in BCN by neighbourhood---------------------------------------------------
AVG_price_reviews_BCN_by_neighbourhood AS 
(
SELECT DISTINCT 
       neighbourhood,
       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
),

--****************************************************************************************************
--MODULE 4: GLOBAL INFO BCN
--****************************************************************************************************
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 
-- Main Structure
Structure.neighbourhood,
-- Hosts Info
Hosts.num_hosts,
100*ROUND(CAST(Hosts.num_hosts AS FLOAT)/Global.num_hosts,2)               AS ptg_hosts,           
Hosts.num_apartments,
100*ROUND(CAST(Hosts.num_apartments AS FLOAT)/Global.num_apartments,2)     AS ptg_apartments,  
Hosts.mean_apartments_by_host                                              AS ap_by_host,
-- Common Apartment
Common_Apartment.property_type                                             AS most_common,
100*ROUND(CAST(Common_Apartment.max_num AS FLOAT)/Hosts.num_apartments,2)  AS ptg_most_com,
-- AVG price and reviews
AVG_by_neighbourhood.AVG_price,
AVG_by_neighbourhood.AVG_cleanliness,
AVG_by_neighbourhood.AVG_location
FROM Neighbourhood_List                                            AS Structure
LEFT JOIN Hosts_BCN                                                AS Hosts
  ON Structure.neighbourhood = Hosts.neighbourhood
LEFT JOIN Most_Common_Apartment_Property_Type_By_Neighbourhood     AS Common_Apartment
  ON Structure.neighbourhood = Common_Apartment.neighbourhood
LEFT JOIN AVG_price_reviews_BCN_by_neighbourhood                   AS AVG_by_neighbourhood
  ON Structure.neighbourhood = AVG_by_neighbourhood.neighbourhood
CROSS JOIN Data_global_BCN                                         AS Global
ORDER BY Hosts.num_hosts DESC