How would you approach cleaning a dataset with inconsistent date formats and missing values?
Write a PostgreSQL query that standardizes recognized date formats, identifies missing and invalid dates, replaces missing numeric values with zero, and preserves records without a matching reference category.
record_id, source_system, cleaned_date, date_status, cleaned_value, and category_name.cleaned_date; missing numeric values must produce zero; unmatched categories must be labeled Unclassified.record_id ascending.| Column | Type | Description |
|---|---|---|
| record_idPK | INT | Unique intake record identifier |
| source_system | VARCHAR(50) | System that supplied the record |
| raw_date | VARCHAR(30) | Date value in an unstandardized text format |
| raw_value | DECIMAL(10,2) | Numeric value that may be missing |
| category_code | VARCHAR(10) | Optional reference category code |
| Column | Type | Description |
|---|---|---|
| category_codePK | VARCHAR(10) | Reference category code |
| category_name | VARCHAR(100) | Readable category label |