Top 5 Employees Earning Above the Average Salary: LIMIT and RANK Versions

• Average filter – a scalar subquery (SELECT AVG(salary) FROM employees) computes the overall average, and the outer query keeps only rows above it.
• LIMIT version – ORDER BY salary DESC LIMIT 5 (or SELECT TOP 5 on SQL Server) caps the result to exactly 5 rows, breaking ties at the boundary arbitrarily.
• RANK version – RANK() OVER (ORDER BY salary DESC) assigns a competition-style rank to the above-average employees, and WHERE rnk <= 5 keeps all employees in the top 5 ranks, including ties.
• DENSE_RANK alternative – DENSE_RANK() gives the top 5 distinct salary tiers with no gaps, potentially returning more than 5 rows when ties exist.
• NULL handling – AVG() ignores NULLs; if NULL salaries should be treated as zero, use COALESCE(salary, 0).

What is the problem statement?

The goal is to find the top 5 employees whose salary is greater than the overall average salary. There are two approaches: the LIMIT/TOP approach for a simple row cutoff, and the RANK approach for fair tie handling.

How do you compute the average for filtering?

The overall average must be computed as a scalar subquery because you cannot compare a row's salary to an aggregate of the whole table directly in WHERE.

-- Scalar subquery returns one number
SELECT AVG(salary) FROM employees;
-- Result: 185000.00

This subquery runs once and returns a single number. The outer query keeps only rows above it.

How does the LIMIT version work?

-- MySQL / PostgreSQL
SELECT emp_name, branch_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC
LIMIT 5;

-- SQL Server
SELECT TOP 5 emp_name, branch_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

emp_name  | branch_id | salary
----------+-----------+----------
Pankaj    | BR001     | 350000.00
Rajesh    | BR002     | 300000.00
Meena     | BR001     | 280000.00
Anita     | BR003     | 250000.00
Suresh    | BR002     | 250000.00   ← tied with Anita, arbitrarily included
-- Deepak with 250000 is excluded despite same salary as #5

The subtle behavior: if the fifth and sixth employees earn exactly the same salary, LIMIT 5 arbitrarily includes one and excludes the other.

How does the RANK version work?

SELECT emp_name, branch_id, salary, rnk
FROM (
    SELECT emp_name, branch_id, salary,
           RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)
) ranked
WHERE rnk <= 5
ORDER BY rnk;

emp_name  | branch_id | salary     | rnk
----------+-----------+------------+-----
Pankaj    | BR001     | 350000.00  |   1
Rajesh    | BR002     | 300000.00  |   2
Meena     | BR001     | 280000.00  |   3
Anita     | BR003     | 250000.00  |   4
Suresh    | BR002     | 250000.00  |   4   ← same rank, both included
Deepak    | BR001     | 250000.00  |   4   ← same rank, also included

RANK gives tied employees the same rank. Since Anita, Suresh, and Deepak all earn Rs.250,000, they all get rank 4, and the next distinct salary would be rank 7. Filtering rnk <= 5 includes all of them.

What is the difference between RANK and DENSE_RANK here?

With DENSE_RANK, tied employees still share a rank but the next rank does not skip. So three employees tied at rank 4 are followed by rank 5, not rank 7.

| Employees (salary DESC) | RANK | DENSE_RANK |
|-------------------------|------|------------|
| Pankaj (350,000) | 1 | 1 |
| Rajesh (300,000) | 2 | 2 |
| Meena (280,000) | 3 | 3 |
| Anita (250,000) | 4 | 4 |
| Suresh (250,000) | 4 | 4 |
| Deepak (250,000) | 4 | 4 |
| Vikram (220,000) | 7 | 5 |

With DENSE_RANK() <= 5, Vikram at rank 5 is also included. With RANK() <= 5, only ranks 1 through 4 match because the next rank is 7.

How do you handle NULL salaries?

AVG() ignores NULL values by default. If employees with NULL salary should be treated as having zero salary:

-- Without COALESCE: NULLs are ignored in AVG
SELECT AVG(salary) FROM employees;
-- Might return 185000.00 (computed over non-NULL rows only)

-- With COALESCE: NULLs treated as zero, lowering the average
SELECT AVG(COALESCE(salary, 0)) FROM employees;
-- Returns a lower average, so more employees qualify

How do you implement this in Java?

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TopEarnerService {

    private final Connection connection;

    public TopEarnerService(Connection connection) {
        this.connection = connection;
    }

    public List<String> getTopEarnersAboveAverage(int topN) throws SQLException {
        String sql = """
            SELECT emp_name, salary, rnk FROM (
                SELECT emp_name, salary,
                       RANK() OVER (ORDER BY salary DESC) AS rnk
                FROM employees
                WHERE salary > (SELECT AVG(salary) FROM employees)
            ) ranked
            WHERE rnk <= ?
            ORDER BY rnk
            """;
        List<String> results = new ArrayList<>();
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, topN);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String name = rs.getString("emp_name");
                    BigDecimal salary = rs.getBigDecimal("salary");
                    int rank = rs.getInt("rnk");
                    results.add(String.format("Rank %d: %s – Rs.%s", rank, name, salary));
                }
            }
        }
        return results;
    }
}

What about performance?

An index on salary helps both the ordering and the average scan.

CREATE INDEX idx_emp_salary ON employees (salary DESC);

The scalar subquery for AVG runs once and is cached. The outer query then uses the index for both the filter and the sort.

What are common mistakes?

The most common mistake is using the average without a subquery and getting a syntax error.

-- Wrong: cannot use aggregate directly in WHERE
SELECT emp_name, salary FROM employees
WHERE salary > AVG(salary);
-- ERROR: aggregate functions are not allowed in WHERE

-- Correct: use a scalar subquery
SELECT emp_name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Another mistake is confusing HAVING (which filters groups after aggregation) with WHERE (which filters rows before aggregation).

What are common interview follow-up questions?

• "What if the average should be per department?" Use a correlated subquery or a window function: AVG(salary) OVER (PARTITION BY dept_id).
• "How do you get the bottom 5 below the average?" Filter salary < AVG(...) and order ASC.
• "What if you want the top 5 per department above that department's average?" Combine PARTITION BY for both the average and the rank.