Skip to main content

Persistence Prediction Queries

Student Persistence Predictions​

This query fetches all historical persistence prediction scores, the institution ID, and the date the score was generated for desired <STUDENT_ID>.

SELECT
 fcs.event_ts AS score_date
 , dp.institution_person_id
 , fcs.civ_career
 , fcs.prediction
FROM data_mart.fact_continuation_scores fcs
 INNER JOIN data_mart.dim_person dp USING (dim_person_id, institution_id)
WHERE
 dp.institution_person_id = '<STUDENT_ID>'
ORDER BY dp.institution_person_id, fcs.event_ts DESC;

Student Persistence Score Shift​

This query returns the latest persistence score for each student and career (academic level), and calculates the one day and one week shift in this score so that the customer can identify students whose score has changed recently. This query can be adapted to calculate score shifts other than one day or week.

The query also displays the Civitas defined bucket for the student's latest persistence score using the default ranges for these buckets: Very Low, Low, Moderate, High, Very High

-- Find the latest persistence score for each student and career.
WITH latest_continuation_score AS (
  SELECT
    fcs.dim_person_id
    , fcs.civ_career
    , fcs.civ_term_id
    , fcs.event_ts
    , fcs.prediction
  FROM data_mart.fact_continuation_scores fcs
  WHERE
    -- This step ensures that the query returns the latest score for each student.
    fcs.event_ts = (SELECT MAX(fcs1.event_ts)
                    FROM data_mart.fact_continuation_scores fcs1
                    WHERE fcs.dim_person_id = fcs1.dim_person_id
                      AND fcs.civ_career = fcs1.civ_career
                      AND fcs.civ_term_id = fcs1.civ_term_id)
    -- This step ensures that the query returns scores for currently enrolled students only.
    -- Only currently enrolled students will have a score on the latest day.
    AND fcs.event_ts = (SELECT MAX(fcs2.event_ts)
                        FROM data_mart.fact_continuation_scores fcs2)
)

, continuation_score_shift AS (
  -- Change the '7 days' or '1 day' thresholds below to use a different window for the score shift.
  SELECT
    lcs.dim_person_id
    , lcs.civ_career
    , lcs.civ_term_id
    , lcs.event_ts
    , lcs.prediction AS current_prediction
    , fcs_shift_7.prediction AS previous_prediction_7_days
    , fcs_shift_1.prediction AS previous_prediction_1_day
  FROM latest_continuation_score lcs
    LEFT JOIN data_mart.fact_continuation_scores fcs_shift_7 ON
      lcs.dim_person_id = fcs_shift_7.dim_person_id
      AND lcs.civ_career = fcs_shift_7.civ_career
      AND lcs.civ_term_id = fcs_shift_7.civ_term_id
      AND (lcs.event_ts - INTERVAL '7 days') = fcs_shift_7.event_ts
    LEFT JOIN data_mart.fact_continuation_scores fcs_shift_1 ON
      lcs.dim_person_id = fcs_shift_1.dim_person_id
      AND lcs.civ_career = fcs_shift_1.civ_career
      AND lcs.civ_term_id = fcs_shift_1.civ_term_id
      AND (lcs.event_ts - INTERVAL '1 day') = fcs_shift_1.event_ts
)

SELECT
  dp.institution_person_id
  , css.civ_career AS civitas_career_id
  , css.civ_term_id
  , css.event_ts::DATE AS event_date
  -- Note, these are the default ranges. They may have been adjusted for your institution.
  , CASE
      WHEN css.current_prediction >= 0.90 THEN 'Very High'
      WHEN css.current_prediction >= 0.70 THEN 'High'
      WHEN css.current_prediction >= 0.50 THEN 'Moderate'
      WHEN css.current_prediction >= 0.20 THEN 'Low'
      WHEN css.current_prediction < 0.20 THEN 'Very Low'
    ELSE NULL
    END AS persistence_likelihood -- Persistence Likelihood (Very High, High, Moderate, Low, Very Low)
  , css.current_prediction AS persistence_prediction_score -- Persistence Prediction Score %
  , css.current_prediction - css.previous_prediction_7_days AS weekly_prediction_score_shift -- Weekly Change in Persistence Score
  , css.current_prediction - css.previous_prediction_1_day AS daily_prediction_score_shift --Daily Change in Persistence Score
FROM data_mart.dim_person dp
  INNER JOIN continuation_score_shift css ON
    dp.dim_person_id = css.dim_person_id
WHERE
  CURRENT_DATE >= dp.raw_eff_ts
  AND CURRENT_DATE < dp.raw_eff_ts_end

Student Persistence Risk Indicators​

This query identifies students who exhibit multiple risk factors for academic failure or dropout, providing a comprehensive view of at-risk students with demographic breakdowns and specific intervention recommendations.

RISK FACTORS ANALYZED:

  1. Low Persistence Scores (< 50%) - Prediction model indicates low likelihood of continuation
  2. Poor Assignment Performance (< 60% average or >50% failing assignments)
  3. Reduced LMS Activity (< 75% of average student activity)
