🧠 SQL Practice: Top Earners by Department

🏆 SQL Practice: High Earners per Department

Let’s write SQL queries to find the highest salary holders in each department. Useful for bonuses, promotions, and recognition reports!

📋 Sample employees Table:

employee_id	name	department_id	salary
1	Arif	101	50000
2	Nasrin	101	55000
3	Rafi	102	60000
4	Ritu	102	62000
5	Faruk	103	58000

1️⃣ Highest Salary Per Department (Using Subquery)

Find employees whose salary is the maximum in their department.

SELECT name, department_id, salary
FROM employees e1
WHERE salary = (
  SELECT MAX(salary)
  FROM employees e2
  WHERE e1.department_id = e2.department_id
);

Output:

name	department_id	salary
Nasrin	101	55000
Ritu	102	62000
Faruk	103	58000

2️⃣ Using RANK() Window Function (if supported)

If your database supports it, you can also use RANK() or DENSE_RANK() to find top earners.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
  FROM employees
) t
WHERE rank = 1;

Same Output:

• Nasrin – Dept 101
• Ritu – Dept 102
• Faruk – Dept 103

💡 Practice Ideas:

• 🧠 Find 2nd highest salary per department
• 💰 List all employees earning above department average
• 📋 Use GROUP BY + HAVING to filter rich departments

📌 Summary:
Use subqueries or window functions to identify top earners department-wise. Useful for awards, promotions, and HR analytics.

SQL top salary per department, SQL high earner query, department-wise salary max, MySQL top salary, employee ranking SQL, Bangladeshi employee salary report, SQL subquery examples