Introduction to Window function | Snowflake

Window Function Syntax:
---------------------------------------------
Window functions applies aggregate and ranking functions over a particular window (set of rows).
OVER clause is used with window functions to define that window.

OVER clause does three things :
--------------------------------------------------------
Partitions rows into form set of rows. (PARTITION BY clause is used)

Orders rows within those partitions into a particular order. (ORDER BY clause is used)

cumulativeFrame/slidingFrame



You specify how to partition and order the input rows as well as the type of window
frame to apply.

select function({arguments})
over ([PARTITION BY {expression}]
[ORDER BY {expression}]
[cumulativeFrame | slidingFrame])


Global Rank:
--------------------
DENSE_RANK:Returns the rank of a value within a group of values, without gaps in the ranks.

The rank value starts at 1 and continues up sequentially.

If two values are the same, they have the same rank.

Code:
--------
DROP DATABASE IF EXISTS RAMU;

CREATE DATABASE IF NOT EXISTS RAMU;

USE RAMU;

create table testemployee
( emp_id varchar(20),
dept_id varchar(20),
emp_salary bigint );


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);

select * from testemployee;

--Dense Rank Code
select emp_id, emp_salary, DENSE_Rank()
over (order by emp_salary desc) global_order
from testemployee;

--3rd highest Salary
with rank_table as (select emp_id, emp_salary, DENSE_Rank()
over (order by emp_salary desc) global_order
from testemployee)
select * from rank_table where global_order=3;


--Rank & Dense Rak difference
create or replace table corn_production (farmer_id integer, state varchar, bushels float);


insert into corn_production (farmer_id, state, bushels) values
   (1, 'Iowa', 100),
   (2, 'Iowa', 110),
   (3, 'Iowa', 110),
   (4, 'Iowa', 110),
   (5, 'Kansas', 120),
   (6, 'Kansas', 130);
   
select farmer_id, state,bushels, DENSE_Rank()
over (order by bushels desc) global_order_dr,
Rank() over (order by bushels desc) global_order_r
from corn_production;

Check this playlist for more AWS Projects in Big Data domain:
Demystifying Data Engineering with Cl...