WITH active_term AS (
    -- Get the current active term with data quality checks
    SELECT
        dct.civ_term_id
        , dct.civ_career_term_start_dt
        , dct.civ_career_term_end_dt
    FROM data_mart.dim_career_term dct
    WHERE dct.is_active = 1
    LIMIT 1
)

, active_students AS (
    -- Get currently enrolled students in active term
    SELECT DISTINCT
        dp.raw_person_id
        , dp.institution_person_id
        , dp.dim_person_id
        , de.civ_career_id
        , dct.civ_term_id
        , dp.civ_first_to_attend_college
        -- Individual race/ethnicity flags from schema
        , dp.is_hispanic
        , dp.is_american_indian
        , dp.is_asian
        , dp.is_white
        , dp.is_black
        , dp.is_pacific_islander
        , dp.raw_gender
        , dp.primary_state
    FROM data_mart.dim_enrollment de
        INNER JOIN data_mart.dim_section ds
            ON de.raw_section_id = ds.raw_section_id
        INNER JOIN data_mart.dim_career_term dct
            ON ds.civ_term_id = dct.civ_term_id
            AND de.raw_career_id = dct.raw_career_id
        INNER JOIN data_mart.dim_person dp
            ON de.raw_person_id = dp.raw_person_id
            AND dp.raw_eff_ts_end > CURRENT_DATE  -- Only current/active person records
    WHERE
        dct.is_active = 1
        AND de.raw_career_id <> 'Not Available'
        --AND de.civ_career_id IN ('U', 'G') -- Limit to certain civ_career_id's as desired
        AND de.civ_is_enrolled = 1
)

-- RISK INDICATOR 1: Low Persistence Scores 
, latest_persistence_scores AS (
    SELECT
        acs.dim_person_id
        , acs.institution_person_id
        , fcs.prediction AS persistence_score
        , ROW_NUMBER() OVER (
            PARTITION BY acs.dim_person_id
            ORDER BY fcs.event_ts DESC
        ) AS rn
    FROM active_students acs
        INNER JOIN data_mart.fact_continuation_scores fcs
            ON acs.dim_person_id = fcs.dim_person_id
            AND acs.civ_career_id = fcs.civ_career
            AND acs.civ_term_id = fcs.civ_term_id
)

, low_persistence_students AS (
    SELECT
        lps.dim_person_id
        , lps.institution_person_id
        , lps.persistence_score
        , 'Low Persistence' AS risk_factor
    FROM latest_persistence_scores lps
    WHERE lps.rn = 1
        AND lps.persistence_score < 0.50
)

-- RISK INDICATOR 2: Poor Assignment Performance (using dim_person_id)
, assignment_performance AS (
    SELECT
        acs.dim_person_id
        , acs.institution_person_id
        , AVG(CASE
            WHEN fagf.raw_student_points_possible > 0
                THEN fagf.civ_assignment_grade / fagf.raw_student_points_possible
            ELSE fagf.civ_assignment_grade / 100.0
        END) AS avg_assignment_grade_pct
        , COUNT(fagf.civ_assignment_grade) AS total_assignments
        , COUNT(CASE
            WHEN (CASE
                WHEN fagf.raw_student_points_possible > 0
                    THEN fagf.civ_assignment_grade / fagf.raw_student_points_possible
                ELSE fagf.civ_assignment_grade / 100.0
            END) < 0.60
            THEN 1
        END) AS failing_assignments
    FROM active_students acs
        INNER JOIN data_mart.fact_assignment_grade_final fagf
            ON acs.dim_person_id = fagf.dim_person_id
    WHERE fagf.civ_assignment_grade IS NOT NULL
    GROUP BY acs.dim_person_id, acs.institution_person_id
)

, poor_assignment_students AS (
    SELECT
        ap.dim_person_id
        , ap.institution_person_id
        , ap.avg_assignment_grade_pct
        , ap.total_assignments
        , ap.failing_assignments
        , 'Poor Assignment Performance' AS risk_factor
    FROM assignment_performance ap
    WHERE
        ap.total_assignments >= 2
        AND (
            ap.avg_assignment_grade_pct < 0.60
            OR (ap.failing_assignments::FLOAT / ap.total_assignments) > 0.50
        )
)

-- RISK INDICATOR 3: Reduced LMS Activity
, term_lms_activity AS (
    -- Get all LMS activity for the active term
    SELECT
        fwa.dim_person_id
        , COUNT(fwa.event_ts) AS activity_count
    FROM data_mart.fact_web_activity fwa
        INNER JOIN active_term at
            ON fwa.event_ts BETWEEN at.civ_career_term_start_dt AND CURRENT_DATE
    WHERE fwa.civ_activity_type_id IN ('assignment', 'gradebook', 'post')
    GROUP BY fwa.dim_person_id
)

, lms_activity_stats AS (
    -- Calculate activity statistics
    SELECT
        AVG(activity_count) AS avg_activity_per_student
        , STDDEV(activity_count) AS stddev_activity
    FROM term_lms_activity
)

