Distinctive Analytics

SQL’s analytic functions allow for complex calculations and in-depth analysis. They operate on rows in a query result set that are related to the current one.

I’ve prepared a thorough examination of common analytic functions for you.

These functions are intended to operate on a set of rows within a query result set that are related to the current row.

Understanding these functions will allow you to gain valuable insights into your data and make sound decisions.

Let’s use the table below as an example for the SQL command demonstration.

1. NTILE(n):

NTILE(n) divides the result set into roughly equal-sized groups or “tiles,” each with its own group number.  This function can be used to calculate quartiles or percentiles.

Examples

SELECT value, NTILE(4) OVER (ORDER BY value) AS quartile FROM dataset;

This query divides the dataset into four quartiles based on the value column

2.PERCENTILE_CONT(value) WITHIN GROUP (ORDER BY column):

• PERCENTILE_CONT calculates the value at a specified percentile within a group of rows. This is particularly helpful for finding the median or other specific percentiles.

Example

SELECT department, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department;

This query finds the median salary for each department.

3. PERCENTILE_DISC(value) WITHIN GROUP (ORDER BY column)

PERCENTILE DISC computes the value at a specified percentile within a group of rows, but instead of interpolated values, it returns an actual data value from the dataset.  It can be used to find discrete percentiles.

SELECT department, PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY salary) AS first_quartile_salary
FROM employees
GROUP BY department;

This query finds the value at the first quartile (25th percentile) of salaries for each department.

4. CUME_DIST() WITHIN GROUP (ORDER BY column):

The cumulative distribution of a value within a group of rows is calculated by CUME DIST, indicating the relative position of a row within the group.

Example:

SELECT department, employee_name, salary, CUME_DIST() WITHIN GROUP (ORDER BY salary DESC) AS cumulative_salary_dist
FROM employees;

This query displays the cumulative distribution of salaries within the employees’ table, ordered by salary in descending order.

5. Lag() and Lead():

The Lag() and Lead() functions allow you to access values from rows preceding or following a result set.  They are frequently used to calculate data shifts or patterns.

SELECT date, revenue, LAG(revenue) OVER (ORDER BY date) AS prev_day_revenue
FROM daily_sales;

This query retrieves the revenue for each day and the revenue for the previous day.

6. First_Value() and Last_Value():

The functions First_Value() and Last_Value() return the first or last value within a group of rows in the specified order.

Example:

SELECT department, employee_name, salary,
       First_Value(employee_name) OVER (PARTITION BY department ORDER BY salary) AS lowest_paid_employee,
       Last_Value(employee_name) OVER (PARTITION BY department ORDER BY salary) AS highest_paid_employee
FROM employees;

This query finds the lowest- and highest-paid employees within each department.

Analytic functions are versatile data analysis and reporting tools that allow you to perform a wide range of calculations within specific groups or ordered sets of data.

These examples illustrate how common analytic functions operate on a dataset and provide valuable insights into data distribution, trends, and percentiles. Analytic functions are powerful tools for data analysis, reporting, and decision-making in SQL.