Forum

Forums
SQL
Interview Questions...
"How to Use SQL Win...
 
Share:
Notifications
Clear all

"How to Use SQL Window Functions for Advanced Analytics"

 
    Last Post
RSS

Posts: 97
 SQL Admin
Admin
Topic starter
Jan 13, 2025 12:59 pm
(@sql-admin)
Estimable Member
Joined: 5 years ago

Introduction
Window functions in SQL are powerful tools for performing advanced analytics and calculations across rows that are related to the current row. They allow you to calculate running totals, rankings, moving averages, and more, all while preserving the original rows of your query. In this post, we’ll explore some common uses of SQL window functions with practical examples.

Understanding SQL Window Functions

A window function performs a calculation across a set of rows defined by an OVER clause. The basic syntax is:

sql
 
function_name(expression) OVER (PARTITION BY column_name ORDER BY column_name)
  • function_name: The aggregate or analytical function (e.g., ROW_NUMBER, RANK, SUM, etc.).
  • PARTITION BY: Divides the data into groups (optional).
  • ORDER BY: Specifies the order of rows for calculations (optional).

Example Table: Sales

SaleID SalesPerson Region SaleAmount
1 Alice East 500
2 Bob East 300
3 Charlie West 400
4 Alice East 700
5 Bob East 600
6 Charlie West 800

1. Ranking Sales with ROW_NUMBER

The ROW_NUMBER function assigns a unique number to each row within a partition.

sql
 
SELECT SalesPerson, Region, SaleAmount,
ROW_NUMBER() OVER (PARTITION BY Region ORDER BY SaleAmount DESC) AS Rank
FROM Sales;

Output:

SalesPerson Region SaleAmount Rank
Alice East 700 1
Bob East 600 2
Alice East 500 3
Charlie West 800 1
Charlie West 400 2

2. Calculating Running Totals with SUM

You can calculate a running total of SaleAmount for each salesperson:

sql
 
SELECT SalesPerson, Region, SaleAmount,
SUM(SaleAmount) OVER (PARTITION BY SalesPerson ORDER BY SaleID) AS RunningTotal
FROM Sales;

3. Finding Percent Rank with PERCENT_RANK

The PERCENT_RANK function calculates the relative rank of a row as a percentage.

sql
 
SELECT SalesPerson, Region, SaleAmount,
PERCENT_RANK() OVER (PARTITION BY Region ORDER BY SaleAmount DESC) AS PercentRank
FROM Sales;

4. Moving Averages with AVG

To calculate a 3-row moving average of SaleAmount:

sql
 
SELECT SalesPerson, Region, SaleAmount,
AVG(SaleAmount) OVER (PARTITION BY Region ORDER BY SaleID ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAverage
FROM Sales;

5. Combining Window Functions

You can combine multiple window functions in a single query to gain deeper insights.

sql
 
SELECT SalesPerson, Region, SaleAmount,
RANK() OVER (PARTITION BY Region ORDER BY SaleAmount DESC) AS Rank,
SUM(SaleAmount) OVER (PARTITION BY Region) AS TotalSales,
AVG(SaleAmount) OVER (PARTITION BY Region) AS AvgSales
FROM Sales;

Applications of Window Functions

  1. Performance Analysis: Rank employees based on performance.
  2. Trend Analysis: Calculate moving averages for sales trends.
  3. Customer Segmentation: Group and rank customers by spending.
  4. Financial Reporting: Perform cumulative calculations and comparisons.

Conclusion
SQL window functions unlock a new level of analytics by allowing you to perform calculations across a defined "window" of data. They are essential for advanced data analysis and reporting. If you're looking for more SQL techniques, check out our SQL forum for detailed examples and discussions.

Topic Tags
SQL window functions exam ROW_NUMBER in SQL SQL running total query SQL PERCENT_RANK functio advanced SQL analytics

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Topic Tags:  SQL window functions exam (1) , ROW_NUMBER in SQL (1) , SQL running total query (1) , SQL PERCENT_RANK functio (1) , advanced SQL analytics (1) ,
Share: