Advanced14 min read PostgreSQL

The Higher-Ed Analyst Track — Census, FTE, Retention, Funnel, Financial Impact

The eight queries an institutional-research analyst is asked for most, built end to end on the practice schema: census headcount and FTE, fall-to-fall retention, a cohort survival curve, persistence, an equity gap, the admissions funnel, revenue at risk, and course DFW rates.

retention
persistence
cohort
census
FTE
admissions funnel
yield
DFW
equity gap
financial impact

Use this when you need to:

Report census headcount and FTE by level for the term that just locked
Give leadership fall-to-fall retention with a breakdown of where the missing students went
Plot a cohort's survival curve across every term since it entered
Show the admissions funnel — applied, accepted, deposited, enrolled — with admit rate and yield per start term
Put a dollar figure on non-retention so the retention initiative gets funded

Every metric here is a cohort definition, a match across terms, and a ratio. What makes them defensible is that the definition is written down in the query: which status counts as registered, which term is the base, what the FTE divisor is. Each section follows the same shape — define the population in a CTE, match it to a later state, aggregate — so that swapping the term code is the only change between this year's report and next year's.

1. Census snapshot: headcount and FTE

Headcount is one row per registered student-term. FTE (full-time equivalent) converts credit hours into "how many full-time students this workload represents", and the divisor is institutional policy — 15 undergraduate credits or 12 graduate credits per FTE is a common convention, but yours may differ. Put the divisor in its own CTE so the policy is visible and changeable, not buried in a CASE.

WITH term_credits AS (
  -- one row per student: attempted credits in the census term
  SELECT scs_student, SUM(scs_credits) AS credit_hours
  FROM ods_stu_course_sec
  WHERE scs_term = '2026FA'
  GROUP BY scs_student
),
fte_policy AS (
  -- the divisor is a business rule; keep it where a reviewer can see it
  SELECT * FROM (VALUES ('UG', 15.0), ('GR', 12.0)) AS p(acad_level, credits_per_fte)
)
SELECT
  st.academic_level_desc                                  AS level,
  COUNT(*)                                                AS headcount,
  COUNT(*) FILTER (WHERE st.sttr_student_load = 'F')      AS full_time,
  COUNT(*) FILTER (WHERE st.sttr_student_load <> 'F')     AS less_than_full_time,
  SUM(COALESCE(tc.credit_hours, 0))                       AS credit_hours,
  ROUND(SUM(COALESCE(tc.credit_hours, 0)) / fp.credits_per_fte, 1) AS fte
FROM ods_student_terms st
  LEFT JOIN term_credits tc ON tc.scs_student = st.sttr_student
  LEFT JOIN fte_policy   fp ON fp.acad_level  = st.sttr_acad_level
WHERE st.sttr_term = '2026FA'
  AND st.sttr_current_status = 'R'
GROUP BY st.academic_level_desc, fp.credits_per_fte
ORDER BY level;

A real census freezes the data on the census date (x_term_census_dates on ods_terms). This schema holds current-state rows, so the query reads "as of now". In production you would either snapshot the tables on census day or filter a status-history table to changes before that date.

2. Fall-to-fall retention — and where the rest went

Retention is "registered in fall N, registered again in fall N+1". The LEFT JOIN is essential: an INNER JOIN would drop the students who did not return and you would report 100% every time. Reporting the next-fall status breakdown, including No record, tells leadership whether the loss is withdrawals, leaves, or students who simply never came back.

SELECT
  COALESCE(nxt.status_desc, 'No record')             AS status_next_fall,
  COUNT(*)                                            AS students,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1)  AS pct
FROM ods_student_terms fall
  LEFT JOIN ods_student_terms nxt
    ON nxt.sttr_student = fall.sttr_student
   AND nxt.sttr_term    = '2027FA'
WHERE fall.sttr_term = '2026FA'
  AND fall.sttr_current_status = 'R'
GROUP BY nxt.status_desc
ORDER BY students DESC;

-- The same measure by academic level, as a rate
WITH base AS (
  SELECT sttr_student, academic_level_desc FROM ods_student_terms
  WHERE sttr_term = '2026FA' AND sttr_current_status = 'R'
),
retained AS (
  SELECT sttr_student FROM ods_student_terms
  WHERE sttr_term = '2027FA' AND sttr_current_status = 'R'
)
SELECT
  b.academic_level_desc                                AS level,
  COUNT(*)                                             AS registered_fall_2026,
  COUNT(r.sttr_student)                                AS retained_fall_2027,
  ROUND(100.0 * COUNT(r.sttr_student) / COUNT(*), 1)   AS retention_rate_pct
