Skip to content
ChatRound
  1. ChatRound
  2. Examples
  3. Code

Top 3 salaries per department, ties included: an AI's SQL, checked

Gemini 2.5 Flash-Lite wrote a DENSE_RANK() query. Two reviewers went through it: it is correct, with caveats about NULL salaries and database versions.

ExampleThe answer below was written for this page by Gemini 2.5 Flash-Lite, through Google's API and without web search. It isn't from anyone's conversation. ChatRound checked it on October 27, 2026.The reviewers are the two ChatRound picks by default for an answer from this company.

Gemini 2.5 Flash-Lite's answer Write a SQL query against employees(id, name, department_id, salary) that lists everyone earning one of the top 3 salaries in their department. If several people share a salary, list all of them. 
WITH RankedSalaries AS (
    SELECT
        e.id,
        e.name,
        e.department_id,
        e.salary,
        DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) as salary_rank
    FROM
        employees e
)
SELECT
    id,
    name,
    department_id,
    salary
FROM
    RankedSalaries
WHERE
    salary_rank <= 3
ORDER BY
    department_id, salary DESC;

Explanation:

  1. WITH RankedSalaries AS (...): This defines a Common Table Expression (CTE) named RankedSalaries. CTEs are temporary, named result sets that you can reference within a single SQL statement. This helps to break down the query into more manageable parts.

  2. SELECT e.id, e.name, e.department_id, e.salary, DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) as salary_rank:

    • We select the employee's id, name, department_id, and salary.
    • DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC): This is the core of the solution. It's a window function:
      • DENSE_RANK(): This function assigns a rank to each row within a partition. Importantly, DENSE_RANK() assigns consecutive ranks without gaps. If two employees have the same salary, they will receive the same rank, and the next distinct salary will get the next consecutive rank. This is crucial for handling ties correctly.
      • OVER (...): This clause specifies that DENSE_RANK() is a window function.
      • PARTITION BY e.department_id: This divides the rows into partitions based on the department_id. The ranking will be performed independently within each department.
      • ORDER BY e.salary DESC: Within each department partition, the rows are ordered by salary in descending order (highest salary first).
      • as salary_rank: We alias the result of the DENSE_RANK() function as salary_rank.

  3. SELECT id, name, department_id, salary FROM RankedSalaries WHERE salary_rank <= 3:

    • We then select the desired columns from the RankedSalaries CTE.
    • WHERE salary_rank <= 3: This filters the results to include only those employees whose salary_rank is 1, 2, or 3 within their respective departments. Because DENSE_RANK() handles ties by assigning the same rank, if multiple employees share the 1st, 2nd, or 3rd highest salary, they will all be included.

  4. ORDER BY department_id, salary DESC:

    • Finally, we order the results first by department_id to group employees by their department, and then by salary in descending order within each department, making the output more readable.

How it handles ties:

Let's say in a department:

  • Employee A: Salary 10000
  • Employee B: Salary 10000
  • Employee C: Salary 9000
  • Employee D: Salary 8000

Using DENSE_RANK():

  • Employee A: salary_rank = 1
  • Employee B: salary_rank = 1
  • Employee C: salary_rank = 2
  • Employee D: salary_rank = 3

The WHERE salary_rank <= 3 clause will include Employees A, B, C, and D. This correctly lists all employees earning one of the top 3 salaries, including those who share the same salary.

Trust the answer: the query correctly returns all employees earning one of the top three distinct salaries per department.

  • 0 wrong
  • 0 disputed
  • 5 hold up
  • 2 missing

Checked by DeepSeek V4.1 Flash and Claude Haiku 4.5 · Report written by GPT-5.6 Luna

The reviewers found nothing wrong with this answer.

The rest

What holds up5 points ▾
  • DENSE_RANK() correctly treats equal salaries as ties while ranking distinct salary levels consecutively.
  • PARTITION BY department_id ranks employees independently within each department.
  • Filtering with salary_rank <= 3 correctly selects the top three distinct salary levels.
  • The CTE correctly allows filtering on the window-function result.
  • The tie example and final ordering are correct for the stated task.
What it missed2 points ▾
  • The explanation does not note that departments with fewer than three distinct salaries simply return all available salary levels; no padding occurs.
  • It does not discuss engine/version requirements or NULL salary behavior, which can vary by database system.
Only one reviewer said2 points ▾
  • DeepSeek V4.1 Flash: Equal-salary rows may appear in an unspecified order unless a stable tie-breaker such as id is added to the final ordering. worth checking
  • DeepSeek V4.1 Flash: The query requires window-function support, and the placement of NULL salaries in descending order depends on the database engine. worth checking

Corrected answer

WITH RankedSalaries AS (
    SELECT
        e.id,
        e.name,
        e.department_id,
        e.salary,
        DENSE_RANK() OVER (
            PARTITION BY e.department_id
            ORDER BY e.salary DESC
        ) AS salary_rank
    FROM employees AS e
)
SELECT
    id,
    name,
    department_id,
    salary
FROM RankedSalaries
WHERE salary_rank <= 3
ORDER BY department_id, salary DESC, id;

