Advanced10 min read PostgreSQL

Percentiles & Statistical Aggregates — Medians, Spread, Correlation

Go past AVG: medians and quartiles with PERCENTILE_CONT, spread with STDDEV, relationships with CORR, the most common value with MODE, and z-scores with an empty OVER ().

PERCENTILE_CONT
PERCENTILE_DISC
STDDEV
CORR
MODE
z-score
WITHIN GROUP

Use this when you need to:

Report the median GPA — the number that does not move when three students post a 1.2
Size a GPA gap between first-generation and continuing-generation students with its spread, not just two averages
Check whether high-school GPA actually predicts college GPA before someone builds an admissions rule on it
Find the students more than two standard deviations below the mean for an early-alert list

Averages hide shape. Two departments can share a mean GPA of 2.9 while one is tightly clustered and the other is half honours students and half probation. PostgreSQL ships the statistics you need to see the difference, and they all slot into an ordinary GROUP BY. The ordered-set aggregates (PERCENTILE_CONT, PERCENTILE_DISC, MODE) use the WITHIN GROUP (ORDER BY …) syntax because they need to know the sort order of the values they summarise.

Median vs mean

SELECT
  COALESCE(p.first_gen_ind, 'Unknown')           AS first_gen,
  COUNT(sal.stpr_gpa)                            AS students_with_gpa,
  ROUND(AVG(sal.stpr_gpa), 3)                    AS mean_gpa,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal.stpr_gpa)::numeric, 3) AS median_gpa
FROM ods_stu_acad_levels sal
  JOIN ods_person p ON p.id = sal.students_id
WHERE sal.stpr_gpa IS NOT NULL
GROUP BY p.first_gen_ind
ORDER BY first_gen;

PERCENTILE_CONT returns double precision, and ROUND(double, integer) does not exist in PostgreSQL — hence the ::numeric cast before rounding. The GPA column is numeric, so AVG needs no cast. Also note that stpr_gpa is on ods_stu_acad_levels, not on ods_student_terms, which has no GPA column at all.

Quartiles in one call

Pass an array of fractions and get an array of percentiles back. PERCENTILE_CONT interpolates between neighbouring values, so the median of an even-sized set is the midpoint; PERCENTILE_DISC returns an actual value from the data, which is what you want when the number must correspond to a real student.

SELECT
  PERCENTILE_CONT(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY stpr_gpa) AS quartiles_interpolated,
  PERCENTILE_DISC(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY stpr_gpa) AS quartiles_actual_values,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY stpr_gpa)                  AS p90
FROM ods_stu_acad_levels
WHERE stpr_gpa IS NOT NULL;

Spread and gap analysis

A subgroup gap is a mean (or median) compared with a reference. Compute the reference once in a CTE and CROSS JOIN it so every group row can subtract it. STDDEV_SAMP is the sample standard deviation — the right choice when the group is a sample of a larger population, which is nearly always the case for a subgroup.

WITH overall AS (
  SELECT AVG(stpr_gpa) AS all_students_mean
  FROM ods_stu_acad_levels
  WHERE stpr_gpa IS NOT NULL
)
SELECT
  p.ethnic_desc,
  COUNT(*)                                          AS students,
  ROUND(AVG(sal.stpr_gpa), 3)                       AS mean_gpa,
  ROUND(STDDEV_SAMP(sal.stpr_gpa), 3)               AS gpa_stddev,
  ROUND(MIN(sal.stpr_gpa), 2)                       AS lowest,
  ROUND(MAX(sal.stpr_gpa), 2)                       AS highest,
  ROUND(AVG(sal.stpr_gpa) - o.all_students_mean, 3) AS gap_vs_all
FROM ods_stu_acad_levels sal
  JOIN ods_person p ON p.id = sal.students_id
  CROSS JOIN overall o
WHERE sal.stpr_gpa IS NOT NULL
GROUP BY p.ethnic_desc, o.all_students_mean
ORDER BY gap_vs_all;

Always show the group size next to a gap. A 0.3 gap on ten students is noise; on three hundred it is a finding. If a group is small enough that one student could flip the sign, say so on the slide.

Correlation — do two measures move together?

CORR(x, y) returns Pearson's r between -1 and 1 and silently ignores any pair with a NULL on either side. Applicants become students, so ods_applications joins to ods_stu_acad_levels through the person id. In the practice data the coefficient is close to zero — high-school GPA and college GPA were generated independently — which is itself the lesson: check before you assume.

SELECT
  COUNT(*)                                             AS matched_students,
  ROUND(CORR(a.appl_hs_gpa,   sal.stpr_gpa)::numeric, 3) AS hs_to_college_gpa_corr,
  ROUND(CORR(a.appl_sat_total, sal.stpr_gpa)::numeric, 3) AS sat_to_gpa_corr
FROM ods_applications a
  JOIN ods_stu_acad_levels sal ON sal.students_id = a.appl_applicant
WHERE a.appl_hs_gpa IS NOT NULL
  AND sal.stpr_gpa  IS NOT NULL;

MODE — the most common value

SELECT
  scs_course_name,
  COUNT(*)                                            AS graded,
  MODE() WITHIN GROUP (ORDER BY scs_verified_grade)   AS most_common_grade,
  ROUND(AVG(scs_grade_pts), 2)                        AS mean_grade_pts
FROM ods_stu_course_sec
WHERE scs_verified_grade IS NOT NULL
GROUP BY scs_course_name
ORDER BY graded DESC
LIMIT 10;

Z-scores — how far from typical?

A z-score is (value − mean) ÷ standard deviation. Both the mean and the standard deviation of the whole set are available on every row through a window with an empty OVER (), so the standardisation is one expression with no subquery.

SELECT
  students_id,
  stpr_gpa,
  ROUND((stpr_gpa - AVG(stpr_gpa) OVER ()) / STDDEV_SAMP(stpr_gpa) OVER (), 2) AS gpa_z_score
FROM ods_stu_acad_levels
WHERE stpr_gpa IS NOT NULL
ORDER BY gpa_z_score
LIMIT 10;

Practice challenge

For each department with at least five students who have a GPA, report the number of students, the median GPA and the 90th-percentile GPA, highest median first. The department lives on ods_stu_acad_levels.stpr_dept.

Solution

SELECT
  d.dept_desc                                                                   AS department,
  COUNT(sal.stpr_gpa)                                                           AS students,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal.stpr_gpa)::numeric, 3) AS median_gpa,
  ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY sal.stpr_gpa)::numeric, 3) AS p90_gpa
FROM ods_stu_acad_levels sal
  JOIN ods_departments d ON d.departments_id = sal.stpr_dept
WHERE sal.stpr_gpa IS NOT NULL
GROUP BY d.dept_desc
HAVING COUNT(sal.stpr_gpa) >= 5
ORDER BY median_gpa DESC;

Practise this on real university data

Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.

Run your first query Browse practice problems

See all 21 lessons