The second-highest salary in every country
A classic interview question with a real twist: in many countries several colleagues earn the same salary. The pay-equity team wants the people on the second-highest distinct salary level in each country, looking at active employees only (termination_date empty). If two people share the top salary, the second level is the next lower amount, not the second person. Countries with only one distinct salary level have no second level and are skipped.
Task: return country, employee (name), salary_usd and gap_to_top (the country's highest active salary minus this salary). Every person on the second level appears. Sort by country 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?
DENSE_RANK does not skip numbers after a tie, which is exactly what second-highest distinct level needs.
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.