How I use SQL windows function as a Data Analyst

In SQL, the term “window function” refers to a category of functions that perform calculations across a specified range of rows related to the current row within the result set. These functions are also known as windowed or analytic functions. Window functions operate on a set of rows defined by an OVER() clause,this clause provides flexibility in defining the scope of the window function's calculation, making it a powerful tool for analytical queries.

SUM(): when combined with the over() or partition by clause it can act as a windows function.

Example: I have a sales data and i want to get month on month sales, the sum function can be used with the over() clause.

what it does is to add up monthly sales respectively starting from the first month to the last month.

other aggregate functions like avg(), min(), max(), count() can also be used with the over() clause as a window function.

LAG():SQL windows function used for data manipulation and analysis allows you to access the value of a specified row in a result set before the current row. It is commonly used for tasks such as calculating the difference between consecutive rows or identifying patterns in time-ordered data.

For example I have a data containing quantity sold and I want to calculate difference in monthly orders, the lag() function can be used to pull the previous record and compare against current record. I can also use it to see monthly percentage increase or decrease in orders.

LEAD(): lead() does the opposite of lag(), Retrieve the value of a column from the next row within the partition.

Example: I have a sales data and I am to find monthly sales and compare with previous month beginning from last month of the year December to January. the lead() function will be used to pull next record to compare against the current record.

ROW_NUMBER():Assigns a unique number to each row within a partition, typically based on a specified order.

Example: I’m to find customers that made 5 or more purchases, I used the row_number() function over partition by customer name which will assign number to each row where that customer name appears.

customers that made 5 or more purchases will have 5 and above as their rank.