Practice/OpenAI/Leetcode 2408. Design SQL

Leetcode 2408. Design SQL

CodingMust

Problem

You are building an HR analytics system that needs to identify the highest-paid employees in each department. Given two tables—one containing employee information and another containing department details—write a solution that returns the top 3 highest-paid employees from each department.

Your solution should rank employees by their salary within their respective departments. If a department has fewer than three employees, include all of them. When employees have identical salaries, they should receive the same rank.

Requirements

1. Join employee data with department information
2. Rank employees by salary within each department (highest salary gets rank 1)
3. Return only employees with rank ≤ 3 in their department
4. Handle tied salaries appropriately (same salary = same rank)
5. Include departments with fewer than 3 employees
6. Sort the final result by department name, then by rank

Constraints

• 0 ≤ number of employees ≤ 10,000
• 1 ≤ number of departments ≤ 100
• Salaries are positive integers
• All employee records have valid department references
• Employee names and department names are non-empty strings

Examples

Example 1:

` Employees: | id | name | salary | department_id | |----|---------|--------|---------------| | 1 | Alice | 85000 | 1 | | 2 | Bob | 90000 | 1 | | 3 | Charlie | 95000 | 1 | | 4 | David | 70000 | 2 | | 5 | Eve | 80000 | 2 |

Departments: | id | department_name | |----|-----------------| | 1 | Engineering | | 2 | Sales |

Output: | department_name | employee_name | salary | rank | |-----------------|---------------|--------|------| | Engineering | Charlie | 95000 | 1 | | Engineering | Bob | 90000 | 2 | | Engineering | Alice | 85000 | 3 | | Sales | Eve | 80000 | 1 | | Sales | David | 70000 | 2 |

Explanation: Charlie has the highest salary in Engineering (rank 1), followed by Bob (rank 2) and Alice (rank 3). In Sales, Eve ranks 1st and David ranks 2nd. Since Sales only has 2 employees, both are included. `

Example 2:

` Employees: | id | name | salary | department_id | |----|---------|--------|---------------| | 1 | Alice | 90000 | 1 | | 2 | Bob | 90000 | 1 | | 3 | Charlie | 85000 | 1 | | 4 | David | 80000 | 1 |

Departments: | id | department_name | |----|-----------------| | 1 | Engineering |

Output: | department_name | employee_name | salary | rank | |-----------------|---------------|--------|------| | Engineering | Alice | 90000 | 1 | | Engineering | Bob | 90000 | 1 | | Engineering | Charlie | 85000 | 2 |

Explanation: Alice and Bob are tied with the highest salary ($90,000), so both receive rank 1. Charlie has the second-highest unique salary and receives rank 2. David is excluded because we only keep the top 3 ranks, and his rank would be 3 or lower depending on tie-handling. `