Promoted stars with a clean record
The people team is building a short list of future leaders. A candidate must (1) have been promoted at least once (a row in promotions), (2) have earned a rating of 4 or 5 in at least one review, and (3) have never been rated below 3 in any review. Set operators (INTERSECT, EXCEPT) fit this perfectly.
Task: return employee (name), department (department name) and grade for every employee who meets all three conditions. Sort by department A to Z, then employee A to Z.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| dept_id INTEGER | name TEXT |
|---|---|
| 1 | Engineering |
| 2 | Sales |
| 3 | Finance |
| 4 | People & Culture |
| 5 | Marketing |
| 6 | Operations |
Showing the first 6 of 7 rows.
| emp_id INTEGER | name TEXT | dept_id INTEGER | job_title TEXT | country TEXT | grade INTEGER | salary_usd INTEGER | hire_date TEXT | termination_date TEXT | manager_id INTEGER |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Harry Clarke | 7 | Chief Executive Officer | United Kingdom | 9 | 375000 | 2016-03-01 | NULL | NULL |
| 2 | Khalid Al Hashimi | 1 | VP of Engineering | United Arab Emirates | 8 | 237000 | 2021-03-01 | NULL | 1 |
| 3 | Zainab Ahmed | 1 | Director of Engineering 1 | Pakistan | 7 | 59000 | 2021-10-12 | NULL | 2 |
| 4 | Hina Khan | 1 | Engineering Manager | Pakistan | 6 | 40000 | 2024-08-10 | NULL | 3 |
| 5 | Ren Tanaka | 1 | QA Engineer | Japan | 4 | 68000 | 2022-12-19 | NULL | 4 |
| 6 | Piotr Zielinski | 1 | Data Engineer | Poland | 3 | 33000 | 2025-10-23 | NULL | 4 |
Showing the first 6 of 58 rows.
| review_id INTEGER | emp_id INTEGER | review_period TEXT | rating INTEGER | reviewed_on TEXT |
|---|---|---|---|---|
| 1 | 2 | 2024-H2 | 2 | 2024-12-20 |
| 2 | 2 | 2025-H1 | 1 | 2025-06-18 |
| 3 | 2 | 2025-H2 | 3 | 2025-12-30 |
| 4 | 2 | 2026-H1 | 2 | 2026-06-25 |
| 5 | 3 | 2024-H2 | 3 | 2024-12-29 |
| 6 | 3 | 2025-H1 | 4 | 2025-06-18 |
Showing the first 6 of 178 rows.
| promo_id INTEGER | emp_id INTEGER | promo_date TEXT | old_grade INTEGER | new_grade INTEGER | old_salary_usd INTEGER | new_salary_usd INTEGER |
|---|---|---|---|---|---|---|
| 1 | 3 | 2025-11-26 | 6 | 7 | 50000 | 59000 |
| 2 | 4 | 2025-12-10 | 5 | 6 | 33000 | 40000 |
| 3 | 6 | 2026-05-08 | 2 | 3 | 28000 | 33000 |
| 4 | 7 | 2025-09-14 | 6 | 7 | 120000 | 143000 |
| 5 | 12 | 2025-01-06 | 2 | 3 | 27000 | 29000 |
| 6 | 29 | 2026-02-23 | 6 | 7 | 80000 | 91000 |
Showing the first 6 of 9 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
Build the list of employee ids with INTERSECT and EXCEPT first, then look up their names.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.