Nashville Housing Data —
SQL Data Cleaning & Analysis

The raw CSV contained dates in natural language format. An explicit re-cast ensures no edge cases in date arithmetic, filtering, or time-series aggregations.

-- Convert 'April 9, 2013' → ISO 8601 DATE
UPDATE housing_data
SET SaleDate = STR_TO_DATE(SaleDate, '%M %d, %Y');

-- Change column type to DATE
ALTER TABLE housing_data
MODIFY COLUMN SaleDate DATE;

The key insight: properties on the same land parcel (same ParcelID) share a physical address. By self-joining the table on ParcelID (excluding same-row matches), we can find a "donor" row with a known address to fill in the blank.

-- Preview: find donor addresses for blank rows
SELECT a.UniqueID, a.ParcelID,
       a.PropertyAddress AS current_address,
       b.PropertyAddress AS donor_address
FROM housing_data a
JOIN housing_data b
    ON  a.ParcelID  = b.ParcelID
    AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress = '';

-- Materialise as a view for reusability
CREATE VIEW updated_address AS
SELECT a.UniqueID, a.ParcelID,
    IF(a.PropertyAddress = '',
       b.PropertyAddress,
       a.PropertyAddress) AS resolved_address
FROM housing_data a
JOIN housing_data b
    ON a.ParcelID = b.ParcelID
    AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress = '';

Both address columns stored multiple values in one field. Decomposing them into atomic columns enables city-level aggregations, state-level joins, and geographic analysis, none of which were possible on the raw compound fields.

-- Add atomic columns
ALTER TABLE housing_data
  ADD PropertyStreet VARCHAR(255),
  ADD PropertyCity   VARCHAR(100);

-- Parse using SUBSTRING_INDEX on comma delimiter
UPDATE housing_data SET
  PropertyStreet = TRIM(SUBSTRING_INDEX(PropertyAddress, ',', 1)),
  PropertyCity   = TRIM(SUBSTRING_INDEX(PropertyAddress, ',', -1));

-- Drop original compound column
ALTER TABLE housing_data DROP COLUMN PropertyAddress;

-- Same for OwnerAddress → Street + City + State
UPDATE housing_data SET
  OwnerStreet = TRIM(SUBSTRING_INDEX(OwnerAddress, ',', 1)),
  OwnerCity   = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(OwnerAddress, ',', -2), ',', 1)),
  OwnerState  = TRIM(SUBSTRING_INDEX(OwnerAddress, ',', -1));

The SoldAsVacant column had 4 distinct values from multiple data entry operators using different conventions. GROUP BY on this column was producing 4 groups instead of 2, breaking boolean logic in every downstream filter.

-- Audit before transformation
SELECT SoldAsVacant, COUNT(*) AS count
FROM housing_data
GROUP BY SoldAsVacant;
-- Returns: 'No' (51,403) | 'N' (399) | 'Y' (52) | 'Yes' (4,623)

-- Normalise all variants to Y / N
UPDATE housing_data
SET SoldAsVacant = CASE
    WHEN SoldAsVacant IN ('Yes', 'Y') THEN 'Y'
    ELSE 'N'
END;

Owner names are personal identifiers with no analytical value for market-level housing analysis. Retaining them unnecessarily increases privacy risk. Simultaneously, a pre-computed SaleMonth column eliminates repeated EXTRACT() calls in downstream queries.

-- Remove PII: owner name has no analytical value
ALTER TABLE housing_data DROP COLUMN OwnerName;

-- Engineer SaleMonth as a stored TINYINT column
ALTER TABLE housing_data ADD SaleMonth TINYINT;

UPDATE housing_data
SET SaleMonth = EXTRACT(MONTH FROM SaleDate);