, student_lms_activity AS (
    -- Join students with their activity counts using dim_person_id
    SELECT
        acs.dim_person_id
        , acs.institution_person_id
        , COALESCE(tla.activity_count, 0) AS activity_count
        , las.avg_activity_per_student
    FROM active_students acs
        CROSS JOIN lms_activity_stats las
        LEFT JOIN term_lms_activity tla
            ON acs.dim_person_id = tla.dim_person_id
)

, reduced_lms_activity_students AS (
    SELECT
        sla.dim_person_id
        , sla.institution_person_id
        , sla.activity_count
        , sla.avg_activity_per_student
        , 'Reduced LMS Activity' AS risk_factor
    FROM student_lms_activity sla
    WHERE sla.activity_count < (sla.avg_activity_per_student * 0.75)
)

-- COMBINE RISK INDICATORS
, student_risk_factors AS (
    SELECT
        acs.dim_person_id
        , acs.institution_person_id
        , acs.raw_person_id
        , acs.civ_career_id
        , acs.civ_term_id
        , acs.civ_first_to_attend_college
        -- Construct race/ethnicity from individual flags
        , CASE
            WHEN acs.is_hispanic = 1 THEN 'Hispanic/Latino'
            WHEN acs.is_black = 1 THEN 'Black/African American'
            WHEN acs.is_asian = 1 THEN 'Asian'
            WHEN acs.is_white = 1 THEN 'White'
            WHEN acs.is_american_indian = 1 THEN 'American Indian/Alaska Native'
            WHEN acs.is_pacific_islander = 1 THEN 'Pacific Islander'
            ELSE 'Unknown/Other'
        END AS race_ethnicity
        , acs.raw_gender
        , acs.primary_state
        , lps.persistence_score
        , pas.avg_assignment_grade_pct
        , pas.total_assignments
        , pas.failing_assignments
        , rlas.activity_count AS lms_activity_count
        , rlas.avg_activity_per_student AS avg_lms_activity
        -- Risk factor flags
        , CASE WHEN lps.dim_person_id IS NOT NULL THEN 1 ELSE 0 END AS has_low_persistence
        , CASE WHEN pas.dim_person_id IS NOT NULL THEN 1 ELSE 0 END AS has_poor_assignments
        , CASE WHEN rlas.dim_person_id IS NOT NULL THEN 1 ELSE 0 END AS has_low_lms_activity
    FROM active_students acs
        LEFT JOIN low_persistence_students lps
            ON acs.dim_person_id = lps.dim_person_id
        LEFT JOIN poor_assignment_students pas
            ON acs.dim_person_id = pas.dim_person_id
        LEFT JOIN reduced_lms_activity_students rlas
            ON acs.dim_person_id = rlas.dim_person_id
)

, at_risk_students AS (
    SELECT
          dim_person_id
        , institution_person_id
        , raw_person_id
        , civ_career_id
        , civ_term_id
        , civ_first_to_attend_college
        -- Construct race/ethnicity from individual flags
        , race_ethnicity
        , raw_gender
        , primary_state
        , persistence_score
        , avg_assignment_grade_pct
        , total_assignments
        , failing_assignments
        , lms_activity_count
        , avg_lms_activity
        , has_low_persistence
        , has_poor_assignments
        , has_low_lms_activity
        -- Aggregate risk metrics
        , (has_low_persistence + has_poor_assignments + has_low_lms_activity) AS total_risk_factors
        , (has_low_persistence * 3 + has_poor_assignments * 2 + has_low_lms_activity * 1) AS priority_score
    FROM student_risk_factors
    WHERE (has_low_persistence + has_poor_assignments + has_low_lms_activity) >= 2
)

-- MAIN QUERY: Students with Multiple Warning Signs + Demographics
SELECT
    ars.raw_person_id
    , ars.institution_person_id
    , ars.civ_career_id

    -- Risk metrics
    , ars.total_risk_factors
    , ars.priority_score

    -- Risk Factor Details
    , CASE WHEN ars.has_low_persistence = 1
         THEN TRIM(TO_CHAR(ars.persistence_score * 100, '990.00')) || '%'
         ELSE 'N/A'
    END AS persistence_score_percentage

    , CASE WHEN ars.has_poor_assignments = 1
         THEN TRIM(TO_CHAR(ars.avg_assignment_grade_pct * 100, '990.00')) || '%'
         ELSE 'N/A'
    END AS avg_assignment_grade_percentage

    , CASE WHEN ars.has_low_lms_activity = 1
         THEN ars.lms_activity_count::VARCHAR
         ELSE 'N/A'
    END AS lms_activity_count

    -- Demographics
    , CASE
        WHEN ars.civ_first_to_attend_college = 1 THEN 'First Generation'
        ELSE 'Non First Generation'
    END AS first_gen_status

    , COALESCE(ars.race_ethnicity, 'Unknown') AS race_ethnicity

    , CASE
        WHEN UPPER(ars.raw_gender) IN ('M', 'MALE') THEN 'Male'
        WHEN UPPER(ars.raw_gender) IN ('F', 'FEMALE') THEN 'Female'
        ELSE 'Other/Unknown'
    END AS gender

    , COALESCE(ars.primary_state, 'Unknown') AS home_state

    -- Intervention Recommendations
    , CASE
        WHEN ars.total_risk_factors = 3 AND ars.has_low_persistence = 1
            THEN 'URGENT: Academic coaching + counseling + LMS training'
        WHEN ars.total_risk_factors = 3
            THEN 'URGENT: Comprehensive intervention needed'
        WHEN ars.has_low_persistence = 1 AND ars.has_poor_assignments = 1
            THEN 'HIGH: Tutoring + study skills support'
        WHEN ars.has_low_persistence = 1 AND ars.has_low_lms_activity = 1
            THEN 'HIGH: Engagement intervention + technical support'
        WHEN ars.has_poor_assignments = 1 AND ars.has_low_lms_activity = 1
            THEN 'MODERATE: Learning support + engagement strategies'
        ELSE 'NORMAL: Monitor closely + targeted support'
    END AS intervention_recommendation

