Write a SQL query using CTEs and joins to identify the top-ranked record per group.
Use the provided groups, records, and record_metrics tables. Consider only approved records with at least one metric. Rank by total metric score, then evaluated_at descending, then record_id ascending to resolve ties.
group_id, group_name, record_id, record_name, total_score, evaluated_at, metric_countgroup_id ascending| Column | Type | Description |
|---|---|---|
| group_idPK | INT | Unique group identifier |
| group_name | VARCHAR(100) | Display name of the group |
| Column | Type | Description |
|---|---|---|
| record_idPK | INT | Unique record identifier |
| group_id | INT | Group containing the record |
| record_name | VARCHAR(120) | Record display name |
| status | VARCHAR(20) | Record approval status |
| evaluated_at | DATE | Date the record was evaluated |
| Column | Type | Description |
|---|---|---|
| metric_idPK | INT | Unique metric row identifier |
| record_id | INT | Record associated with the metric |
| metric_name | VARCHAR(80) | Metric label |
| score | INT | Metric score |