How would you write a query to find the most recent record for each instrument?
Use the instruments and instrument_records tables. Return every instrument, including instruments without records. When timestamps tie, select the record with the highest record_id.
instrument_idinstrument_id, symbol, latest_record_id, recorded_at, price, and status| Column | Type | Description |
|---|---|---|
| instrument_idPK | INT | Unique instrument identifier |
| symbol | VARCHAR(20) | Instrument symbol |
| venue | VARCHAR(40) | Trading venue |
| Column | Type | Description |
|---|---|---|
| record_idPK | INT | Unique record identifier |
| instrument_id | INT | Referenced instrument |
| recorded_at | TIMESTAMP | Timestamp when the record was captured |
| price | NUMERIC(12,4) | Recorded instrument price |
| status | VARCHAR(20) | Record status |