Скачать или смотреть Exploring RANK and DENSE_RANK Functions in Snowflake SQL

Unlock the power of Snowflake's RANK and DENSE_RANK functions in your data analysis! In this video, we'll explore how to use these powerful SQL functions to rank rows within your datasets, understand the key differences between them, and learn practical applications for sorting and analyzing data effectively. Whether you're a beginner or looking to refine your Snowflake skills, this tutorial has got you covered. Don't forget to like, subscribe, and hit the bell icon for more Snowflake tutorials!

#Snowflake #SQL #DataAnalysis #RANKFunction #DENSE_RANK #DatabaseTutorial #SQLTutorial #DataScience #DataRanking #TechTutorial #LearnSQL #SnowflakeDatabase #DataQuery #SQLFunctions #dataengineering

----SQL-----------


-- Analytical Function Rank() , Dense_Rank()

--Analytic Syntax for rank
--RANK ( ) OVER ( [query_partition_clause] order_by_clause )

-- IMPORTANT NOTE
-- FIRST PROCESS
-- all joins, WHERE, GROUP BY, and HAVING clauses are performed
-- AFTER THESE the result set is made available to the analytic functions and all their calculations take place
-- Third, if the query have an ORDER BY clause at the end.


-- Create the Table
CREATE OR REPLACE TABLE customers (
CUST_CODE STRING,
CUST_FIRST_NAME STRING,
CUST_LAST_NAME STRING,
PURCHASE_AMT INTEGER
);

-- Step 2: Insert the Data
INSERT INTO customers (CUST_CODE, CUST_FIRST_NAME, CUST_LAST_NAME, PURCHASE_AMT) VALUES
('CUST-10002', 'Herman', 'Kidwell', 2545),
('CUST-1089', 'Gwen', 'Rutherford', 213),
('CUST-1104', 'Glenn', 'Hardesty', 1432),
('CUST-11131', 'Gina', 'Ballard', 124324),
('CUST-11407', 'Haland', 'Vue', 546),
('CUST-11408', 'Guy', 'Weaver', 3243),
('CUST-11443', 'Granville', 'Welk', 54315),
('CUST-1155', 'Ginna', 'Lyon', 6547);



select CUST_CODE, CUST_FIRST_NAME, CUST_LAST_NAME, PURCHASE_AMT from DEMO_DB.PUBLIC.CUSTOMERS;


select CUST_CODE, CUST_FIRST_NAME, CUST_LAST_NAME, PURCHASE_AMT,
RANK ( ) OVER ( order by PURCHASE_AMT desc) PURCHASE_AMT_rank
from DEMO_DB.PUBLIC.CUSTOMERS;


--now let us add another record with PURCHASE_AMT 54315
insert into customers values ('CUST-1156', 'Gerhard', 'Wiser', 54315);

--using rank could lead to gaps
select CUST_CODE, CUST_FIRST_NAME, CUST_LAST_NAME, PURCHASE_AMT,RANK ( ) OVER ( order by PURCHASE_AMT desc) PURCHASE_AMT_rank
from DEMO_DB.PUBLIC.CUSTOMERS;

--we can solve this by using DENSE_RANK

select CUST_CODE, CUST_FIRST_NAME, CUST_LAST_NAME, PURCHASE_AMT,DENSE_RANK ( ) OVER ( order by PURCHASE_AMT desc) PURCHASE_AMT_rank
from DEMO_DB.PUBLIC.CUSTOMERS;

--now let us add one record to the CUSTOMERS with NULL PURCHASE_AMT
insert into customers values ('CUST-11702', 'Haland', 'Welch', NULL);

--when using order by desc , the null pulling first
select CUST_CODE, CUST_FIRST_NAME, CUST_LAST_NAME, PURCHASE_AMT,DENSE_RANK ( ) OVER ( order by PURCHASE_AMT desc) PURCHASE_AMT_rank
from DEMO_DB.PUBLIC.CUSTOMERS;

-- 2 solutions

select CUST_CODE, CUST_FIRST_NAME, CUST_LAST_NAME, PURCHASE_AMT,DENSE_RANK ( ) OVER ( order by NVL(PURCHASE_AMT,0) desc) PURCHASE_AMT_rank
from DEMO_DB.PUBLIC.CUSTOMERS;


select CUST_CODE, CUST_FIRST_NAME, CUST_LAST_NAME, PURCHASE_AMT,DENSE_RANK ( ) OVER ( order by PURCHASE_AMT desc nulls last ) PURCHASE_AMT_rank
from DEMO_DB.PUBLIC.CUSTOMERS;