Window
Functions
crash course
Swipe 👉👉👉
Window functions in SQL are a powerful feature
that allows performing calculations across a set
of table rows related to the current row, but
without grouping the rows into a single output
row, as aggregation functions do. Instead, they
maintain row-wise results.
Key components of Window Functions:
1. OVER(): Specifies the window for the
function. It can include partitioning, ordering,
and frame clauses.
2. PARTITION BY: Divides the result set into
partitions, and the window function is applied
to each partition separately.
3. ORDER BY: Orders the rows within each
partition, defining the sequence for
calculations.
4. Window Frame: Optional specification to limit
the set of rows considered for each row’s
calculation within the window.
Types of Window Functions
Aggregate Window Functions: These are
aggregate functions that work over a window
of rows (e.g., SUM(), AVG(), MAX(), MIN(),
COUNT()).
Ranking Window Functions: Assign a rank or
a number to rows in a partition (e.g.,
ROW_NUMBER(), RANK(), DENSE_RANK(),
NTILE()).
Value Window Functions: Access data from
other rows in the window (e.g., LAG(), LEAD(),
FIRST_VALUE(), LAST_VALUE()).
Cumulative/Running Window Functions:
Perform cumulative calculations (e.g.,
CUME_DIST(), PERCENT_RANK()).
Examples of Different Window Functions
ROW_NUMBER(): Assigns a unique number
to each row starting from 1 for each
partition.
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER(PARTITION BY
department_id ORDER BY salary DESC) AS
row_num
FROM
employees;
Explanation: The ROW_NUMBER() function assigns a
unique rank starting from 1 to each employee within
their department, ordered by salary.
RANK(): Similar to ROW_NUMBER() but
gives the same rank to rows with the same
value.
SELECT
employee_id,
department_id,
salary,
RANK() OVER(PARTITION BY
department_id ORDER BY salary DESC) AS
rank
FROM
employees;
Explanation: The RANK() function assigns the same
rank to employees with the same salary, skipping
ranks where applicable.
DENSE_RANK(): Similar to RANK(), but
does not skip ranks after ties.
SELECT
employee_id,
department_id,
salary,
DENSE_RANK() OVER(PARTITION BY
department_id ORDER BY salary DESC) AS
dense_rank
FROM
employees;
Explanation: The DENSE_RANK() function assigns
ranks to tied rows but does not leave gaps in the
rankings.
PERCENT_RANK(): Computes the relative
rank of a row within a partition.
SELECT
employee_id,
department_id,
salary,
PERCENT_RANK() OVER(PARTITION BY
department_id ORDER BY salary DESC) AS
percent_rank
FROM
employees;
Explanation: The PERCENT_RANK() function assigns
a percentile rank to each employee based on their
salary within the department.
LAG(): Accesses the value of a column from
a previous row.
SELECT
employee_id,
department_id,
salary,
LAG(salary, 1) OVER(PARTITION BY
department_id ORDER BY salary) AS
previous_salary
FROM
employees;
Explanation: The LAG() function retrieves the
salary of the previous employee in the
department based on ascending salary order.
LEAD(): Accesses the value of a column
from a subsequent row.
SELECT
employee_id,
department_id,
salary,
LEAD(salary, 1) OVER(PARTITION BY
department_id ORDER BY salary) AS
next_salary
FROM
employees;
Explanation: The LEAD() function retrieves the
salary of the next employee in the department.
SAVE THIS POST
JOIN US ON TELEGRAM
We Post Free Data Science & Analytics material ,
Courses and Daily Job Notifications
JOIN USING LINK IN BIO