DENSE_RANK() ranks distinct salaries within each department. Employees with the same salary receive the same rank, so every employee earning one of the top three distinct salaries is returned. If a department has fewer than three distinct salaries, all of its available salary levels are returned. The final id ordering is an optional tie-breaker for deterministic output among employees with equal salaries. The query requires database support for window functions.

OUR CHECK

We read this report before publishing it

We ran the query in SQLite with tied salaries (100, 100, 90, 80): it returns all four people, as the answer says. RANK() would have dropped the 80. Window functions need MySQL 8.0+, PostgreSQL, SQL Server, Oracle or SQLite 3.25+.

Checked against the sources below on October 28, 2026.

Sources:

Each reviewer's full review

DeepSeek V4.1 Flash▾

My own answer

The query is correct. DENSE_RANK() ... PARTITION BY department_id ORDER BY salary DESC is exactly the right tool: <= 3 selects everyone whose salary is the 1st, 2nd, or 3rd distinct salary level in their department, and ties share a rank so co-earners are all returned. RANK() would leave gaps (1, 1, 3) and ROW_NUMBER() would silently drop tied employees, so the choice matters and the answer makes it correctly.

Claim by claim

  • DENSE_RANK() is the right function here — holds. It gives 1,1,2,3 for salaries 10000, 10000, 9000, 8000, so "top 3 salaries" including ties is exactly what salary_rank <= 3 returns.
  • "DENSE_RANK() assigns consecutive ranks without gaps. If two employees have the same salary, they will receive the same rank, and the next distinct salary will get the next consecutive rank." — holds. That is the defining behavior versus RANK().
  • "This filters the results to include only those employees whose salary_rank is 1, 2, or 3 within their respective departments." — holds. The partition makes the rank local to each department, so a low earner in one department can still appear while a high earner in another is excluded.
  • Tie example: A=10000, B=10000, C=9000, D=8000 → ranks 1,1,2,3, all four included — holds. The ranks and the conclusion are both correct.
  • CTE syntax, aliasing, OVER clause description, ORDER BY department_id, salary DESC — holds. Standard SQL:2003 window-function usage; runs as written on PostgreSQL, SQL Server, Oracle, SQLite, and MySQL 8+.
  • "making the output more readable" (about the final ORDER BY) — holds, though without a tiebreaker the order within equal salaries is not deterministic. Presentation only, not a correctness issue.

No incorrect claim found.

What it missed

Nothing that changes the result. Two small additions would make it more complete, both optional:

  • Deterministic ordering: add , id to the final ORDER BY if you want stable row order among employees with identical salaries.
  • NULL salaries / portability: behavior for salary IS NULL depends on the engine's default NULLS FIRST/LAST for DESC; and the query needs SQL:2003 window-function support (MySQL < 8, older SQLite won't run it).

Corrected answer

The answer holds as written. Nothing needs rewriting; at most add the optional tiebreaker ORDER BY department_id, salary DESC, id for stable output.

Claude Haiku 4.5▾

My own answer

The query is correct. It uses DENSE_RANK() in a CTE, filters outside the CTE with WHERE salary_rank <= 3, partitions by department, and orders by salary descending. This properly returns all employees earning one of the top 3 distinct salaries in their department, including ties.

Claim by claim

All claims hold. The explanation is accurate:

  • DENSE_RANK() correctly assigns consecutive ranks without gaps, so ties share the same rank and the next distinct salary gets the next consecutive number (not skipped). ✓
  • The PARTITION BY e.department_id correctly isolates ranking within each department. ✓
  • ORDER BY e.salary DESC correctly ranks from highest to lowest. ✓
  • Window functions cannot be filtered in WHERE, so using a CTE is the correct approach. ✓
  • The WHERE salary_rank <= 3 filter correctly includes all employees at ranks 1, 2, and 3. ✓
  • The tie-handling example is correct: employees A and B both at rank 1 (same salary), C at rank 2 (distinct lower salary), D at rank 3 (distinct lower salary), all four included. ✓

What it missed

The answer does not discuss an important edge case: what happens when fewer than 3 distinct salaries exist in a department. In that case, salary_rank <= 3 will return fewer employees than expected, which is correct behavior but worth noting. For example, if a department has only 2 employees with distinct salaries, the query will return both (ranks 1 and 2), not pad to 3 rows.

The answer also does not mention that the ORDER BY clause in the final query is optional for correctness but good practice for readability—it's not part of the logic that filters top 3 salaries.

Corrected answer

The answer holds. No corrections needed to the SQL or the explanations.

Pages it consulted: Top N per Group in SQL: Top 3 Salaries per Dept | DataDank · LeetCode SQL Problem & Solution: Department Top Three Salaries | LearnSQL.com · 00185-department-top-three-salaries.sql · FAANG SQL Interview Question | DataLemur · SQL Detective Mode: Using DENSE_RANK to Identify Top Earners by Department

YOUR TURN

Check the answer you're about to rely on.

Paste an answer from ChatGPT, Claude, Gemini or any AI. Two AIs from other companies go through it and show you what's wrong.

Check an answer Add the Chrome extension

It puts a Check button under every ChatGPT, Claude and Gemini answer.

More example checks