Your question is Deduplicate and Keep Latest. Start with the requirements and the one table 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.
Gartner Peer Insights receives review records from periodic ingestion jobs, and the same review can appear multiple times as it is updated. Write a PostgreSQL query that returns one latest valid record for each (source_system, source_review_id) key.
source_review_id or source_updated_at.ROW_NUMBER() to rank records by the most recent source_updated_at within each key.ingestion_id descending as a deterministic tie-breaker when timestamps match.source_system, then source_review_id.| Column | Type | Description |
|---|---|---|
| ingestion_idPK | INT | Unique ingestion record identifier |
| source_system | VARCHAR(40) | Originating Gartner review surface |
| source_review_id | VARCHAR(30) | Review identifier from the source system |
| source_updated_at | TIMESTAMP | Last update time reported by the source |
| status | VARCHAR(20) | Review lifecycle status |
| rating | INT | Review rating from 0 to 5 |
| review_title | VARCHAR(120) | Review title |