AVG()
AVG()
WINDOW FUNCTION
@AMJADALSALEM @FULL PROJECT
INTRODUCTION
A Windows Function in SQL is a powerful tool that allows you
to perform calculations across a set of table rows that are
related to the current row. Unlike aggregate functions, which
return a single value for a group of rows, windows functions
can return a value for each row while still having access to the
full data set. They are typically used to calculate running totals,
rankings, moving averages, or percentages within a specific
partition
COMMON TYPES OF WINDOWS FUNCTIONS
There are several common types of Windows Functions in
SQL, each serving a different purpose. Here are the main
categories:
1. Ranking Functions
These functions assign a rank to each row within a partition of
a result set.
ROW_NUMBER(): Assigns a unique sequential number to
each row.
RANK(): Assigns a rank with gaps in case of ties.
DENSE_RANK(): Similar to RANK(), but without gaps in
ranking.
NTILE(n): Divides the result set into n roughly equal parts
2. Aggregate Functions
These allow you to perform aggregations (like sum or average)
over a set of rows within a window.
SUM(): Calculates the sum of values.
AVG(): Computes the average of values.
COUNT(): Counts the number of rows.
MIN() and MAX(): Return the minimum and maximum
values, respectively.
NOTE: THE WINDOW FUNCTION IS EXECUTED LAST IN THE QUERY
@AMJADALSALEM @FULL PROJECT
LET'S USE THE RANKING FUNCTION
-- 1.0: RETRIEVE A LIST OF EMPLOYEE_ID, FIRST_NAME, HIRE_DATE,
-- AND DEPARTMENT OF ALL EMPLOYEES
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT, HIRE_DATE,
ROW_NUMBER() OVER () AS ROW_N
FROM EMPLOYEES;
--1.1: RETRIEVE A LIST OF EMPLOYEE_ID, FIRST_NAME, HIRE_DATE,
-- AND DEPARTMENT OF ALL EMPLOYEES ORDERED BY THE HIRE DATE
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT, HIRE_DATE,
ROW_NUMBER() OVER (ORDER BY HIRE_DATE) AS ROW_N
FROM EMPLOYEES;
@AMJADALSALEM @FULL PROJECT
-- 1.2: RETRIEVE THE EMPLOYEE_ID, FIRST_NAME,
-- HIRE_DATE OF EMPLOYEES FOR DIFFERENT DEPARTMENTS
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT, HIRE_DATE,
ROW_NUMBER() OVER (PARTITION BY DEPARTMENT ORDER BY HIRE_DATE) AS ROW_N
FROM EMPLOYEES;
-- EXERCISE 1.3
SELECT FIRST_NAME, EMAIL, DEPARTMENT, SALARY, HIRE_DATE,
DENSE_RANK() OVER( ORDER BY HIRE_DATE ) AS RANK_N
FROM EMPLOYEES;
@AMJADALSALEM @FULL PROJECT
-- 1.4: RETRIEVE THE HIRE_DATE. RETURN DETAILS OF
-- EMPLOYEES HIRED ON OR BEFORE 31ST DEC, 2005 AND ARE IN
-- FIRST AID, MOVIES AND COMPUTERS DEPARTMENTS
SELECT FIRST_NAME, EMAIL, DEPARTMENT, SALARY, HIRE_DATE,
RANK() OVER(PARTITION BY DEPARTMENT
ORDER BY SALARY DESC) AS RANK_A
FROM EMPLOYEES
WHERE HIRE_DATE<='2005-12-31' AND DEPARTMENT IN ('FIRST AID', 'MOVIES' ,
'COMPUTERS');
-- 1.5: RETURN THE FIFTH RANKED SALARY FOR EACH DEPARTMENT
SELECT * FROM (SELECT FIRST_NAME ,DEPARTMENT ,SALARY,
RANK() OVER(PARTITION BY DEPARTMENT ORDER BY
SALARY DESC) AS RANK_N
FROM EMPLOYEES) A
WHERE RANK_N =5 ;
@AMJADALSALEM @FULL PROJECT
UNDERSTAND THE DIFFERENCE BETWEEN ROW_NUMBER,
RANK, DENSE_RANK
--1.6 CREATE A COMMON TABLE EXPRESSION TO RETRIEVE THE
CUSTOMER_ID,
-- AND HOW MANY TIMES THE CUSTOMER HAS PURCHASED FROM THE MALL
WITH PURCHASE_COUNT AS (
SELECT CUSTOMER_ID, COUNT(SALES) AS PURCHASE
FROM SALES
GROUP BY CUSTOMER_ID
ORDER BY PURCHASE DESC
)
SELECT CUSTOMER_ID, PURCHASE,
ROW_NUMBER() OVER (ORDER BY PURCHASE DESC) AS ROW_N,
RANK() OVER (ORDER BY PURCHASE DESC) AS RANK_N,
DENSE_RANK() OVER (ORDER BY PURCHASE DESC) AS DENSE_RANK_N
FROM PURCHASE_COUNT
ORDER BY PURCHASE DESC;
-- EXERCISE 1.7
SELECT FIRST_NAME, EMAIL, DEPARTMENT, SALARY,
NTILE(4) OVER(ORDER BY SALARY DESC) GROUP_1,
NTILE(10) OVER(ORDER BY SALARY DESC) GROUP_2,
NTILE(200) OVER(ORDER BY SALARY DESC) GROUP_3
FROM EMPLOYEES;
@AMJADALSALEM @FULL PROJECT
LET'S USE THE AGGREGATE FUNCTION
-- 2.0: TOTAL SALARY FOR EACH DEPARTMENT AND
-- ORDER BY THE HIRE DATE. CALL THE NEW COLUMN RUNNING_TOTAL
SELECT FIRST_NAME, HIRE_DATE, DEPARTMENT, SALARY,
SUM(SALARY) OVER(PARTITION BY DEPARTMENT ORDER BY HIRE_DATE) AS
RUNNING_TOTAL
FROM EMPLOYEES;
-- 2.1: RETRIEVE THE FIRST NAMES, DEPARTMENT AND
-- NUMBER OF EMPLOYEES WORKING IN THAT DEPARTMENT AND REGION
SELECT FIRST_NAME, DEPARTMENT,
COUNT(*) OVER (PARTITION BY DEPARTMENT) DEPT_COUNT,
REGION_ID,
COUNT(*) OVER(PARTITION BY REGION_ID) REGION_COUNT
FROM EMPLOYEES
ORDER BY DEPARTMENT;
@AMJADALSALEM @FULL PROJECT
-- 2.2: FIND THE RUNNING AVERAGE FOR EACH DATE
SELECT FIRST_NAME, HIRE_DATE, SALARY,
ROUND( AVG (SALARY) OVER (PARTITION BY HIRE_DATE ORDER BY HIRE_DATE ),2)AS
RUNNING_AVERAGE
FROM EMPLOYEES;
-- EXERCISE 2.3
WITH PURCHASE_COUNT AS (
SELECT CUSTOMER_ID, COUNT(SALES) AS PURCHASE
FROM SALES
GROUP BY CUSTOMER_ID
ORDER BY PURCHASE DESC
)
SELECT CUSTOMER_ID, PURCHASE,
MAX(PURCHASE) OVER() AS MAX_N,
MAX(PURCHASE) OVER(ORDER BY CUSTOMER_ID ASC) AS MAX_OF_SALES
FROM PURCHASE_COUNT;
@AMJADALSALEM @FULL PROJECT
Thank
! u
y !
o
@AMJADALSALEM @FULL PROJECT