PostgreSQL 풀이 | Top Three Salaries

문제 1.  Top Three Salaries

As part of an ongoing analysis of salary distribution within the company, your manager has requested a report identifying high earners in each department. A 'high earner' within a department is defined as an employee with a salary ranking among the top three salaries within that department.

You're tasked with identifying these high earners across all departments. Write a query to display the employee's name along with their department name and salary. In case of duplicates, sort the results of department name in ascending order, then by salary in descending order. If multiple employees have the same salary, then order them alphabetically.

Note: Ensure to utilize the appropriate ranking window function to handle duplicate salaries effectively.

https://datalemur.com/questions/sql-top-three-salaries

FAANG SQL Interview Question | DataLemur

---

💡 문제 풀이 

`내 풀이`

소요 시간 08:39

with t1 as(
SELECT department_name, name, salary, DENSE_RANK() OVER(PARTITION BY department_name ORDER BY salary DESC) AS salary_ranking
FROM employee
JOIN department USING(department_id)
ORDER BY department_name, salary DESC, name ASC 
)
SELECT department_name, name, salary
FROM t1
WHERE salary_ranking <4 ;

 

`오답노트`

• ChatGPT 조언 : ORDER BY를 최종 SELECT에서 적용해야 정확한 정렬 수행

`개선 코드`

with t1 as(
SELECT department_name, name, salary, DENSE_RANK() OVER(PARTITION BY department_name ORDER BY salary DESC) AS salary_ranking
FROM employee
JOIN department USING(department_id)
)
SELECT department_name, name, salary
FROM t1
WHERE salary_ranking <=3 -- 더 직관적으로
ORDER BY department_name, salary DESC, name ASC -- 정렬을 맨 마지막에 적용해야 
;

---

💯결과