FROM base b
  LEFT JOIN retained r ON r.sttr_student = b.sttr_student
GROUP BY b.academic_level_desc
ORDER BY level;

3. Cohort survival curve

A first-time cohort is every student whose first registered term is the cohort term. Cross-join that cohort with every later term and LEFT JOIN their status; the share still registered per term is the survival curve. This is the query behind the "class of Fall 2024" line on a retention dashboard.

WITH first_term AS (
  SELECT st.sttr_student, MIN(t.term_start_date) AS first_start
  FROM ods_student_terms st
    JOIN ods_terms t ON t.terms_id = st.sttr_term
  WHERE st.sttr_current_status = 'R'
  GROUP BY st.sttr_student
),
cohort AS (
  SELECT ft.sttr_student, ft.first_start
  FROM first_term ft
    JOIN ods_terms t ON t.term_start_date = ft.first_start
  WHERE t.terms_id = '2024FA'
),
later_terms AS (
  SELECT terms_id, term_start_date
  FROM ods_terms
  WHERE term_start_date >= (SELECT MIN(first_start) FROM cohort)
    AND terms_id IN (SELECT DISTINCT sttr_term FROM ods_student_terms)
)
SELECT
  lt.terms_id,
  COUNT(*)                                                           AS cohort_size,
  COUNT(st.sttr_student) FILTER (WHERE st.sttr_current_status = 'R') AS still_registered,
  ROUND(100.0 * COUNT(st.sttr_student) FILTER (WHERE st.sttr_current_status = 'R') / COUNT(*), 1) AS pct_of_cohort
FROM cohort c
  CROSS JOIN later_terms lt
  LEFT JOIN ods_student_terms st
    ON st.sttr_student = c.sttr_student
   AND st.sttr_term    = lt.terms_id
GROUP BY lt.terms_id, lt.term_start_date
ORDER BY lt.term_start_date;

4. Fall-to-spring persistence

-- Same shape as retention, one term apart
WITH fall AS (
  SELECT sttr_student FROM ods_student_terms
  WHERE sttr_term = '2026FA' AND sttr_current_status = 'R'
),
spring AS (
  SELECT sttr_student FROM ods_student_terms
  WHERE sttr_term = '2027SP' AND sttr_current_status = 'R'
)
SELECT
  COUNT(*)                                             AS fall_registered,
  COUNT(sp.sttr_student)                               AS persisted_to_spring,
  ROUND(100.0 * COUNT(sp.sttr_student) / COUNT(*), 1)  AS persistence_rate_pct
FROM fall f
  LEFT JOIN spring sp ON sp.sttr_student = f.sttr_student;

5. Equity gap

A gap is a subgroup rate minus the overall rate. The overall rate is available on every group row through SUM(COUNT(...)) OVER () — the window runs over the grouped result — so no second query is needed. Always print the group size beside the gap.

WITH base AS (
  SELECT sttr_student FROM ods_student_terms
  WHERE sttr_term = '2026FA' AND sttr_current_status = 'R'
),
retained AS (
  SELECT sttr_student FROM ods_student_terms
  WHERE sttr_term = '2027FA' AND sttr_current_status = 'R'
)
SELECT
  COALESCE(p.first_gen_ind, 'Unknown')                 AS first_gen,
  COUNT(*)                                             AS registered_fall_2026,
  COUNT(r.sttr_student)                                AS retained_fall_2027,
  ROUND(100.0 * COUNT(r.sttr_student) / COUNT(*), 1)   AS retention_rate_pct,
  ROUND(100.0 * COUNT(r.sttr_student) / COUNT(*), 1)
    - ROUND(100.0 * SUM(COUNT(r.sttr_student)) OVER () / SUM(COUNT(*)) OVER (), 1) AS gap_vs_overall_pts
FROM base b
  JOIN ods_person p ON p.id = b.sttr_student
  LEFT JOIN retained r ON r.sttr_student = b.sttr_student
GROUP BY p.first_gen_ind
ORDER BY first_gen;

6. Admissions funnel

The status history has one row per change, so "reached Accepted" is a BOOL_OR over each application's rows. Collapse the history to one flag row per application first, then count the flags per start term. Admit rate is accepted ÷ applied; yield is deposited ÷ accepted. NULLIF guards the division for a term with no admits.

