SQL : Creating user defined Function for solving Nth Highest Salary problem

 SQL Leetcode Question No. 177. Nth Highest Salary

Hello All,

We are back with another SQL problem to gain understanding about SQL. This blog-post focuses on SQL user defined function.

To know more about SQL user defined function, I highly recommend this blog-post.

Learn SQL: User-Defined Functions (sqlshack.com)

Give a try to solve below problem. Solution is provided as usual. Feel free to comment if any queries and also suggest alternate approach to solve this problem. Have a Happy Learning !  

SQL Schema

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.

 

Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null.

The query result format is in the following example.

 

Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

Solution :

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT   
BEGIN 
DECLARE M INT; 
SET M = N - 1;
    RETURN (  
        SELECT DISTINCT Salary FROM Employee             
        ORDER BY Salary DESC 
        LIMIT 1 Offset M
                     );
END

Explanation :

Consider a problem where we want to calculate 3rd highest salary. What logic we would be using ?
 
Simple approach will be arranging salary column from highest to lowest and picking 3rd item from that.
So Syntax will be:

SELECT DISTINCT Salary #selecting salary from salary column
FROM Employee                   #Employee as table name 
ORDER BY Salary DESC    #Arranging in Descending order
LIMIT 1 OFFSET 2              #Limit 1 tells to show only 1 result, and OFFSET tells to OFFSET first 2                                                            results and start from 3  

In our case we want Nth highest salary, so further:

SELECT DISTINCT Salary 
FROM Employee                
ORDER BY Salary DESC    
LIMIT 1 OFFSET M             

where M = N - 1, which is further denoted by 
  
DECLARE M INT; 
SET M = N - 1;            

in our final solution. Further function is build around it.

Happy Learning! 

Comments

Post a Comment

Popular posts from this blog

SQL for beginners : finding out Employees Earning More Than Their Managers

Leetcode question no. 181   181 .  Employees Earning More Than Their Managers SQL Schema Table:  Employee +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | salary | int | | managerId | int | +-------------+---------+ id is the primary key column for this table. Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.   Write an SQL query to find the employees who earn more than their managers. Return the result table in  any order . The query result format is in the following example.   Example 1: Input: Employee table: +----+-------+--------+-----------+ | id | name | salary | managerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | Null | | 4 | Max | 90000 | Null | +----+-------+--------+-----------+ Output: ...
Read more

Answer to SQL Leetcode Question 183. Customers Who Never Order

Answer to SQL Leetcode Question 183 183 .  Customers Who Never Order   SQL Schema Table:  Customers +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id is the primary key column for this table. Each row of this table indicates the ID and name of a customer.   Table:  Orders +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | customerId | int | +-------------+------+ id is the primary key column for this table. customerId is a foreign key of the ID from the Customers table. Each row of this table indicates the ID of an order and the ID of the customer who ordered it.   Write an SQL query to report all customers who never order anything. Return the result table in  any order . The query result format is in the following example.   Example 1: Input: Customers table: +----+-------+ | id | name | +---...
Read more

SQL Medium difficulty practice question : Consecutive Numbers

180 .  Consecutive Numbers   Hello All, I am back with another SQL leetcode solution. This time we are going one step ahead and practice question with medium difficulty. SQL Schema Table:  Logs +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ id is the primary key for this table. id is an autoincrement column.   Write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in  any order . The query result format is in the following example.   Example 1: Input: Logs table: +----+-----+ | id | num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ Output: +-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+ Explanation: 1 is the only number that appears consecutively for at least three ti...
Read more