Real Estate Market Urban vs Suburbs, 1 year into Covid: SQL Analysis

  • TL:DR
    • People are leaving city for suburbs
    • SF market slows, Suburban market is HOT
    • Lower demand for SF >>  prices flattened
    • Very low supply in suburbs >> 25% increase in price
    • Real estate businesses like brokerages and OpenDoor may struggle in certain suburban markets 
    • Analyzed data with SQL (Ex: Table Creation, Queries, Aggregate Functions, Joins, Inline Queries) using SQLite (in appendix)

Background

Through the pandemic, we have seen a large scale exodus away from cities to suburbs. Quarantine has emphasised the living pains of high density, expensive, and cramped city life, while the attractive social attractions and city culture have been restricted. Work/school/life at home has been especially stressful to families. The pandemic was initially thought to severely depress the housing market. Rental market has been noticeably depressed especially in the city; however, the dramatic housing market slowdown was largely not realized. 

This quick analysis aims to see how the market has changed one year into the pandemic, highlighting the urban market in San Francisco, and three SF suburbs. I’ll look specifically at sales volume, days on the market (DOM), and price changes. I look at how this impacts Opendoor as well. 

Hypothesis

Due to the high living situation turnover, and attractiveness of suburbia during the pandemic, I predict a stable to slight increase in housing sale prices in both the urban and suburban markets. From personal observation, I believe that total volume of sales has decreased while DOM has reduced. I believe there is generally low supply and high demand. 

Specific Cities and Questions Looked At

I’m looking specifically at San Francisco and three outlying suburbs. San Francisco Bay Area is the largest urban metropolitan area in the West Coast, with San Francisco the city heart. Three suburbs I’ll look at are Davis, Livermore, and Tracy, which are common commuter cities. 

Sales bunched in these criteria:

  • Urban (San Francisco) vs Suburban (Davis, Livermore, Tracy)
  • Three sale price buckets: Lower quartile (0-25%), interquartile (25-75%), upper quartile (75-100%)
  • Residential (single family/condo/townhouse)
  • Pre-pandemic: sold between 1/1/2020-2/28/2020
  • One year into pandemic: sold between 1/1/2021-2/28/2021

Specific questions within each buckets:

  • Change in total sales volume, DOM, sales price
  • How are markets changing? 
  • Where are people moving?

Data Results:

Total number of Transactions in Urban/Suburban, prepandemic/pandemic

Average Sales Price and DOM

Shift from Cities to Suburbs

  • SF market is weak, prices stagnated for the first time since recession. 
  • Suburb exhibit a strong sellers market, large demand, low supply. Houses flew off the shelves. Sale prices increase 25%. 
  • Shift in demand from SF to suburbs. People in suburbs staying put
  • Higher value homes tended to grow in value compared to lower value homes in urban and suburban areas. 

The results tell a narrative that people are moving from the cities to suburbs. People in the suburbs are staying put, and not selling. 

The SF housing market has cooled down after a very strong run for the past 10 years. Lower quartile houses dropped -12% in value, while upper quartile homes increased +8%. On average, there was little value change at +0.5%. The buying market is not hot. DOM has increased +82% for all brackets, to 53 days. Demand has dropped in SF, especially for lower value homes. Not as many people are wanting to move to SF during the pandemic. 

Suburban markets tell a very different story. In one year, housing prices across all brackets increased a whopping +25%. Q1 houses increased +18%, and Q4 houses +34%. Furthermore, homes that get listed are super hot and purchased very quickly. Average DOM dropped -62% to 13 days. Total transactions also lowered a dramatic -58%. Supply for suburban houses has dropped significantly, leading to higher prices. (is this supply shock?)

