Tutorial - Housing Data Analytics & Business Intelligence

SELECT COUNT(id) as newly_listed_properties
FROM listings
WHERE amenities @> '["Wifi", "Elevator"]'
    AND last_scraped BETWEEN CURRENT_DATE - INTERVAL '30 days' AND CURRENT_DATE;

WITH VerificationCounts AS (
    SELECT 
        host_id,
        jsonb_array_elements_text(host_verifications_jsonb) AS verification_method
    FROM listings
    WHERE host_verifications_jsonb IS NOT NULL
)
SELECT 
    verification_method, 
    COUNT(DISTINCT host_id) AS host_count,
    ROUND(COUNT(DISTINCT host_id) * 100.0 / 
          (SELECT COUNT(DISTINCT host_id) FROM listings), 2) AS percentage
FROM VerificationCounts
GROUP BY verification_method
ORDER BY host_count DESC;

verification_method | host_count | percentage
-------------------+-----------+-----------
email              |       2,847|      68.4
phone              |       2,156|      51.8
work_email         |         892|      21.4
government_id      |         634|      15.2

SELECT 
    id,
    host_name,
    host_response_rate,
    host_about,
    number_of_reviews,
    review_scores_rating
FROM listings
WHERE 
    host_response_rate > '90%'
    AND host_identity_verified = 't'
    AND review_scores_rating >= 4.5
ORDER BY review_scores_rating DESC, number_of_reviews DESC;

WITH HostAverageRating AS (
    SELECT 
        host_id,
        AVG(review_scores_rating) AS avg_rating,
        COUNT(*) as property_count
    FROM listings
    WHERE review_scores_rating IS NOT NULL
    GROUP BY host_id
    HAVING COUNT(*) >= 2  -- Hosts with multiple properties
),
VerificationRatings AS (
    SELECT 
        jsonb_array_elements_text(l.host_verifications_jsonb) AS verification_method,
        h.avg_rating,
        h.property_count
    FROM listings l
    JOIN HostAverageRating h ON l.host_id = h.host_id
    WHERE l.host_verifications_jsonb IS NOT NULL
)
SELECT 
    verification_method,
    ROUND(AVG(avg_rating), 2) as average_rating,
    COUNT(*) as host_count,
    ROUND(AVG(property_count), 1) as avg_properties_per_host
FROM VerificationRatings
GROUP BY verification_method
HAVING COUNT(*) >= 50  -- Statistically significant sample
ORDER BY average_rating DESC;

verification_method | average_rating | host_count | avg_properties_per_host
-------------------+---------------+-----------+------------------------
government_id      |           4.73|        234|                     2.3
work_email         |           4.68|        445|                     2.1
phone              |           4.52|        892|                     1.8
email              |           4.48|      1,234|                     1.7

WITH AmenityPopularity AS (
    SELECT 
        jsonb_array_elements_text(amenities) AS amenity,
        COUNT(id) AS listings_count,
        AVG(review_scores_rating) as avg_rating,
        AVG(price::numeric) as avg_price
    FROM listings
    WHERE amenities IS NOT NULL
    GROUP BY amenity
    HAVING COUNT(id) >= 100  -- Statistically significant
)
SELECT 
    amenity,
    listings_count,
    ROUND(avg_rating, 2) as avg_rating,
    ROUND(avg_price, 0) as avg_price_hkd,
    ROUND(listings_count * 100.0 / (SELECT COUNT(*) FROM listings), 1) as market_penetration_pct
FROM AmenityPopularity
ORDER BY listings_count DESC
LIMIT 15;

amenity           | listings_count | avg_rating | avg_price_hkd | market_penetration_pct
-----------------+--------------+----------+--------------+----------------------
Wifi             |         5,234|      4.52|          892|                   77.8
Kitchen          |         4,567|      4.48|          1,045|                  67.8
Air conditioning |         3,892|      4.56|          978|                   57.8
Washer           |         3,445|      4.51|          1,123|                  51.2
Elevator         |         2,789|      4.59|          1,234|                  41.4

SELECT 
    id, 
    name, 
    neighbourhood,
    price,
    review_scores_rating
