Intermediate10 min read PostgreSQL

Window Frames & FILTER — Running Totals, Moving Averages, Conditional Counts

Control exactly which rows a window function sees with ROWS BETWEEN, and replace CASE-inside-SUM with the FILTER clause for cleaner conditional aggregates.

FILTER
ROWS BETWEEN
window frame
running total
moving average
FIRST_VALUE
LAST_VALUE

Use this when you need to:

Report headcount next to registered, on-leave and withdrawn counts in the same row — one pass, no CASE gymnastics
Track a student's cumulative credits term by term to spot the moment they cross 60 credits
Smooth a noisy enrollment trend with a three-term moving average for the Board slide
Show a student's first and latest academic standing on one line for the advising dashboard

Window Functions covered OVER, PARTITION BY and the ranking family. This lesson covers the two pieces that separate "knows window functions" from "writes reporting SQL": the FILTER clause, which puts a WHERE on a single aggregate, and the window frame, which decides which rows a running calculation is allowed to see. Every example runs as-is in the QueryU editor.

FILTER — a WHERE clause for one aggregate

The classic way to count a subset next to the total is SUM(CASE WHEN … THEN 1 ELSE 0 END). It works, but it hides the intent inside arithmetic and only works for counts and sums. FILTER (WHERE …) attaches a condition to any aggregate — COUNT, AVG, STRING_AGG, PERCENTILE_CONT — and reads exactly like the question you were asked.

-- Both columns give the same answer; the second one says what it means.
SELECT
  t.terms_id,
  COUNT(*)                                                       AS headcount,
  SUM(CASE WHEN st.sttr_current_status = 'R' THEN 1 ELSE 0 END)  AS registered_old_way,
  COUNT(*) FILTER (WHERE st.sttr_current_status = 'R')           AS registered_filter
FROM ods_student_terms st
  JOIN ods_terms t ON t.terms_id = st.sttr_term
GROUP BY t.terms_id, t.term_start_date
ORDER BY t.term_start_date;

-- A full status breakdown per term, one pass over the table
SELECT
  t.terms_id,
  t.term_desc,
  COUNT(*)                                              AS headcount,
  COUNT(*) FILTER (WHERE st.sttr_current_status = 'R')  AS registered,
  COUNT(*) FILTER (WHERE st.sttr_current_status = 'L')  AS on_leave,
  COUNT(*) FILTER (WHERE st.sttr_current_status = 'W')  AS withdrew,
  ROUND(100.0 * COUNT(*) FILTER (WHERE st.sttr_current_status = 'R') / COUNT(*), 1) AS pct_registered
FROM ods_student_terms st
  JOIN ods_terms t ON t.terms_id = st.sttr_term
GROUP BY t.terms_id, t.term_desc, t.term_start_date
ORDER BY t.term_start_date;

FILTER works on every aggregate, not just COUNT. AVG(stpr_gpa) FILTER (WHERE first_gen_ind = 'Y') gives a subgroup mean without a second query, and STRING_AGG(...) FILTER (WHERE saa_is_active = 'Y') lists only the active advisors. Rows that fail the filter are simply skipped — they do not become NULL or zero.

The frame — which rows can the window see?

When a window has an ORDER BY, PostgreSQL does not hand the function the whole partition. It hands it a frame: by default, every row from the start of the partition up to the current row (and its ties). That default is what makes SUM(...) OVER (ORDER BY ...) a running total. ROWS BETWEEN lets you say exactly where the frame starts and ends — and saying it explicitly is the habit that avoids surprises.

