Your question is SQL Row Before Latest. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Datavant's entity resolution workflows retain multiple observations for each linked entity, including records received from different source systems. Write a PostgreSQL query to return the observation immediately before the latest observation for every entity.
entity_id by observed_at descending. Use observation_id descending as a deterministic tie-breaker.entity_id.| Column | Type | Description |
|---|---|---|
| entity_idPK | INTEGER | Unique Datavant-linked entity identifier |
| entity_name | VARCHAR(100) | Display name for the linked entity |
| entity_type | VARCHAR(30) | Entity classification |
| Column | Type | Description |
|---|---|---|
| observation_idPK | INTEGER | Unique observation identifier |
| entity_id | INTEGER | References linked_entities.entity_id |
| source_system | VARCHAR(50) | System that supplied the observation |
| observed_at | TIMESTAMP | Time the observation was recorded |
| observation_status | VARCHAR(20) | Processing status |
| observed_value | TEXT | Value captured by the observation |