NEWS Real Estate Market Analysis

SELECT * FROM 'cali_houses_2020.csv'

--property price change in 2020
SELECT
    EXTRACT(MONTH FROM datePostedString) AS Month,
    EXTRACT(YEAR FROM datePostedString) AS Year,
    percentile_disc(0.5) WITHIN GROUP (ORDER BY price) AS MedianPrice
FROM 'cali_houses_2020.csv'
GROUP BY EXTRACT(MONTH FROM datePostedString), EXTRACT(YEAR FROM datePostedString)
ORDER BY EXTRACT(YEAR FROM datePostedString), EXTRACT(MONTH FROM datePostedString)

--correlation between property features (size, number of bedrooms,etc...) and their prices
SELECT
price,
livingArea,
bedrooms,
bathrooms
FROM 'cali_houses_2020.csv'
WHERE 
price IS NOT NULL
AND livingArea IS NOT NULL
AND bedrooms IS NOT NULL
AND bathrooms IS NOT NULL

--query top 3 highest median price per county
SELECT
county,
percentile_disc(0.5) WITHIN GROUP (ORDER BY price) AS high_med_price
FROM 'cali_houses_2020.csv'
GROUP BY county
ORDER BY high_med_price DESC
LIMIT 3

--query top 3 lowest median price per county
SELECT
county,
percentile_disc(0.5) WITHIN GROUP (ORDER BY price) AS low_med_price
FROM 'cali_houses_2020.csv'
GROUP BY county
ORDER BY low_med_price
LIMIT 3

--query for relationship between property location (county or city) and its price
SELECT 
	county,
	'California' AS State,
    percentile_disc(0.5) WITHIN GROUP (ORDER BY price) AS MedianPrice
FROM 'cali_houses_2020.csv' 
WHERE price IS NOT NULL AND livingArea IS NOT NULL
GROUP BY county