Her departmanda en yüksek maaşı alan çalışan(lar)ı listeleyen bir SQL sorgusu yazın.

Tablolar: employees(id, name, salary, department_id) ve departments(id, name). Eşit maaşta birden fazla kişi olabileceğini hesaba katmak gerekir.

Çözüm 1: Window function (önerilen)

SELECT department, name, salary
FROM (
    SELECT d.name AS department,
           e.name,
           e.salary,
           DENSE_RANK() OVER (PARTITION BY e.department_id
                              ORDER BY e.salary DESC) AS rnk
    FROM employees e
    JOIN departments d ON d.id = e.department_id
) t
WHERE rnk = 1
ORDER BY department;

DENSE_RANK eşitlikte hepsini getirir. Her departmandan tam olarak bir kişi isteniyorsa ROW_NUMBER kullanılır. "İlk 3 maaş" sorusuna da rnk <= 3 ile genişler.

Çözüm 2: GROUP BY + JOIN

SELECT d.name AS department, e.name, e.salary
FROM employees e
JOIN departments d ON d.id = e.department_id
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) m ON m.department_id = e.department_id
   AND m.max_salary   = e.salary;

Çözüm 3: Korele alt sorgu

SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary = (SELECT MAX(salary)
                  FROM employees x
                  WHERE x.department_id = e.department_id);

PostgreSQL'e özel kısa yol

SELECT DISTINCT ON (department_id) department_id, name, salary
FROM employees
ORDER BY department_id, salary DESC;   -- departman başına tek satır

Performans için (department_id, salary DESC) composite index'i her üç yaklaşıma da yardımcı olur.