During the hackathon, we performed some queries on the aggregate data that would be nearly impossible with the existing API. Here are a few that focus on buildings classified as a
Single Family Residence. As of November 7, 2013, there were 109,093 of these buildings listed in Washoe county.
New home construction peaked in 2005 and dropped significantly until 2010. Despite the slight increase in recent years, construction remains at a level not seen since the early 1980s.
Likewise, sales of single family homes peaked in 2005 and are currently at about 60% of that high.
The sale price per square foot of homes underwent a similar decrease. Because of extreme outliers in this query, results were limited to sales less than $1.5 million and homes less than 10,000 square feet. A large number of zero dollar sales were also removed - nearly 50% of the data set.
|Year||Sales||Avg. Ft2||$ / Ft2|
* All 2013 data is year to date.
The queries were performed using the following SQL statements. To find the number of single family homes:
SELECT COUNT(buildings.parcel) FROM buildings WHERE type = 'Single Family Residence'
To produce the table and graph of new home construction:
SELECT original_year AS year, COUNT(buildings.parcel) FROM buildings WHERE type = 'Single Family Residence' GROUP BY 1 ORDER BY 1
For home sales:
SELECT EXTRACT(year from sales.date) as year, COUNT(sales.id) FROM sales JOIN buildings ON buildings.parcel = sales.parcel WHERE type = 'Single Family Residence' GROUP BY 1 ORDER BY 1
And the price per square foot:
SELECT EXTRACT(year from sales.date) as year, COUNT(sales.id), AVG(properties.building_sqft), AVG(sales.amount / properties.building_sqft) FROM sales JOIN buildings ON buildings.parcel = sales.parcel JOIN properties ON properties.parcel = sales.parcel WHERE type = 'Single Family Residence' AND sales.amount <> 0 AND sales.amount < 1500000 AND properties.building_sqft <> 0 AND properties.building_sqft < 10000 GROUP BY 1 ORDER BY 1