How would you ensure your analysis is reproducible and easy for others to review?
For this SQL exercise, identify the latest completed run for each analysis that has both a query hash and source snapshot recorded. Include review information when available.
analysis_name, run_id, analyst, query_hash, source_snapshot, reviewer_count, latest_review_status, and latest_reviewed_at.analysis_name ascending, then run_id ascending.executed_at; break ties by selecting the highest run_id.review_id in reviewer_count.reviewer_count as 0 and the latest review fields as NULL.latest_review_status by descending reviewed_at, placing NULL values last and breaking ties with the highest review_id.latest_reviewed_at to the maximum non-null reviewed_at.| Column | Type | Description |
|---|---|---|
| run_idPK | INT | Unique analysis execution identifier |
| analysis_name | VARCHAR(100) | Name of the analysis |
| analyst | VARCHAR(100) | Person who executed the analysis |
| query_hash | VARCHAR(64) | Hash identifying the SQL version used |
| source_snapshot | VARCHAR(100) | Identifier for the source-data snapshot |
| executed_at | TIMESTAMP | Timestamp when the analysis ran |
| status | VARCHAR(20) | Execution status |
| Column | Type | Description |
|---|---|---|
| review_idPK | INT | Unique review identifier |
| run_id | INT | Reviewed analysis run |
| reviewer | VARCHAR(100) | Person who reviewed the run |
| review_status | VARCHAR(20) | Review outcome |
| reviewed_at | TIMESTAMP | Timestamp of the review |
| comment | VARCHAR(255) | Optional reviewer comment |