-- Cumulative credits per student, term by term.
-- Aggregate to one row per student-term FIRST, then run the window over that.
WITH term_credits AS (
  SELECT
    scs.scs_student,
    t.terms_id,
    t.term_start_date,
    SUM(scs.scs_credits) AS term_credits
  FROM ods_stu_course_sec scs
    JOIN ods_terms t ON t.terms_id = scs.scs_term
  GROUP BY scs.scs_student, t.terms_id, t.term_start_date
)
SELECT
  scs_student,
  terms_id,
  term_credits,
  SUM(term_credits) OVER (
    PARTITION BY scs_student
    ORDER BY term_start_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_credits
FROM term_credits
ORDER BY scs_student, term_start_date;

Notice the ORDER BY is on term_start_date, never on the term code. '2025FA' sorts before '2025SP' as text, so a frame ordered by the code would run your total backwards through the year. Join ods_terms and order by the date.

Moving averages and "prior peak"

WITH registered_by_term AS (
  SELECT
    t.terms_id,
    t.term_start_date,
    COUNT(*) FILTER (WHERE st.sttr_current_status = 'R') AS registered
  FROM ods_student_terms st
    JOIN ods_terms t ON t.terms_id = st.sttr_term
  GROUP BY t.terms_id, t.term_start_date
)
SELECT
  terms_id,
  registered,
  -- this term and the two before it
  ROUND(AVG(registered) OVER (
    ORDER BY term_start_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 1) AS three_term_avg,
  -- everything strictly before this term
  MAX(registered) OVER (
    ORDER BY term_start_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  ) AS prior_peak
FROM registered_by_term
ORDER BY term_start_date;

Read the frame as a sentence: "2 PRECEDING AND CURRENT ROW" is a three-row window sliding down the sorted list; "UNBOUNDED PRECEDING AND 1 PRECEDING" is everything before this row, which is how you ask "was this term a record?" without a self-join. The first row's prior_peak is NULL because nothing precedes it — that is correct, not a bug.

RANGE vs ROWS — the tie trap

The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE treats every row that ties on the ORDER BY key as the same "current row". Three applications received on the same day all get the same running count. If you want one-at-a-time behaviour, say ROWS.

SELECT
  applications_id,
  appl_date,
  COUNT(*) OVER (ORDER BY appl_date)                        AS running_range,  -- ties share a value
  COUNT(*) OVER (ORDER BY appl_date
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows  -- strictly one at a time
FROM ods_applications
WHERE appl_start_term = '2026FA'
ORDER BY appl_date, applications_id
LIMIT 12;

FIRST_VALUE and LAST_VALUE need the whole frame

FIRST_VALUE works out of the box because the default frame always starts at the beginning. LAST_VALUE does not — with the default frame it returns the current row, every time, which looks like a bug until you remember the frame ends at CURRENT ROW. Extend it to UNBOUNDED FOLLOWING. The WINDOW clause lets you define that frame once and reuse it.

SELECT DISTINCT
  st.sttr_student,
  FIRST_VALUE(st.standing_desc) OVER w AS first_standing,
  LAST_VALUE(st.standing_desc)  OVER w AS latest_standing
FROM ods_student_terms st
  JOIN ods_terms t ON t.terms_id = st.sttr_term
WINDOW w AS (
  PARTITION BY st.sttr_student
  ORDER BY t.term_start_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY st.sttr_student;

Practice challenge

Using the cumulative-credits query above, find the term in which each student crossed 60 credits (the usual junior-standing threshold). Show the student, the term, that term's credits and the running total. A student should appear at most once.

Solution

WITH term_credits AS (
  SELECT
    scs.scs_student,
    t.terms_id,
    t.term_start_date,
    SUM(scs.scs_credits) AS term_credits
  FROM ods_stu_course_sec scs
    JOIN ods_terms t ON t.terms_id = scs.scs_term
  GROUP BY scs.scs_student, t.terms_id, t.term_start_date
),
running AS (
  SELECT
    scs_student,
    terms_id,
    term_credits,
    SUM(term_credits) OVER (
      PARTITION BY scs_student
      ORDER BY term_start_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_credits
  FROM term_credits
)
SELECT
  scs_student,
  terms_id,
  term_credits,
  running_credits
FROM running
WHERE running_credits >= 60
  AND running_credits - term_credits < 60   -- was below 60 before this term
ORDER BY scs_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