FROM at_risk_students ars
ORDER BY
    ars.priority_score DESC
    , ars.total_risk_factors DESC
    , ars.dim_person_id ASC

Student Persistence Prediction by College​

This query returns the average persistence (continuation) score for each college.

Output Columns: Career ID Civitas Career ID Chamberlain Career Description Event Timestamp College ID Term Desciption College Name

WITH latest_continuation_score AS (
  SELECT
    fcs.dim_person_id
    , fcs.civ_career
    , fcs.event_ts
    , fcs.prediction
    , fcs.dim_date_id
    , fcs.civ_term_id
  FROM data_mart.fact_continuation_scores fcs
  WHERE fcs.event_ts = (SELECT MAX(fcs1.event_ts)
                        FROM data_mart.fact_continuation_scores fcs1)
),

college_score AS (
  SELECT
    dct.raw_career_id
    , lcs.civ_career
    , CASE
        WHEN lcs.civ_career = 'B' THEN 'MBA'
        WHEN lcs.civ_career = 'CE' THEN 'Continuing Education'
        WHEN lcs.civ_career = 'CR' THEN 'Correspondence'
        WHEN lcs.civ_career = 'D' THEN 'Doctorate'
        WHEN lcs.civ_career = 'G' THEN 'Graduate'
        WHEN lcs.civ_career = 'H' THEN 'High School'
        WHEN lcs.civ_career = 'L' THEN 'Law'
        WHEN lcs.civ_career = 'M' THEN 'Medical School'
        WHEN lcs.civ_career = 'P' THEN 'Pharmacy'
        WHEN lcs.civ_career = 'PB' THEN 'Post Baccalaureate'
        WHEN lcs.civ_career = 'U' THEN 'Undergraduate'
      ELSE 'Unknown'
      END AS civ_desc
    , lcs.event_ts
    , fcfl.text_value AS college_name
    , dct.raw_desc
    , lcs.prediction
  FROM latest_continuation_score lcs
    LEFT JOIN data_mart.fact_continuation_feature_latest fcfl USING (dim_person_id)
    LEFT JOIN data_mart.dim_continuation_feature dcf ON
      fcfl.dim_continuation_feature_id = dcf.dim_continuation_feature_id
    LEFT JOIN data_mart.dim_career_term dct ON
      lcs.civ_term_id = dct.civ_term_id
      AND lcs.civ_career = dct.civ_career_id
  WHERE dcf.civ_continuation_feature_id = 'college_name'
    AND fcfl.text_value IS NOT NULL
)

SELECT
  cs.raw_career_id
  , cs.civ_career || ' - ' || cs.civ_desc AS civ_desc
  , cs.event_ts
  , cs.college_name
  , cs.raw_desc
  , AVG(cs.prediction)
FROM college_score cs
GROUP BY
    cs.civ_career
    , cs.raw_career_id
    , cs.civ_desc
    , cs.event_ts
    , cs.college_name
    , cs.raw_desc
ORDER BY
    cs.college_name
    , cs.civ_career

Student Persistence Prediction by Financial Aid​

This query returns the persistence (continuation) score for each student taking into account the amount of Financial Aid the student has been given over the course of the current termn and the cumulative amount.

Output columns: Student ID Student First Name --Optional Student Last Name --Optional Event Timestamp Current Term Financial Aid Given Cumulative Financial Aid Given Prediction Score

WITH latest_continuation_score AS (
  SELECT DISTINCT
    dp.dim_person_id AS dim_person_id
    , dp.institution_person_id AS institution_person_id
    , dp.first_name
    , dp.last_name
    , fcs.event_ts AS event_ts
    , fcs.prediction AS prediction
  FROM data_mart.fact_continuation_scores fcs
  INNER JOIN data_mart.dim_person dp
  ON fcs.dim_person_id = dp.dim_person_id
  WHERE fcs.event_ts = (
                          SELECT MAX(fcs1.event_ts)
                          FROM data_mart.fact_continuation_scores fcs1
                          WHERE fcs1.dim_person_id = fcs.dim_person_id
                        )
  AND CURRENT_DATE BETWEEN dp.raw_eff_ts AND dp.raw_eff_ts_end
)

