Describe how you would approach a SQL and data preprocessing problem to produce a reliable dataset for analysis at Zillow.
Use the provided Zillow listing and event data to return valid listings as of January 31, 2025. Exclude records with missing or invalid core attributes, select the latest event available by the cutoff, and retain listings whose latest status is ACTIVE or PENDING.
listing_id, city, state, list_price, bedrooms, latest_status, latest_event_at, and days_on_market.city ascending, then listing_id ascending.| Column | Type | Description |
|---|---|---|
| listing_idPK | INT | Unique Zillow listing identifier |
| city | VARCHAR(100) | Listing city, potentially blank or padded |
| state | CHAR(2) | Two-letter state code |
| list_price | DECIMAL(12,2) | Current listing price |
| bedrooms | INT | Number of bedrooms |
| listed_at | DATE | Date the listing entered the market |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique listing event identifier |
| listing_id | INT | Referenced listing identifier |
| event_type | VARCHAR(20) | Listing status event |
| event_at | TIMESTAMP | Time the status event occurred |
| ingested_at | TIMESTAMP | Time the event was loaded into the warehouse |