SQL LEARNING SERIES PART-16

Complete SQL Topics for Data Analysis
-> https://t.me/sqlspecialist/523

Today, we will learn about Analytical Functions:

Analytical functions operate on a set of rows related to the current row and are often used for advanced analytics and reporting.

#### LAG() and LEAD():
Retrieve data from rows before or after the current row within a partition.

SELECT product_name, price, LAG(price) OVER (ORDER BY price) AS prev_price
FROM products;

#### FIRST_VALUE() and LAST_VALUE():
Get the first or last value within a partition.

SELECT department, employee_name, FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date) AS first_salary
FROM employees;

#### PERCENTILE_CONT():
Calculates a specified percentile within a group.

SELECT product_category, product_price, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY product_price) OVER (PARTITION BY product_category) AS median_price
FROM products;

Analytical functions enable advanced statistical analysis and reporting.

Share with credits: https://t.me/sqlspecialist

Hope it helps :)