Understanding and Using Window Functions in SQL

Introduction

Window functions are SQL functions that perform calculations across a set of rows related to the current row (window) or partition without grouping rows into a single output
While aggregate functions like (SUM,AVG) group rows to produce a single reuslt window functions keep all rows in the result set and apply calcualtions over a specific range

Syntax

Here is a syntax of the sql window function

<window_function>() OVER (
    [PARTITION BY <partition_column>]
    [ORDER BY <order_column>]
    [<frame_clause>]
)

The Partition by clause divides the data into partitions or subsets based on one or more columns
The order by orders the rows often based on the time column or the numeric value
The frame clause defines the window frame specifying the exact range of rows to include in each calculation relative to the current row

Types of window functions

There are four main type of window functions

• Aggregate Window Functions: These include functions like SUM(),AVG(),COUNT(),MIN(), and MAX() used with the OVER clause

• Ranking Window Functions: These are functions like ROW_NUMBER(), RANK(),DENSE_RANK() and NTILE() they are used in order ranking

• Value Window Functions: Functions like LAG(),LEAD(),FIRST_VALUE() and LAST_VALUE() which access value from other rows within the window funtions

• Analytic Window Funtions: Funtions like CUME_DIST() and PERCENT_RANK() they are used to provide statistical insights