, final_continuation_scores AS (
    SELECT
      lcs.institution_person_id -- Student ID
      , lcs.first_name
      , lcs.last_name
      , lcs.event_ts
      , fcfl.float_value AS financial_aid
      , dcf.civ_continuation_feature_name
      , lcs.prediction AS student_persistence_prediction_score
    FROM latest_continuation_score lcs
    INNER JOIN data_mart.fact_continuation_feature_latest fcfl USING (dim_person_id)
    INNER JOIN data_mart.dim_continuation_feature dcf ON
        fcfl.dim_continuation_feature_id = dcf.dim_continuation_feature_id
    WHERE dcf.civ_continuation_feature_name ILIKE 'Financial Aid (Current Term)'
        OR dcf.civ_continuation_feature_name ILIKE 'Financial Aid (Cumulative)'
)

SELECT
    fcs.institution_person_id
--- You can display student name and lastname by uncommenting the next 2 columns here and on the group by
--       , fcs.first_name
--       , fcs.last_name
      , fcs.event_ts
      , TO_CHAR (
            SUM (
                CASE
                    WHEN civ_continuation_feature_name = 'Financial Aid (Current Term)' THEN financial_aid
                ELSE 0
                END
            ), 'FM$999,999,999,990D00'
        ) AS current_term_financial_aid
      , TO_CHAR (
            SUM (
                CASE
                    WHEN civ_continuation_feature_name = 'Financial Aid (Cumulative)' THEN financial_aid
                ELSE 0
                END
            ), 'FM$999,999,999,990D00'
        ) AS cumulative_financial_aid
      , TO_CHAR(student_persistence_prediction_score * 100, 'FM99D00%') AS prediction_score
FROM final_continuation_scores fcs
GROUP BY
    institution_person_id
    , event_ts
    , student_persistence_prediction_score
    --, first_name
    --, last_name
ORDER BY
    institution_person_id

Discovering Term Names and Continuation Patterns​

Step 1: Check Career Code Mapping​

First, identify how career codes map between tables (this is critical!):

Output Columns:

  • fact_career_code: Career code from fact_continuation table
  • dim_raw_career_id: Raw career ID from dim_career_term table
  • dim_civ_career_id: Civitas career ID from dim_career_term table
-- Shows the career code mapping between fact and dimension tables
SELECT DISTINCT
  fc.civ_career AS fact_career_code
  , dct.raw_career_id AS dim_raw_career_id
  , dct.civ_career_id AS dim_civ_career_id
FROM data_mart.fact_continuation fc
INNER JOIN data_mart.dim_career_term dct ON 
  DATE(fc.event_ts) BETWEEN DATE(dct.civ_career_term_start_dt) - 5
  AND DATE(dct.civ_career_term_start_dt) + 5
ORDER BY fc.civ_career;

Important: If this query returns 0 rows, your datamart may require joining on civ_career_id instead of raw_career_id.

Step 2: Find Available Term Names​

Run this query to see what term names exist in your datamart:

Output Columns:

  • term_name: Term description (e.g., 'Fall 2024', 'Spring 2025')
  • raw_career: Raw career identifier from source system
  • civ_career: Civitas career code
  • term_start_date: Start date of the term
-- See all available term names with their start dates
SELECT DISTINCT
  raw_desc AS term_name
  , raw_career_id AS raw_career
  , civ_career_id AS civ_career
  , civ_career_term_start_dt AS term_start_date
FROM data_mart.dim_career_term
WHERE raw_acad_year >= '2023'
ORDER BY civ_career_term_start_dt DESC, civ_career_id ASC;

Step 3: Discover Actual Continuation Patterns​

This query shows which term-to-term combinations actually exist in your data:

Output Columns:

  • from_term: Starting term name
  • to_term: Destination term name
  • civ_career: Career type code
  • student_count: Number of distinct students who continued
  • from_term_date: Date of the starting term evaluation
  • to_term_date: Date of the destination term
-- Shows all actual continuation patterns with student counts
-- NOTE: If Step 1 showed that career codes don't match, use civ_career_id instead of raw_career_id
SELECT
  t1.raw_desc AS from_term
  , (t2.raw_desc) AS to_term
  , fc.civ_career
  , COUNT(DISTINCT fc.dim_person_id) AS student_count
  , MIN(fc.event_ts) AS from_term_date
  , MIN(fc.next_continuation_event) AS to_term_date
FROM data_mart.fact_continuation fc
INNER JOIN data_mart.dim_career_term t1 ON
  DATE(fc.event_ts) BETWEEN DATE(t1.civ_career_term_start_dt) - 2
  AND DATE(t1.civ_career_term_start_dt) + 2
  AND fc.civ_career = t1.civ_career_id
