Finding the Second Highest Salary

This challenge involves identifying the second-highest unique salary from a given dataset of employee salaries. This is a common task in data analysis and reporting, where you might need to find top performers or segment employees based on salary tiers.

Problem Description

You are given a table or a list of employee records, where each record contains an employee's ID and their salary. Your task is to write a function or query that returns the second-highest unique salary.

Requirements:

• The solution should consider only unique salary values. Duplicate salaries should be treated as a single occurrence for ranking purposes.
• If there is no second-highest salary (e.g., only one unique salary exists, or the table is empty), the function should return a specific indicator (e.g., NULL or a designated value).

Expected Behavior:

• Given a list of salaries, identify the highest unique salary and then find the next highest unique salary.
• The order of employees in the input does not matter.

Edge Cases:

• Empty Input: What happens if no employee records are provided?
• Single Unique Salary: What if all employees have the same salary, or only one unique salary value exists?
• All Salaries Distinct: The standard case where all salaries are different.
• Multiple Employees with Highest Salary: Ensure the second-highest is correctly identified even if multiple employees share the top salary.

Examples

Example 1:

Input:
Employees = [
  {"id": 1, "salary": 100},
  {"id": 2, "salary": 200},
  {"id": 3, "salary": 150},
  {"id": 4, "salary": 200}
]

Output: 150

Explanation: The unique salaries are 100, 150, and 200. The highest is 200. The second highest unique salary is 150.

Example 2:

Input:
Employees = [
  {"id": 1, "salary": 100},
  {"id": 2, "salary": 100},
  {"id": 3, "salary": 100}
]

Output: NULL

Explanation: There is only one unique salary (100). Therefore, there is no second-highest unique salary.

Example 3:

Input:
Employees = []

Output: NULL

Explanation: The input list of employees is empty. There are no salaries to process.

Constraints

• The number of employee records can range from 0 to 10,000.
• Salaries will be positive integers, ranging from 1 to 1,000,000.
• Employee IDs will be unique integers.
• Your solution should aim for an efficient time complexity, ideally better than O(n^2) where n is the number of employee records.

Notes

• Consider how you will handle the uniqueness of salaries before determining the ranking.
• Think about different approaches: sorting, using sets, or specialized data structures.
• The specific return value for "no second highest salary" should be clearly defined in your implementation (e.g., returning null, an empty optional, or a specific sentinel value). For this challenge, NULL is the expected indicator.