SQL in Action: Cleaning and Analysing Nashville Housing Data

0. Collecting and Importing the Data

To start, I downloaded the dataset in CSV format from this link. I then transformed the CSV file into a suitable format (e.g., Excel) and imported it into SSMS.

1. Data Understanding and Exploration

To gain a deeper understanding of the dataset, I examined the structure and data types of the columns using the following SQL query:

  SELECT 
    COLUMN_NAME, DATA_TYPE
  FROM 
    PortfolioProject.INFORMATION_SCHEMA.COLUMNS
  WHERE 
    TABLE_NAME = 'NashvilleHousing';

The data consists of 19 columns and 56,477 rows, each representing specific attributes of the properties:

UniqueID: An ID number assigned to each buyer.

ParcelID: A unique code associated with each land parcel.

LandUse: Categorizes the different uses of land.

SalesPrice: The price of the land when sold.

LegalReference: A citation referencing authoritative documents and sources.

OwnerName: The name of the landowner.

Acreage: The size of the land area in acres.

LandValue: The assessed value of the land.

BuildingValue: The assessed value of the building on the land.

TotalValue: The sum of the land value and building value.

YearBuilt: The year the building was constructed.

FullBath: The number of full bathrooms in the property.

HalfBath: The number of half bathrooms in the property.

SaleDate: The date when the land was sold.

SaleAddress: The address of the sold land.

City: The location of the land.

OwnerAddress: The address of the landowner's house.

OwnerCity: The city where the landowner resides.

OwnerState: The state where the landowner is located.

2. Data Cleaning and Preprocessing

During the initial exploration, several data quality issues were identified and resolved to ensure accurate analysis:

Standardizing the Date Format: The SaleDate column was not in the standard format. I converted it to the appropriate date format.

  UPDATE 
      NashvilleHousing
  SET 
      SaleDate = CONVERT(Date, SaleDate)

Handling NULL Values: Some rows in the PropertyAddress column were found to be NULL. To address this, I updated the correct Address that link with the UniqeID.

  UPDATE a
  SET 
    PropertyAddress = b.PropertyAddress
  From 
    PortfolioProject..NashvilleHousing a
  JOIN 
    PortfolioProject..NashvilleHousing b
  ON 
    a.ParcelID = b.ParcelID
  AND 
    a.[UniqueID ] <> b.[UniqueID ]
  WHERE 
    a.PropertyAddress is null

Separating PropertyAddress into City and House Address: Since the PropertyAddress column contained both the city and house address, I spited it into two separate columns by SUBSTRING method for better analysis.

  ALTER TABLE 
    NashvilleHousing
  ADD 
    PropertyAddressNumber Nvarchar(255);
      
  UPDATE 
    NashvilleHousing
  SET 
    PropertyAddressNumber = SUBSTRING(PropertyAddress,1,CHARINDEX(',',PropertyAddress)-1)
      
  ALTER 
    TABLE NashvilleHousing
  ADD 
    PropertyAddressCity Nvarchar(255);
      
  UPDATE 
    NashvilleHousing
  SET 
    PropertyAddressCity = SUBSTRING(PropertyAddress,CHARINDEX(',',PropertyAddress)+1,LEN(PropertyAddress))

Splitting OwnerAddress: The OwnerAddress column contained the state, city, and address in a single column. I separated this information into three distinct columns by PARSENAME method to facilitate analysis.

 ALTER TABLE
    NashvilleHousing
  ADD
    OwnerAddressNumber Nvarchar(255);
      
  UPDATE 
    NashvilleHousing
  SET
    OwnerAddressNumber = PARSENAME(REPLACE(OwnerAddress,',','.'),3)
      
  ALTER TABLE 
    NashvilleHousing
  ADD
    OwnerAddressCity Nvarchar(255);
      
  UPDATE 
    NashvilleHousing
  SET
    OwnerAddressCity = PARSENAME(REPLACE(OwnerAddress,',','.'),2)
      
  ALTER TABLE 
    NashvilleHousing
  ADD 
    OwnerAddressState Nvarchar(255);
      
  UPDATE 
    NashvilleHousing
  SET
    OwnerAddressState = PARSENAME(REPLACE(OwnerAddress,',','.'),1)

Updating SoldAsVacant: The SoldAsVacant column had values of Y and N instead of Yes or No. I changed them to the appropriate format for consistency.

 UPDATE 
    NashvilleHousing
  SET 
    SoldAsVacant = CASE 
  WHEN 
    SoldAsVacant = 'Y' THEN 'Yes'
  WHEN 
    SoldAsVacant = 'N' THEN 'No'
  ELSE 
    SoldAsVacant
  END

Removing Duplicate Rows: Duplicate rows were identified and eliminated from the dataset.

 WITH RowNumCTE 
  AS(
    SELECT *,
      ROW_NUMBER() OVER (
    PARTITION BY ParcelID,
      PropertyAddress,
      SalePrice,
      SaleDate,
      LegalReference
    ORDER BY
       UniqueID) row_num
    FROM PortfolioProject..NashvilleHousing
      )
  DELETE
  FROM 
    RowNumCTE
  Where 
    row_num >1

3. Data Visualisation

After extracting the relevant data using SQL queries, I loaded the processed data into Tableau for data visualisation. Tableau offers powerful visualisation capabilities that allow for interactive exploration and presentation of the data. Moving forward, with the refined dataset in Tableau, I will delve into data analysis and visualisation to derive meaningful insights. By leveraging various Tableau features and visualisations, such as charts, graphs, and interactive dashboards, I will present the findings in a visually appealing and easily understandable manner. Several visualisations that are explored include:

Relationship between Average Sale Price and number of bedrooms for each District: This visualisation examines the relationship between the average sale price of properties and the number of bedrooms in each district of Nashville. By using a bar chart, we can assess if there is a correlation between the number of bedrooms and the sale price. This analysis provides insights into the housing market's pricing dynamics based on the number of bedrooms in different districts.
Number of sales vs. number of bedrooms: This visualisation presents a comparison of the number of property sales against the number of bedrooms. It showcases the distribution of sales across various bedroom configurations, such as 1-bedroom, 2-bedroom, etc. This analysis helps identify popular bedroom configurations and their corresponding sales volumes, providing insights into buyer preferences and market demand.
Number of sales for each year: This visualisation depicts the number of property sales recorded for each year in Nashville. It was presented as a bar chart, allowing us to observe trends and patterns over time. By analyzing the sales volumes over the years, we can identify growth trends, seasonal variations, or any significant changes in the Nashville housing market.
Number of sales for each month: This visualisation displays the number of property sales recorded for each month of the year. It was represented as a bar chart, enabling us to identify monthly fluctuations and seasonality in the housing market. Analyzing sales patterns by month provides insights into the temporal dynamics of the Nashville housing market, helping identify peak months or periods of high sales activity.

The dashboard can be accessed on my Tableau Public Profile via this link.

4. Conclusion