INNER JOIN data_mart.dim_career_term t2 ON
  DATE(fc.next_continuation_event) BETWEEN DATE(t2.civ_career_term_start_dt) - 2
  AND DATE(t2.civ_career_term_start_dt) + 2
  AND fc.civ_career = t2.civ_career_id
WHERE fc.continued = 1
  AND fc.event_ts >= '2024-01-01' -- Adjust year as needed
GROUP BY t1.raw_desc, t2.raw_desc, fc.civ_career
ORDER BY from_term_date DESC, student_count DESC;

Step 4: Verify Specific Term Combination​

Before running the full query, verify your term combination exists:

Output Columns:

  • record_count: Total number of continuation records found
  • from_date: Earliest date from the starting term
  • to_date: Earliest date from the destination term
-- Replace 'Spring 2024' and 'Summer 2025' with your desired terms
SELECT
  COUNT(*) AS record_count
  , MIN(fc.event_ts) AS from_date
  , MIN(fc.next_continuation_event) AS to_date
FROM data_mart.fact_continuation fc

-- From term
INNER JOIN data_mart.dim_career_term t1 ON
  DATE(fc.event_ts) BETWEEN DATE(t1.civ_career_term_start_dt) - 2
  AND DATE(t1.civ_career_term_start_dt) + 2
  AND fc.civ_career = t1.civ_career_id
  AND t1.raw_desc = '<FROM_TERM>' -- e.g., 'Fall 2024'

-- To term
INNER JOIN data_mart.dim_career_term t2 ON
  DATE(fc.next_continuation_event) BETWEEN DATE(t2.civ_career_term_start_dt) - 2
  AND DATE(t2.civ_career_term_start_dt) + 2
  AND fc.civ_career = t2.civ_career_id
  AND t2.raw_desc = '<TO_TERM>' -- e.g., 'Spring 2025'
WHERE fc.continued = 1;

If this returns 0 rows, either:

  1. The term combination doesn't exist in your data (use Step 3 to find valid combinations), OR
  2. You need to use raw_career_id instead of civ_career_id (check Step 1)

Students Who Continued from One Term to Another​

This query lists individual students who persisted from one term to another, including students who graduated at the end of the starting term. It uses census-date enrollment from dim_enrollment and degree conferrals from fact_graduation to produce a complete student-level persistence roster.

Use Cases:

  • Track individual student continuation patterns between specific terms
  • Identify students for targeted communications about their continuation
  • Audit or verify student progression data for specific term combinations
  • Generate lists for retention analysis or intervention follow-up
  • Distinguish between students who re-enrolled vs. those who graduated

Prerequisites:

  • Replace <FROM_TERM> and <TO_TERM> with actual term names from your institution (e.g., 'Fall 2024', 'Spring 2025')
  • Verify the term names exist using the discovery queries in the previous section

Output Columns:

  • raw_person_id: Source system person identifier
  • civ_career_id: Career type code (U=Undergraduate, G=Graduate, etc.)
  • from_term: Starting term name
  • to_term: Destination term name
  • enrolled_in_to_term: 1 if the student is enrolled in the destination term, 0 otherwise
  • graduated_in_from_term: 1 if the student graduated (award conferred) at the end of the starting term, 0 otherwise
  • outcome: 'Persisted & Graduated', 'Persisted', 'Graduated', or 'Did Not Persist'
-- Students enrolled in the starting term as of census date
-- The census filter ensures the section has reached its census date (not future sections)
-- The drop filter excludes students who dropped a section before that section's census date
WITH from_term_students AS (
  SELECT DISTINCT
    de.raw_person_id
    , de.civ_career_id
  FROM data_mart.dim_enrollment de
  INNER JOIN data_mart.dim_section ds ON
    de.raw_section_id = ds.raw_section_id
  INNER JOIN data_mart.dim_career_term dct ON
    ds.raw_term_id = dct.raw_term_id
    AND de.civ_career_id = dct.civ_career_id
  WHERE de.civ_is_enrolled = 1
    AND dct.raw_desc = '<FROM_TERM>' -- e.g., 'Spring 2025'
    AND ds.civ_census_dt IS NOT NULL
    AND ds.civ_census_dt <= CURRENT_DATE -- Only sections that have reached census
    AND (de.raw_drop_dt IS NULL
      OR de.raw_drop_dt::date >= ds.civ_census_dt) -- Exclude drops before census
)

-- Students enrolled in the destination term
, to_term_students AS (
  SELECT DISTINCT
    de.raw_person_id
    , de.civ_career_id
  FROM data_mart.dim_enrollment de
  INNER JOIN data_mart.dim_section ds ON
    de.raw_section_id = ds.raw_section_id
  INNER JOIN data_mart.dim_career_term dct ON
    ds.raw_term_id = dct.raw_term_id
    AND de.civ_career_id = dct.civ_career_id
  WHERE de.civ_is_enrolled = 1
    AND dct.raw_desc = '<TO_TERM>' -- e.g., 'Fall 2025'
)