FROM listings
WHERE 
    amenities @> '["Wifi", "Pets allowed"]' 
    AND neighbourhood = 'Hong Kong, Hong Kong Island, Hong Kong'
    AND has_availability = true
ORDER BY review_scores_rating DESC, price ASC;

WITH RankedProperties AS (
    SELECT 
        id,
        name,
        neighbourhood_cleansed,
        review_scores_rating,
        number_of_reviews,
        price,
        RANK() OVER (
            PARTITION BY neighbourhood_cleansed 
            ORDER BY review_scores_rating DESC, number_of_reviews DESC
        ) as ranking
    FROM listings
    WHERE 
        review_scores_rating IS NOT NULL
        AND number_of_reviews >= 10  -- Ensure reliable ratings
        AND has_availability = true
)
SELECT 
    neighbourhood_cleansed,
    name,
    review_scores_rating,
    number_of_reviews,
    price
FROM RankedProperties
WHERE ranking <= 3  -- Top 3 per neighborhood
ORDER BY neighbourhood_cleansed, ranking;

SELECT 
    r.listing_id,
    l.name,
    r.comments,
    l.amenities
FROM reviews r
JOIN listings l ON r.listing_id = l.id
WHERE 
    l.amenities @> '["Wifi"]'
    AND (
        r.comments ILIKE '%wifi%' 
        OR r.comments ILIKE '%internet%'
        OR r.comments ILIKE '%connection%'
    )
    AND (
        r.comments ILIKE '%excellent%'
        OR r.comments ILIKE '%fast%'
        OR r.comments ILIKE '%reliable%'
    )
ORDER BY l.review_scores_rating DESC
LIMIT 10;

WITH PropertyMetrics AS (
    SELECT 
        id,
        price::numeric as price_numeric,
        beds,
        accommodates,
        review_scores_rating,
        neighbourhood_cleansed,
        room_type,
        jsonb_array_length(amenities) as amenity_count,
        CASE 
            WHEN amenities @> '["Pool"]' THEN 1 ELSE 0 
        END as has_pool,
        CASE 
            WHEN amenities @> '["Hot tub"]' THEN 1 ELSE 0 
        END as has_hot_tub,
        CASE 
            WHEN amenities @> '["Gym"]' THEN 1 ELSE 0 
        END as has_gym
    FROM listings
    WHERE price IS NOT NULL AND amenities IS NOT NULL
)
SELECT 
    room_type,
    ROUND(AVG(price_numeric), 0) as avg_price,
    ROUND(AVG(amenity_count), 1) as avg_amenity_count,
    ROUND(AVG(has_pool) * 100, 1) as pool_percentage,
    ROUND(AVG(has_hot_tub) * 100, 1) as hot_tub_percentage,
    ROUND(AVG(has_gym) * 100, 1) as gym_percentage,
    COUNT(*) as property_count
FROM PropertyMetrics
GROUP BY room_type
ORDER BY avg_price DESC;

room_type        | avg_price | avg_amenity_count | pool_percentage | hot_tub_percentage | gym_percentage | property_count
----------------+----------+------------------+----------------+-------------------+---------------+---------------
Entire home/apt |     1,245|              23.4|             8.2|               12.4|            5.7|          3,456
Private room    |       567|              18.9|             2.1|                3.8|            2.1|          2,123
Shared room     |       234|              14.2|             0.8|                1.2|            1.2|            234

WITH NeighborhoodMetrics AS (
    SELECT 
        neighbourhood_cleansed,
        COUNT(*) as total_listings,
        AVG(review_scores_rating) as avg_rating,
        AVG(price::numeric) as avg_price,
        AVG(number_of_reviews) as avg_review_count,
        SUM(CASE WHEN has_availability = true THEN 1 ELSE 0 END) as available_listings
    FROM listings
    WHERE neighbourhood_cleansed IS NOT NULL
    GROUP BY neighbourhood_cleansed
    HAVING COUNT(*) >= 20  -- Focus on established markets
)
SELECT 
    neighbourhood_cleansed,
    total_listings,
    ROUND(avg_rating, 2) as avg_rating,
    ROUND(avg_price, 0) as avg_price_hkd,
    ROUND(avg_review_count, 1) as avg_review_count,
    ROUND(available_listings * 100.0 / total_listings, 1) as availability_rate,
    CASE 
        WHEN avg_price > 1000 AND avg_rating > 4.5 THEN 'Premium Market'
        WHEN avg_price > 800 AND total_listings < 100 THEN 'Growth Opportunity'
        WHEN availability_rate > 80 THEN 'High Supply'
        ELSE 'Balanced Market'
    END as market_category
