Calculating Percentiles

You can use the PERCENTILE_CONT or PERCENTILE_DISC functions available in most relational database management systems. Both PERCENTILE_CONT and PERCENTILE_DISC are aggregate functions in SQL used to calculate the percentiles of a set of values. However, there are some differences between them.

Here’s an example of how to use the PERCENTILE_CONT to calculate the 75th percentile (i.e., the value below which 75% of the data falls) of a column called sales in a table called orders:

SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sales) AS p75 
FROM orders;

In this example, the argument 0.75 specifies the percentile to calculate (in this case, the 75th percentile). The ORDER BY clause specifies the ordering of the data to use when calculating the percentile, and in this case, the data is ordered by the sales column.

The PERCENTILE_CONT retrieves a continuous percentile value, meaning a value interpolated between the two closest values in the data set. For example, if the result set row count is not divisible by 4 and therefore there is no discrete 75th percentile value, then the returned value is interpolated from the two adjacent values in the set.

If you prefer to use discrete percentile values (i.e., values actually present in the data set), you can use the PERCENTILE_DISC function instead:

SELECT PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY sales) AS p75 
FROM orders;

In this example, the PERCENTILE_DISC returns a value corresponding to the data set’s nearest actual value.