-- Students who graduated (award conferred) in the starting term
, from_term_graduates AS (
  SELECT DISTINCT
    dp.raw_person_id
    , dct.civ_career_id
  FROM data_mart.fact_graduation fg
  INNER JOIN data_mart.dim_person dp ON
    fg.dim_person_id = dp.dim_person_id
  INNER JOIN data_mart.dim_career_term dct ON
    fg.dim_career_term_id = dct.dim_career_term_id
  WHERE fg.civ_is_award_conferred = 1
    AND dct.raw_desc = '<FROM_TERM>' -- e.g., 'Spring 2025'
)

SELECT
  fts.raw_person_id
  , fts.civ_career_id
  , '<FROM_TERM>' AS from_term
  , '<TO_TERM>' AS to_term
  , CASE WHEN tts.raw_person_id IS NOT NULL THEN 1 ELSE 0 END AS enrolled_in_to_term
  , CASE WHEN ftg.raw_person_id IS NOT NULL THEN 1 ELSE 0 END AS graduated_in_from_term
  , CASE
      WHEN tts.raw_person_id IS NOT NULL AND ftg.raw_person_id IS NOT NULL
        THEN 'Persisted & Graduated'
      WHEN tts.raw_person_id IS NOT NULL
        THEN 'Persisted'
      WHEN ftg.raw_person_id IS NOT NULL
        THEN 'Graduated'
      ELSE 'Did Not Persist'
    END AS outcome
FROM from_term_students fts
LEFT JOIN to_term_students tts ON
  fts.raw_person_id = tts.raw_person_id
  AND fts.civ_career_id = tts.civ_career_id
LEFT JOIN from_term_graduates ftg ON
  fts.raw_person_id = ftg.raw_person_id
  AND fts.civ_career_id = ftg.civ_career_id
ORDER BY fts.civ_career_id, outcome, fts.raw_person_id;

Summary Count by Career Type​

This query calculates actual term-to-term persistence rates by comparing enrollment across two terms, grouped by career type (Undergraduate, Graduate, etc.). It counts students enrolled as of census date in the starting term, then checks which of those students either enrolled in the destination term or graduated at the end of the starting term.

Use Cases:

  • Generate summary statistics for institutional reporting
  • Compare persistence rates across different career types (Undergraduate vs Graduate)
  • Monitor overall retention trends between specific terms
  • Create executive dashboards showing term-to-term persistence rates

Prerequisites:

  • Replace <FROM_TERM> and <TO_TERM> with actual term names from your institution (e.g., 'Spring 2025' and 'Fall 2025')
  • Verify the term names exist using the discovery queries in the previous section

Output Columns:

  • from_term: Starting term name
  • to_term: Destination term name
  • civ_career_id: Career type code (U=Undergraduate, G=Graduate, etc.)
  • from_term_enrollment: Total students enrolled as of census date in the starting term
  • persisted_students: Students who enrolled in the destination term
  • graduated_students: Students who graduated (award conferred) at the end of the starting term
  • persisted_or_graduated: Combined count (enrolled in to_term OR graduated in from_term)
  • persistence_rate: Percentage including both persisted and graduated students

Note: This query uses dim_enrollment for census-based enrollment counts and fact_graduation for degree conferrals. A student who both graduated and re-enrolled is counted once. The census date filter (civ_census_dt <= CURRENT_DATE) ensures only sections that have reached census are included. The drop date filter (raw_drop_dt IS NULL OR raw_drop_dt >= civ_census_dt) excludes students who dropped a section before its census date, so the denominator only includes students who were actively enrolled as of census.

-- Students enrolled in the starting term as of census date
-- The census filter ensures the section has reached its census date (not future sections)
-- The drop filter excludes students who dropped a section before that section's census date
WITH from_term_students AS (
  SELECT DISTINCT
    de.raw_person_id
    , de.civ_career_id
  FROM data_mart.dim_enrollment de
  INNER JOIN data_mart.dim_section ds ON
    de.raw_section_id = ds.raw_section_id
  INNER JOIN data_mart.dim_career_term dct ON
    ds.raw_term_id = dct.raw_term_id
    AND de.civ_career_id = dct.civ_career_id
  WHERE de.civ_is_enrolled = 1
    AND dct.raw_desc = '<FROM_TERM>' -- e.g., 'Spring 2025'
    AND ds.civ_census_dt IS NOT NULL
    AND ds.civ_census_dt <= CURRENT_DATE -- Only sections that have reached census
    AND (de.raw_drop_dt IS NULL
      OR de.raw_drop_dt::date >= ds.civ_census_dt) -- Exclude drops before census
)

