Percentage, Quantiles, Percentiles, Ntiles Window functions in SQL

We have learnt the Rank, Dense_rank, Row_number in the Introduction to windows functions. First_value, Last_value, Lag and Lead window functions have been discussed in First_Value, Last_Value, Lag, Lead window functions.

Percentages:

Percentage is a mathematical value that divides a range of values into 100 equal parts. Let us assume that a student had written an exam of 200 marks. Then if we divide into 100 equal parts, then it will come as 2 marks for each percent. If the student got 120 marks, then he had scored 60 percentage.

SELECT region,count(*) 'records of region',sum(count(*)) over() 'total records', count(*)*100/sum(count(*)) over() as 'percentage'
FROM training.insurance_data
group by region;

Percentage is an independent of the marks of other students. It can give only mention how good you have performed on a standalone basis.

Quantiles:

Quantile is a method to divide the into any number of equally sized intervals. If the dataset had been divided into 10 equal parts, then it is known as decile; 4 equal parts then it is a quartile; 100 equal parts then it is percentile.

Percentiles:

Percentile is typically used in most of the exams to share the number of students who are behind you. For example, if a student had received 70 percentiles, then it means that 70% of the students have scored marks less than this student. Percentile is dependent on the marks of the other students as well. It mentions that how good you have performed among the overall students participated.

SELECT * ,
percent_rank() 
over (partition by region order by claim) as 'percentile_claim'
FROM training.insurance_data
where region = 'southeast';

Here patient id 1 is the lowest insurance claimer in the southeast region.

We have two methodologies for calculating the percentiles i.e.., percentile discrete and percentile continuous. Percentile discrete will provide the values from the data set only whereas percentile continuous will provide the value by interpolation. Let us assume that we have 20th record which has percentile of 74.6 and 21st record has 76 percentiles. If you use to find out the 75th percentile using percentile discrete, then it will return the value of 21st record. However, if you use percentile continuous, then it will return the value between 20th and 21st record by using interpolation method.

Segmentation:

Segmentation is used to divide the data into different categories based on a value of a certain column. For example, we have an insurance claim data and we would like to divide them into three categories based on the amount that had been claimed. Here the data will be equally distributed among the buckets, wherever possible. Let us assume that we have 21 records, then each bucket will have 7 records. If we have 23 records, then bucket1 and bucket2 will have 8 records and bucket three will have 7 records.

SELECT PatientID,age,gender,region ,claim,
ntile(10) over (partition by region order by claim desc) as 'ntile_region',
ntile(10) over ( order by claim desc) as 'ntile'
FROM training.insurance_data
where region = 'southeast'
;