Aggregate and Window functions in SQL -03
2024
Analytic Functions:
a. LAG()
SELECT
s.sale_id,
s.employee_id,
e.employee_name,
s.amount,
LAG(s.amount, 1) OVER (PARTITION BY s.employee_id ORDER BY s.sale_date) AS prev_amount
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
b. LEAD()
SELECT
s.sale_id,
s.employee_id,
e.employee_name,
s.amount,
LEAD(s.amount, 1) OVER (PARTITION BY s.employee_id ORDER BY s.sale_date) AS next_amount
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
c. FIRST_VALUE()
SELECT
s.sale_id,
s.employee_id,
e.employee_name,
s.amount,
FIRST_VALUE(s.amount) OVER (PARTITION BY s.employee_id ORDER BY s.sale_date) AS first_sale
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
d. LAST_VALUE()
SELECT
s.sale_id,
s.employee_id,
e.employee_name,
s.amount,
LAST_VALUE(s.amount) OVER (PARTITION BY s.employee_id ORDER BY s.sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
E.NTH_VALUE()
SELECT
s.sale_id,
s.employee_id,
e.employee_name,
s.amount,
NTH_VALUE(s.amount, 2) OVER (PARTITION BY s.employee_id ORDER BY s.sale_date) AS second_sale
FROM sales s
JOIN employee e ON s.employee_id = e.employee_id;
These examples use the sales and employee tables to show how window functions can be applied with multiple tables to provide context and derive meaningful insights
