Snowflake SQL: Understanding PERCENT_RANGE Function
The PERCENT_RANGE function in Snowflake computes the relative rank of each row within a specified window partition. It returns values ranging from 0 to 1, indicating the position of each row as a percentage of the total number of rows in the partition, excluding the first and last rows which are 0 and 1, respectively. This function is useful for data analysis tasks where understanding the distribution or rank of values within a dataset is essential.
---------------------------------------------------------------------------------------------------------------------
SQL QUERY-
/*
-- PERCENT_RANK
PERCENT_RANK () OVER ([query_partition_clause] order_by_clause)
The PERCENTAGE_RANK function returns the relative standing of a value within a set of values.
*/
select EMP_ID, EMP_NAME, SALARY, rank () over (order by salary desc )
from employee;
---PPERCENT_RANK()
select EMP_ID, EMP_NAME, SALARY,
rank () over (order by salary ) sal_rank_in_Asc ,
PERCENT_RANK () over (order by salary ) sal_PERCENT_RANK
from employee;
-- The following query retrieves the employees who are in the highest 30% of salaries in the company.
select * from
(
select EMP_ID, EMP_NAME, SALARY,
rank () over (order by salary ) sal_rank_in_Asc ,
PERCENT_RANK () over (order by salary ) sal_PERCENT_RANK
from employee
)
where sal_PERCENT_RANK GT=0.7;
-----------------------------------------------------------------------------------------------
select EMP_ID, EMP_NAME, SALARY,DEPT_ID,
rank () over ( PARTITION BY DEPT_ID order by salary ) sal_rank_in_Asc ,
PERCENT_RANK () over (PARTITION BY DEPT_ID order by salary ) sal_PERCENT_RANK
from employee;
select EMP_ID, EMP_NAME, SALARY,
rank () over ( PARTITION BY DEPT_ID order by salary ) sal_rank_in_Asc ,
PERCENT_RANK () over (PARTITION BY DEPT_ID order by salary ) sal_PERCENT_RANK
from employee
where DEPT_ID=3;
select EMP_ID, EMP_NAME, SALARY,
rank () over ( order by salary ) sal_rank_in_Asc ,
PERCENT_RANK () over ( order by salary ) sal_PERCENT_RANK
from employee
where DEPT_ID=3;
---------------------------------------------------------------------------------------------------------------------
#SnowflakeDB #SQLTutorial #DatabaseTutorial #PERCENT_RANGE #DataAnalysis #SQLFunctions #SnowflakeSQL #LearnSQL #DataScience #TechTutorial