FROM NeighborhoodMetrics
ORDER BY avg_price DESC, avg_rating DESC;

WITH HostPerformance AS (
    SELECT 
        host_id,
        host_name,
        COUNT(*) as property_count,
        AVG(review_scores_rating) as avg_rating,
        AVG(number_of_reviews::numeric) as avg_review_count,
        host_response_rate,
        host_is_superhost,
        MAX(last_scraped) as last_activity
    FROM listings
    WHERE host_id IS NOT NULL
    GROUP BY host_id, host_name, host_response_rate, host_is_superhost
    HAVING COUNT(*) >= 2  -- Multi-property hosts
)
SELECT 
    host_name,
    property_count,
    ROUND(avg_rating, 2) as avg_rating,
    ROUND(avg_review_count, 1) as avg_review_count,
    host_response_rate,
    host_is_superhost,
    last_activity,
    CASE 
        WHEN avg_rating < 4.0 THEN 'Needs Rating Improvement'
        WHEN host_response_rate < '80%' THEN 'Needs Response Improvement'
        WHEN avg_review_count < 5 THEN 'Needs More Bookings'
        WHEN avg_rating >= 4.5 AND host_response_rate >= '90%' THEN 'Top Performer'
        ELSE 'Good Performance'
    END as performance_category
FROM HostPerformance
ORDER BY avg_rating ASC, host_response_rate ASC;

-- Index for amenity analysis
CREATE INDEX idx_analytics_amenities ON listings USING GIN(amenities);
 
-- Index for host analysis
CREATE INDEX idx_analytics_host ON listings(host_id, review_scores_rating, last_scraped);
 
-- Index for neighborhood analysis
CREATE INDEX idx_analytics_neighborhood ON listings(neighbourhood_cleansed, price, review_scores_rating);
 
-- Composite index for complex analytics
CREATE INDEX idx_analytics_composite ON listings(has_availability, room_type, price) 
INCLUDE (review_scores_rating, number_of_reviews);

-- Create materialized view for amenity popularity
CREATE MATERIALIZED VIEW mv_amenity_analytics AS
SELECT 
    jsonb_array_elements_text(amenities) AS amenity,
    COUNT(*) AS listings_count,
    AVG(review_scores_rating) as avg_rating,
    AVG(price::numeric) as avg_price,
    COUNT(CASE WHEN has_availability THEN 1 END) as available_count
FROM listings
WHERE amenities IS NOT NULL
GROUP BY amenity;
 
-- Refresh periodically
REFRESH MATERIALIZED VIEW mv_amenity_analytics;

-- Platform overview dashboard
SELECT 
    'Total Properties' as metric,
    COUNT(*)::text as value
FROM listings
UNION ALL
SELECT 
    'Available Properties',
    COUNT(*)::text
FROM listings WHERE has_availability = true
UNION ALL
SELECT 
    'Average Rating',
    ROUND(AVG(review_scores_rating), 2)::text
FROM listings WHERE review_scores_rating IS NOT NULL
UNION ALL
SELECT 
    'Total Reviews',
    COUNT(*)::text
FROM reviews
UNION ALL
SELECT 
    'Active Hosts',
    COUNT(DISTINCT host_id)::text
FROM listings;

-- Revenue analysis dashboard
SELECT 
    DATE_TRUNC('month', last_scraped) as month,
    COUNT(*) as new_listings,
    AVG(price::numeric) as avg_price,
    SUM(price::numeric) as total_potential_revenue
FROM listings
WHERE last_scraped >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', last_scraped)
ORDER BY month DESC;