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:
https://youtube.com/playlist?list=PLjfRmoYoxpNopPjdACgS5XTfdjyBcuGku