Advanced10 min read PostgreSQL

Calendar & Gap Logic — Term Order, GENERATE_SERIES, Stop-outs

Why term codes must never be sorted, how to number terms chronologically, how to manufacture calendar rows with GENERATE_SERIES, and how to detect students who left and came back.

ods_terms
term_start_date
GENERATE_SERIES
WITH ORDINALITY
LAG
stop-out
calendar table

Use this when you need to:

Sort a trend chart so Spring comes before Fall in the same year — every time, in every query
Build a week-of-term axis for a registration-velocity chart, with the census week marked
Show terms that offered zero sections instead of silently dropping them from the report
Find students who returned after a leave or withdrawal, and how many terms they were away

Time in higher education is not a timestamp — it is a term. Term codes look sortable and are not, real calendars have gaps that a GROUP BY will hide, and the interesting students are the ones with a hole in their history. This lesson is the toolkit for all three, built on ods_terms, which is the calendar dimension of the practice schema.

Term codes do not sort

'2024FA' is less than '2024SP' as text because F comes before S. Sorted by the code, every year's Fall lands before its Spring, so a "term over term" chart runs backwards for half of its points. This has broken more than one production query. The only chronological key is term_start_date on ods_terms — join it and order by it, every time.

-- Wrong: Fall 2021 sorts before Spring 2021
SELECT terms_id, term_desc, term_start_date
FROM ods_terms
ORDER BY terms_id;

-- Right: the date is the order
SELECT terms_id, term_desc, term_start_date
FROM ods_terms
ORDER BY term_start_date;

The same rule applies to MIN(), MAX(), BETWEEN and window ORDER BYs. MAX(sttr_term) returns the alphabetically last code, not the latest term. Get "latest" by joining ods_terms and taking the greatest term_start_date.

A term sequence number

Once you have the right order, give every term an integer position. Consecutive integers turn "is this the next term?" into simple subtraction, which is what gap detection needs. Put this in a CTE and reuse it.

SELECT
  terms_id,
  term_start_date,
  ROW_NUMBER() OVER (ORDER BY term_start_date) AS term_seq
FROM ods_terms
ORDER BY term_seq;

GENERATE_SERIES — manufacture the calendar

GENERATE_SERIES returns a row for every step between two values. Feed it a term's start and end dates and you have a month axis, a week axis, or a day axis for that term — with nothing missing, because the rows come from arithmetic rather than from whatever happened to be in the data.

-- One row per calendar month touched by Fall 2026
SELECT
  t.terms_id,
  month_start::date AS month_start
FROM ods_terms t
  CROSS JOIN LATERAL generate_series(
    DATE_TRUNC('month', t.term_start_date),
    t.term_end_date,
    INTERVAL '1 month'
  ) AS month_start
WHERE t.terms_id = '2026FA'
ORDER BY month_start;

-- Week-of-term axis, numbered, with the census week flagged
SELECT
  t.terms_id,
  w.week_no,
  w.week_start::date AS week_start,
  (w.week_start::date >= t.x_term_census_dates) AS after_census
FROM ods_terms t
  CROSS JOIN LATERAL generate_series(t.term_start_date, t.term_end_date, INTERVAL '1 week')
    WITH ORDINALITY AS w(week_start, week_no)
WHERE t.terms_id = '2026FA'
ORDER BY w.week_no;

WITH ORDINALITY appends a 1-based row number to any set-returning function. It is the cleanest way to get "week 1, week 2, …" without a separate ROW_NUMBER window.

The calendar dimension makes empty periods visible

GROUP BY only produces groups that have rows. A term with no sections, or a month with no applications, simply vanishes from the output — and a vanished zero is the most dangerous kind, because nobody sees that it is missing. Start from the calendar table and LEFT JOIN the facts to it; the zero shows up as a zero.

SELECT
  t.terms_id,
  t.term_desc,
  COUNT(cs.course_sections_id) AS sections_offered   -- COUNT(column) ignores the NULLs from the LEFT JOIN