An interesting insight is that suburban DOM dropped while urban DOM increased. Low DOM indicates a strong seller market. High DOM indicates a strong buyer market. In 2021, the suburban average DOM was 13 days; IQR DOM was only 8 days. The average DOM dropped -62%. That is incredibly fast to close a house deal indicating a very strong sellers market. SF had a DOM 56 days, indicating a strong buyers market. This is an 82% increase DOM from last year. There is a huge shift in buyer demand from SF to suburban markets. 

In both urban and suburban markets, upper quartile houses held/gained the most value. Even in SF, where markets softened, Q4 houses still gained 8% value. Suburban q4 houses increased in value 14% more points than Q1 houses (Q1: +18% , Q4: 34%). 

Drop in Volume could hurts Suburban Realtors, even with higher Suburban Prices

The drop in total sales volume in suburbs (especially lower-priced ones) could damage to suburban real estate businesses, such as independent brokerages and Opendoor. Brokerages rely heavily on sales commission for revenue. Even though sales prices rose +25%, the -58% drop in volume is damaging. Brokerages in the city are hurting less, as overall volume and sales price has not changed drastically. 

While we didn’t analyze OpenDoor cities specifically, OpenDoor could also be hurting with their focus on average to lower priced (100-400K homes), most of which are in suburbs. From 2020 OpenDoor balance sheets, gross profit was down 50% from the year prior. This drop in supply is damaging for OpenDoor. However, I think that the supply shortage will recover in 2021, hopefully as vaccines and reopening is rolled out. If that occurs, some people will move back to urban areas and transaction volume will pick up again. I think even after a correction of people moving back to the cities, there will be a long lasting desire to trend to stay in suburbs. This long term trend should grow the suburban market, and help OpenDoor.

I believe suburban brokerages and businesses like Opendoor will recover to prepandemic operations, but only after the markets return to prepandemic volumes.

Retrospective

I have personally felt the suburban migration as I personally know many city dwelling friends which have moved away. This analysis highlights how this change in preference has led to a drastic increase in suburban prices. Figuring out how people are thinking, what actions they take, and how these behaviours change markets is super valuable in investing, business decisions, and even as ‘small’ as deciding where to live. 

I think there is still much to be explored:

  • An expanded time scope
  • Other areas: suburbs within the SF metropolitan area. Non-California cities/suburbs. Middle America.
  • including all listings, (unsold houses, non-residential)

One shortcoming of this analysis is the limited time scope. I looked at only Jan-Feb, one year apart. There is a gap in the story in the 10 months between. The immediate covid market shock during March and April is missing. 

To capture a snapshot view, I only looked at SF and 3 suburbs. Expanding to other SF suburbs, and other cities would be valuable. I would not be surprised if middle American city markets behaved very differently than the SF bubble.

This analysis also only captures closed transactions; it does not capture houses that are listed but not sold. It is theoretically possible that hundreds of houses in the suburbs are on the market for months unsold, and not captured by this analysis. I don’t think this is true, but there are likely a few unsold houses that meaningfully change the results, particularly in SF, where DOM is higher. Next time, I would get data that also includes the active listings. 

Additionally, commercial real estate is highly affected as well. Restaurants, salons, and hundreds of businesses are directly affected by covid and have had to close shop. Analysis on commercial property would be very interesting. 

Appendix: SQL Code

I got raw data in the form of multiple csv files. Using pandas and sqlite3, I joined them in python, and converted it to a dataframe, then a SQL database. The csv converted all the columns as text, which I corrected. I created a second table, <cities>, which labels each city as either suburb/urban. 

All in all, I used roughly 4-5 different queries (excluding creating the SQL tables).

Query example 1: The inner query takes the sales data and adds a quartile bucket based on sales price. It partitions the sales in urban/suburban. (two lists) The new table then averages the sales price and DOM grouped by city class and quartile. The result gives us the average sale price, in each quartile, in each city class. I repeated the query for 2021 for pandemic numbers. 

Query Example 2: This query counts the number of sales. I joined the sales table with city classifier table, and counted the sales for each urban/suburban and pre-pandemic/pandemic group.