HR: Employees With Rating >= 4 in Latest Review

SQL coding challenge · Difficulty: hard · +200 XP

Problem

Performance reviews happen quarterly. Find employees whose most recent review has a rating of 4 or higher. Only the latest review per employee counts.

Tables

Table: Employees

| emp_id | name | department | salary | hire_date |
| --- | --- | --- | --- | --- |
| 1 | Ashna | HR | 60000 | 2022-01-15 |
| 2 | Pinki | IT | 70000 | 2021-06-20 |
| 3 | Shruti | Finance | 65000 | 2020-09-10 |
| 4 | Parvati | Marketing | 62000 | 2023-03-05 |

Table: Performance

| perf_id | emp_id | rating | review_date |
| --- | --- | --- | --- |
| 101 | 1 | 4 | 2023-06-30 |
| 102 | 2 | 5 | 2023-06-30 |
| 103 | 3 | 3 | 2023-06-30 |
| 104 | 4 | 4 | 2023-06-30 |
| 105 | 1 | 5 | 2024-06-30 |
| 106 | 2 | 4 | 2024-06-30 |
| 107 | 3 | 4 | 2024-06-30 |
| 108 | 4 | 5 | 2024-06-30 |

Expected Output

| name | rating | review_date |
| --- | --- | --- |
| Ashna | 5 | 2024-06-30 |
| Parvati | 5 | 2024-06-30 |
| Pinki | 4 | 2024-06-30 |
| Shruti | 4 | 2024-06-30 |

• Return: emp_id, name, latest_rating
• Only use the most recent review per employee
• Filter where rating >= 4
• Sort by name ascending