Aggregate and Window functions in SQL -02
2024
Let’s incorporate both tables (sales and employee) into the examples for window functions. We’ll use sales to show the data and apply window functions, and employee to provide additional context like employee names.
Sample Data
Table: sales
Window Functions with Two Tables
1. Aggregate Functions
a. AVG()
SELECT
s.employee_id,
e.employee_name,
s.amount,
AVG(s.amount) OVER (PARTITION BY s.employee_id) AS avg_sale_amount
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
b. MAX()
SELECT
s.employee_id,
e.employee_name,
s.amount,
MAX(s.amount) OVER (PARTITION BY s.employee_id) AS max_sale_amount
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
c. MIN()
SELECT
s.employee_id,
e.employee_name,
s.amount,
MIN(s.amount) OVER (PARTITION BY s.employee_id) AS min_sale_amount
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
d. SUM()
SELECT
s.employee_id,
e.employee_name,
s.amount,
SUM(s.amount) OVER (PARTITION BY s.employee_id) AS total_sales
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
e. COUNT()
SELECT
s.employee_id,
e.employee_name,
s.amount,
COUNT(s.amount) OVER (PARTITION BY s.employee_id) AS sale_count
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
2.Ranking Functions
a. ROW_NUMBER()
SELECT
s.sale_id,
s.employee_id,
e.employee_name,
s.amount,
ROW_NUMBER() OVER (PARTITION BY s.employee_id ORDER BY s.sale_date) AS row_num
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
b. RANK()
SELECT
s.sale_id,
s.employee_id,
e.employee_name,
s.amount,
RANK() OVER (PARTITION BY s.employee_id ORDER BY s.amount DESC) AS rank
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
c. DENSE_RANK()
SELECT
s.sale_id,
s.employee_id,
e.employee_name,
s.amount,
DENSE_RANK() OVER (PARTITION BY s.employee_id ORDER BY s.amount DESC) AS dense_rank
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
d. PERCENT_RANK()
SELECT
s.sale_id,
s.employee_id,
e.employee_name,
s.amount,
PERCENT_RANK() OVER (PARTITION BY s.employee_id ORDER BY s.amount) AS percent_rank
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
Note:
if it is not partitioned then number would increase gradually
If the ROW_NUMBER() function is not partitioned by employee_id, then the numbering would increase continuously across the entire result set, without resetting for each employee. The numbering would just follow the global order defined by the ORDER BY clause.
Example without PARTITION BY:
SELECT
s.sale_id,
s.employee_id,
e.employee_name,
s.amount,
ROW_NUMBER() OVER (ORDER BY s.sale_date) AS row_num
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
In this case, the row number (row_num) would increase gradually across all rows, without resetting for each employee. The numbering would depend only on the order provided by the ORDER BY clause.
ROW_NUMBER(): Sequential numbering without considering ties.
RANK(): Same rank for ties, but skips subsequent ranks.
DENSE_RANK(): Same rank for ties, but does not skip subsequent ranks.