-- Students enrolled in the destination term
, to_term_students AS (
  SELECT DISTINCT
    de.raw_person_id
    , de.civ_career_id
  FROM data_mart.dim_enrollment de
  INNER JOIN data_mart.dim_section ds ON
    de.raw_section_id = ds.raw_section_id
  INNER JOIN data_mart.dim_career_term dct ON
    ds.raw_term_id = dct.raw_term_id
    AND de.civ_career_id = dct.civ_career_id
  WHERE de.civ_is_enrolled = 1
    AND dct.raw_desc = '<TO_TERM>' -- e.g., 'Fall 2025'
)

-- Students who graduated (award conferred) in the starting term
, from_term_graduates AS (
  SELECT DISTINCT
    dp.raw_person_id
    , dct.civ_career_id
  FROM data_mart.fact_graduation fg
  INNER JOIN data_mart.dim_person dp ON
    fg.dim_person_id = dp.dim_person_id
  INNER JOIN data_mart.dim_career_term dct ON
    fg.dim_career_term_id = dct.dim_career_term_id
  WHERE fg.civ_is_award_conferred = 1
    AND dct.raw_desc = '<FROM_TERM>' -- e.g., 'Spring 2025'
)

SELECT
  '<FROM_TERM>' AS from_term
  , '<TO_TERM>' AS to_term
  , fts.civ_career_id
  , COUNT(DISTINCT fts.raw_person_id) AS from_term_enrollment
  , COUNT(DISTINCT tts.raw_person_id) AS persisted_students
  , COUNT(DISTINCT ftg.raw_person_id) AS graduated_students
  , COUNT(DISTINCT CASE
      WHEN tts.raw_person_id IS NOT NULL
        OR ftg.raw_person_id IS NOT NULL
      THEN fts.raw_person_id
    END) AS persisted_or_graduated
  , ROUND(
      (COUNT(DISTINCT CASE
        WHEN tts.raw_person_id IS NOT NULL
          OR ftg.raw_person_id IS NOT NULL
        THEN fts.raw_person_id
      END)::DECIMAL
        / NULLIF(COUNT(DISTINCT fts.raw_person_id), 0)) * 100,
      2
    ) AS persistence_rate
FROM from_term_students fts
LEFT JOIN to_term_students tts ON
  fts.raw_person_id = tts.raw_person_id
  AND fts.civ_career_id = tts.civ_career_id
LEFT JOIN from_term_graduates ftg ON
  fts.raw_person_id = ftg.raw_person_id
  AND fts.civ_career_id = ftg.civ_career_id
GROUP BY fts.civ_career_id
ORDER BY fts.civ_career_id ASC, persisted_or_graduated DESC;

All Continuation Patterns from a Specific Term​

This query shows all possible continuation destinations from a single starting term, providing a comprehensive view of where students went after completing a specific term. Unlike the previous queries that require both a start and end term, this query discovers all destination terms automatically.

Use Cases:

  • Explore all possible continuation paths from a starting term without knowing the destinations
  • Identify unusual or unexpected continuation patterns (e.g., students skipping terms)
  • Understand the distribution of student continuation across multiple subsequent terms
  • Discover seasonal patterns (e.g., Fall → Spring vs Fall → Summer)

Prerequisites:

  • Replace <FROM_TERM> with the starting term name you want to analyze (e.g., 'Fall 2024')
  • No need to specify a destination term - the query finds all destinations

Output Columns:

  • from_term: Starting term name (will be the same for all rows)
  • to_term: Destination term name (shows all terms students continued to)
  • civ_career: Career type code
  • student_count: Number of students who continued to each destination term (by dim_person_id)
  • raw_person_count: Number of students who continued to each destination term (by raw_person_id)

Note: Results are ordered by student count (descending) to show the most common continuation patterns first. The query uses a LEFT JOIN to capture all continuation patterns, even if they're uncommon.

-- Shows all destinations from a specific starting term
SELECT
  t1.raw_desc AS from_term
  , t2.raw_desc AS to_term
  , fc.civ_career
  , COUNT(DISTINCT fc.dim_person_id) AS student_count
  , COUNT(DISTINCT dp.raw_person_id) AS raw_person_count
FROM data_mart.fact_continuation fc

-- Person dimension for raw_person_id counts
INNER JOIN data_mart.dim_person dp ON
  fc.dim_person_id = dp.dim_person_id

-- From term
INNER JOIN data_mart.dim_career_term t1 ON
  DATE(fc.event_ts) BETWEEN DATE(t1.civ_career_term_start_dt) - 2
  AND DATE(t1.civ_career_term_start_dt) + 2
  AND fc.civ_career = t1.civ_career_id
  AND t1.raw_desc = '<FROM_TERM>' -- e.g., 'Fall 2024'

-- All possible destinations
LEFT JOIN data_mart.dim_career_term t2 ON
  DATE(fc.next_continuation_event) BETWEEN DATE(t2.civ_career_term_start_dt) - 2
  AND DATE(t2.civ_career_term_start_dt) + 2
  AND fc.civ_career = t2.civ_career_id
WHERE fc.continued = 1
GROUP BY t1.raw_desc, t2.raw_desc, fc.civ_career
ORDER BY student_count DESC;