FROM ods_terms t
  LEFT JOIN ods_course_sections cs ON cs.sec_term = t.terms_id
GROUP BY t.terms_id, t.term_desc, t.term_start_date
ORDER BY t.term_start_date;

-- Two fact tables against the same calendar: registered status vs actual course enrollment.
-- Where they disagree is a data-quality finding, not a rounding error.
SELECT
  t.terms_id,
  COUNT(DISTINCT st.sttr_student)  FILTER (WHERE st.sttr_current_status = 'R') AS registered,
  COUNT(DISTINCT scs.scs_student)                                              AS enrolled_in_a_section,
  COUNT(DISTINCT st.sttr_student)  FILTER (WHERE st.sttr_current_status = 'R')
    - COUNT(DISTINCT scs.scs_student)                                          AS registered_without_courses
FROM ods_terms t
  LEFT JOIN ods_student_terms   st  ON st.sttr_term = t.terms_id
  LEFT JOIN ods_stu_course_sec  scs ON scs.scs_term = t.terms_id
WHERE t.terms_id IN (SELECT DISTINCT sttr_term FROM ods_student_terms)
GROUP BY t.terms_id, t.term_start_date
ORDER BY t.term_start_date;

Two LEFT JOINs from the same driving table multiply rows (every student-term row pairs with every enrollment row). The DISTINCT inside each COUNT is what keeps the numbers honest. For sums you would aggregate each fact table in its own CTE first and join the two summaries.

Stop-outs and returners

A stop-out is a student who was away and came back. With the term sequence in place and LAG() over each student's history, a return is any Registered row whose previous row was Leave or Withdrew. The distance from the student's first term to the return term says how long they were gone.

WITH term_seq AS (
  SELECT terms_id, term_start_date,
         ROW_NUMBER() OVER (ORDER BY term_start_date) AS term_seq
  FROM ods_terms
),
history AS (
  SELECT
    st.sttr_student,
    ts.terms_id,
    ts.term_seq,
    st.sttr_current_status                                                              AS status,
    LAG(st.sttr_current_status) OVER (PARTITION BY st.sttr_student ORDER BY ts.term_seq) AS prev_status,
    MIN(ts.term_seq)            OVER (PARTITION BY st.sttr_student)                     AS first_seq
  FROM ods_student_terms st
    JOIN term_seq ts ON ts.terms_id = st.sttr_term
)
SELECT
  sttr_student,
  terms_id              AS returned_in,
  prev_status           AS status_before_return,
  term_seq - first_seq  AS terms_away
FROM history
WHERE status = 'R'
  AND prev_status IN ('L', 'W')
ORDER BY term_seq, sttr_student;

The same CTE answers the opposite question — who left — by flipping the WHERE to status IN ('L', 'W') AND prev_status = 'R'. And if a student can be missing a row entirely rather than carrying a Leave row, compare term_seq with LAG(term_seq): a difference greater than 1 is a silent gap.

Dates inside the term

-- date - date gives an integer number of days; LEAD reaches the next term's start
SELECT
  terms_id,
  term_start_date,
  x_term_census_dates,
  x_term_census_dates - term_start_date          AS days_to_census,
  term_end_date - term_start_date + 1            AS term_length_days,
  LEAD(term_start_date) OVER (ORDER BY term_start_date) - term_end_date AS break_days_after
FROM ods_terms
ORDER BY term_start_date;

Practice challenge

For every student whose status has ever changed, show their full status trajectory as one string in chronological order — for example 'L → L → R → R' — together with the number of terms on record. Students who were the same status every term should not appear.

Solution

SELECT
  st.sttr_student,
  STRING_AGG(st.sttr_current_status, ' → ' ORDER BY t.term_start_date) AS status_trajectory,
  COUNT(*)                                                           AS terms_on_record
FROM ods_student_terms st
  JOIN ods_terms t ON t.terms_id = st.sttr_term
GROUP BY st.sttr_student
HAVING COUNT(DISTINCT st.sttr_current_status) > 1
ORDER BY st.sttr_student;

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