Snowflake1 : Finding Nth highest Salary of an employee

The Snowflake is becoming one of the most consumed Cloud Data Warehouse in recent time.

The beauty of Snowflake lies in many areas like auto scaling of Data query processing power by the use of Virtual Warehouse or use of amazon web services s3 bucket or azure blob storage for storing the data behind it by using micro partion methodology.

But todays topic is not about the architecture of Snowflake, but we will try to do below activities in snowflake -

1. Create an Employee table with Salary for each employee
2. Load the data for the table using simple insert statement.
3. Write the Query for finding the Nth highest Salary of an Employee using snowflake Rank() function — For example we will try to find 3rd highest Salary from the list of employees.

Step 1 : As first step please use below script to create the Employee table in Snowflake -

— ddl for employee table

create table testemployee

( emp_id varchar(20),

dept_id varchar(20),

emp_salary bigint )

Now the table is created as below -

Create Table Script for Snowflake

Step 2: Load the data for the table using simple insert statement -

insert into testemployee
values
(‘A’,’IT’,12000),
(‘B’,’IT’,13000),
(‘C’,’MARKETING’,13500),
(‘D’,’MARKETING’,14000),
(‘E’,’HR’,15000),
(‘F’,’HR’,16000),
(‘H’,’SALES’,17000);

Step 3: Write the Query for finding the Nth highest Salary of an Employee using snowflake Rank() function — For example we will try to find 3rd highest Salary from the list of employees -

At first we will write the query to rank employees on basis of their received Salary. So the employee receiving the highest salary will be rank 1 whereas the employee receiving lowest salary will be the last rank.

The query will be as below -

— finding the rank for employees on basis of Salary values

select emp_id, emp_salary, Rank()
over (order by emp_salary desc) Rank
from testemployee

Now we will slightly modify the above query to find the 3rd highest Salaried employee from the list

So as per the above data we can predict the 3rd Highest Salaried employee is E and his received salary is 15000.

— finding the 3rd highest Salary from the list of employees.

select emp_id,emp_salary from
(
select emp_id,emp_salary, Rank()
over (order by emp_salary desc) Rank
from testemployee
)
where Rank=3

So can you try to find the 3rd lowest employee Salary in the list ?

Happy Coding!! Happy Reading!!