Create a Function for a Requested Distinct Salary Rank
Company: Amazon
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Other
### Problem
Implement getNthHighestSalary(N INT), returning an integer salary from Employee. Given a positive integer N, return the Nth-largest distinct known salary, or NULL when fewer than N known salary values exist. Preserve the function-creation contract and integer parameter; a fixed standalone SELECT with a substituted constant does not implement this interface.
### Tables
Table: `Employee`
```text
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.
```
### Example
```text
Employee (id,salary): (1,45), (2,90), (3,90), (4,70)
getNthHighestSalary(2) -> 70
getNthHighestSalary(4) -> NULL
```
### What a Strong Answer Covers
- Keep the function callable with an integer rank.
- Count each known salary value once and return NULL when the rank is missing.
### Follow-up Questions
- How would you return the requested salary for each department?
- How would repeated calls change your choice of indexes or precomputation?
Overview: Write a PostgreSQL function that returns a requested distinct salary rank, or NULL when the rank does not exist.
