Tell me about how you would clean and transform a set of unstructured datasets using SQL or Pandas within a strict time limit.
Use the provided raw trade reports and instrument reference data. Return only valid, completed trades after normalizing dates, symbols, sides, quantities, and prices. Rows with an invalid or NULL date, blank or unknown symbol, non-positive quantity, negative price, or unsupported status must be ignored.
report_id, trade_date, symbol, instrument_name, side, quantity, and pricetrade_date ascending, then report_id ascending| Column | Type | Description |
|---|---|---|
| report_idPK | INTEGER | Unique raw trade report identifier |
| reported_at_text | VARCHAR(40) | Trade date in an inconsistent text format |
| symbol_text | VARCHAR(40) | Raw instrument symbol |
| quantity_text | VARCHAR(40) | Raw quantity with possible punctuation or units |
| price_text | VARCHAR(40) | Raw price with possible currency text |
| side_text | VARCHAR(20) | Raw buy or sell designation |
| status_text | VARCHAR(20) | Raw execution status |
| Column | Type | Description |
|---|---|---|
| symbolPK | VARCHAR(20) | Canonical instrument symbol |
| instrument_name | VARCHAR(80) | Canonical instrument name |