WITH stages AS (
  SELECT
    applications_id,
    BOOL_OR(appl_status = 'AC') AS accepted,
    BOOL_OR(appl_status = 'DP') AS deposited,
    BOOL_OR(appl_status = 'MS') AS enrolled     -- 'Move to Student'
  FROM ods_appl_status_history
  GROUP BY applications_id
)
SELECT
  a.appl_start_term,
  COUNT(*)                                   AS applied,
  COUNT(*) FILTER (WHERE s.accepted)         AS accepted,
  COUNT(*) FILTER (WHERE s.deposited)        AS deposited,
  COUNT(*) FILTER (WHERE s.enrolled)         AS enrolled,
  ROUND(100.0 * COUNT(*) FILTER (WHERE s.accepted)  / COUNT(*), 1) AS admit_rate_pct,
  ROUND(100.0 * COUNT(*) FILTER (WHERE s.deposited)
        / NULLIF(COUNT(*) FILTER (WHERE s.accepted), 0), 1)         AS yield_pct
FROM ods_applications a
  LEFT JOIN stages s ON s.applications_id = a.applications_id
  JOIN ods_terms t ON t.terms_id = a.appl_start_term
GROUP BY a.appl_start_term, t.term_start_date
ORDER BY t.term_start_date;

7. Financial impact of non-retention

Leadership funds retention work when it sees the revenue. Net cost per student is cost of attendance minus grants for the aid year that matches the retention year — ods_financial_aid is student × aid-year, so filter fa_year or the join fans out. Revenue at risk is the non-retained count times the average net cost. It is an estimate; say so on the slide, and show the inputs.

WITH base AS (
  SELECT sttr_student
  FROM ods_student_terms
  WHERE sttr_term = '2026FA' AND sttr_current_status = 'R'
),
retained AS (
  SELECT sttr_student
  FROM ods_student_terms
  WHERE sttr_term = '2027FA' AND sttr_current_status = 'R'
),
net_cost AS (
  SELECT fa_student, fa_cost_of_attendance - fa_total_grants AS net_cost
  FROM ods_financial_aid
  WHERE fa_year = '2026-2027'
)
SELECT
  COUNT(*)                                                 AS registered_fall_2026,
  COUNT(r.sttr_student)                                    AS retained_fall_2027,
  COUNT(*) - COUNT(r.sttr_student)                         AS not_retained,
  ROUND(AVG(nc.net_cost), 0)                               AS avg_net_cost_per_student,
  ROUND((COUNT(*) - COUNT(r.sttr_student)) * AVG(nc.net_cost), 0) AS revenue_at_risk
FROM base b
  LEFT JOIN retained r  ON r.sttr_student = b.sttr_student
  LEFT JOIN net_cost nc ON nc.fa_student  = b.sttr_student;

8. Course success: DFW rates

-- Gateway courses with the highest share of D and F grades. Add 'W' when your
-- grade scheme records withdrawals as a grade. The HAVING floor keeps a
-- three-student section from topping the list.
SELECT
  scs_course_name,
  COUNT(*)                                                     AS graded_enrollments,
  COUNT(*) FILTER (WHERE scs_verified_grade IN ('D', 'F'))     AS d_or_f,
  ROUND(100.0 * COUNT(*) FILTER (WHERE scs_verified_grade IN ('D', 'F')) / COUNT(*), 1) AS df_rate_pct
FROM ods_stu_course_sec
WHERE scs_verified_grade IS NOT NULL
GROUP BY scs_course_name
HAVING COUNT(*) >= 20
ORDER BY df_rate_pct DESC
LIMIT 10;

Every query above hard-codes a term. In a reporting tool, replace the literals with parameters (a base term and a comparison term) and derive the aid year from the base term's reporting year — then the same report runs for any pair of terms without an edit.

Practice challenge

Compute fall-to-fall retention from Fall 2026 to Fall 2027 by academic standing in Fall 2026 (Good Standing, Academic Warning, Probation, Suspension). Show the base count, the retained count and the rate, and order the rows so the standing with the lowest retention comes first.

Solution

WITH base AS (
  SELECT sttr_student, standing_desc
  FROM ods_student_terms
  WHERE sttr_term = '2026FA' AND sttr_current_status = 'R'
),
retained AS (
  SELECT sttr_student
  FROM ods_student_terms
  WHERE sttr_term = '2027FA' AND sttr_current_status = 'R'
)
SELECT
  b.standing_desc                                      AS standing_fall_2026,
  COUNT(*)                                             AS registered_fall_2026,
  COUNT(r.sttr_student)                                AS retained_fall_2027,
  ROUND(100.0 * COUNT(r.sttr_student) / COUNT(*), 1)   AS retention_rate_pct
FROM base b
  LEFT JOIN retained r ON r.sttr_student = b.sttr_student
GROUP BY b.standing_desc
ORDER BY retention_rate_pct, standing_fall_2026;

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