Academic Queries

First-Generation College Student Academic Performance Analysis

This query compares first-year academic performance and engagement patterns between first-generation and continuing-generation college students by analyzing students with complete academic records during their initial terms. It examines performance gaps, engagement levels, and risk indicators to support targeted intervention programs and equity initiatives aimed at closing achievement gaps and improving first-generation student outcomes. Target Population: First-year students with complete academic records (start term, grades, and section enrollment data)

Key Metrics:

• Generation Status: Classification as First Generation, Continuing Generation, or N/A (no generation data)
• Academic Performance: First-year GPA, withdrawal counts, and courses attempted
• Engagement Metrics: Days active online and total LMS activities
• Continuation Rates: Percentage of terms successfully continued
• Risk Indicators: Low GPA (below 2.0), high withdrawal (2+ withdrawals), and low engagement (under 30 days online)
• Student Distribution: Count per generation status against total first-year population

Note: Analysis focuses on students during their first year only (terms within start_term_sequence_no + 2). Population represents students with complete academic records, not total active enrollment. The N/A category includes students without generation data.

WITH first_year_performance AS (
 SELECT
   dp.dim_person_id
   , dp.civ_first_to_attend_college
   , sst.civ_start_term_sequence_no
   , AVG(fg.civ_gpa_points) AS first_year_gpa
   , COUNT(CASE WHEN fg.civ_is_withdraw = 1 THEN 1 END) AS withdrawals
   , COUNT(DISTINCT ds.dim_section_id) AS courses_attempted
   , COUNT(DISTINCT fwa.event_ts::date) AS days_active_online
   , COUNT(fwa.dim_person_id) AS total_lms_activities
 FROM data_mart.dim_person dp
 INNER JOIN data_mart.fact_student_start_term sst ON
   dp.dim_person_id = sst.dim_person_id
 INNER JOIN data_mart.fact_grade fg ON
   dp.dim_person_id = fg.dim_person_id
 INNER JOIN data_mart.dim_section ds ON
   fg.dim_section_id = ds.dim_section_id
 INNER JOIN data_mart.dim_career_term dct ON
   ds.civ_term_id = dct.civ_term_id
 LEFT JOIN data_mart.fact_web_activity fwa ON
   dp.dim_person_id = fwa.dim_person_id
   AND fwa.dim_section_id = ds.dim_section_id
 WHERE dct.civ_career_term_id <= sst.civ_start_term_sequence_no + 2
   AND dp.raw_eff_ts_end >= '9999-01-01'
 GROUP BY dp.dim_person_id, dp.civ_first_to_attend_college, sst.civ_start_term_sequence_no
)
, continuation_rates AS (
 SELECT
   dp.dim_person_id
   , dp.civ_first_to_attend_college
   , COUNT(CASE WHEN fc.continued = 1 THEN 1 END) AS terms_continued
   , COUNT(*) AS continuation_opportunities
 FROM data_mart.dim_person dp
 LEFT JOIN data_mart.fact_continuation fc ON
   dp.dim_person_id = fc.dim_person_id
 WHERE dp.raw_eff_ts_end >= '9999-01-01'
 GROUP BY dp.dim_person_id, dp.civ_first_to_attend_college
)
SELECT
 CASE
   WHEN fyp.civ_first_to_attend_college = 1 THEN 'First Generation'
   WHEN fyp.civ_first_to_attend_college = 0 THEN 'Continuing Generation'
   ELSE 'N/A'
 END AS generation_status
 , COUNT(*) AS student_count
 , SUM(COUNT(*)) OVER() AS total_first_year_students  Sum of all student_counts
 , CAST(ROUND(AVG(fyp.first_year_gpa), 2) AS DECIMAL(10,2)) AS avg_first_year_gpa
 , CAST(ROUND(AVG(fyp.withdrawals), 2) AS DECIMAL(10,2)) AS avg_withdrawals
 , CAST(ROUND(AVG(fyp.courses_attempted), 2) AS DECIMAL(10,2)) AS avg_courses_attempted
 , CAST(ROUND(AVG(fyp.days_active_online), 2) AS DECIMAL(10,2)) AS avg_days_online_active
 , CAST(ROUND(AVG(fyp.total_lms_activities), 2) AS DECIMAL(10,2)) AS avg_lms_activities
 , CAST(
     ROUND(
       AVG(cr.terms_continued * 100.0 / NULLIF(cr.continuation_opportunities, 0)), 2
     ) AS DECIMAL(10,2)
   ) AS continuation_rate_pct
 , CAST(
     ROUND(
       COUNT(CASE WHEN fyp.first_year_gpa < 2.0 THEN 1 END) * 100.0 / COUNT(*), 2
     ) AS DECIMAL(10,2)
   ) AS low_gpa_risk_pct
 , CAST(
     ROUND(
       COUNT(CASE WHEN fyp.withdrawals >= 2 THEN 1 END) * 100.0 / COUNT(*), 2
     ) AS DECIMAL(10,2)
   ) AS high_withdrawal_risk_pct
 , CAST(
     ROUND(
       COUNT(CASE WHEN fyp.days_active_online < 30 THEN 1 END) * 100.0 / COUNT(*), 2
     ) AS DECIMAL(10,2)
   ) AS low_engagement_risk_pct
FROM first_year_performance fyp
LEFT JOIN continuation_rates cr ON
 fyp.dim_person_id = cr.dim_person_id
GROUP BY
 CASE
   WHEN fyp.civ_first_to_attend_college = 1 THEN 'First Generation'
   WHEN fyp.civ_first_to_attend_college = 0 THEN 'Continuing Generation'
   ELSE 'N/A'
 END
ORDER BY generation_status

Developmental Education Effectiveness Analysis

Purpose: Analyze success rates and progression patterns through developmental education programs to assess program effectiveness and identify improvement opportunities.

Key Metrics:

• First Attempt Success Rate: Percentage of students who pass on their first enrollment attempt
• Overall Pass Rate: Total success rate across all attempts for each program
• Progression Rate: Percentage of successful students who advance to college-level courses in the same subject (NULL for transition programs without college equivalents)
• Average College GPA: Performance of progressed students in college-level coursework
• Average Days to Progression: Time between dev ed completion and college-level enrollment

Usage Options:

Default: Shows only viable programs (10+ students, active since 2015, <90% withdrawals) All Programs: Comment out the WHERE clause on viable_dev_programs CTE to include discontinued/low-enrollment programs Recent Only: Modify latest_enrollment >= '2020-01-01' for more recent analysis Custom Threshold: Adjust unique_students >= 50 for larger program analysis

Note: Transition programs (ETP, preparatory courses) show NULL progression rates as they feed into other academic subjects rather than same-subject college courses.

WITH program_classification AS (
 SELECT DISTINCT
   ds.raw_subject
   , CASE
       WHEN EXISTS (
         SELECT 1 FROM data_mart.dim_section college_ds
         WHERE college_ds.raw_subject = ds.raw_subject
         AND college_ds.civ_is_deved = 0
       ) THEN 'ACADEMIC_PROGRAM'
       ELSE 'TRANSITION_PROGRAM'
     END AS program_type
 FROM data_mart.dim_section ds
 WHERE ds.civ_is_deved = 1
)
, program_viability_check AS (
 SELECT
   ds.raw_subject
   , COUNT(*) AS total_enrollments
   , COUNT(DISTINCT fg.dim_person_id) AS unique_students
   , MAX(fg.event_ts) AS latest_enrollment
   , SUM(CASE WHEN fg.civ_display_grade IN ('W', 'WU', 'WC') THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS withdrawal_rate
 FROM data_mart.dim_section ds
 JOIN data_mart.fact_grade fg ON ds.dim_section_id = fg.dim_section_id
 WHERE ds.civ_is_deved = 1
 GROUP BY ds.raw_subject
)
, viable_dev_programs AS (
 SELECT raw_subject
 FROM program_viability_check
 WHERE unique_students >= 10
   AND latest_enrollment >= '2015-01-01'
   AND withdrawal_rate < 90
)
, dev_ed_courses AS (
 SELECT DISTINCT
   ds.dim_section_id
   , ds.raw_subject
   , ds.raw_catalog_nbr
   , ds.raw_title
   , ds.civ_term_id
   , ds.civ_is_deved
 FROM data_mart.dim_section ds
 JOIN viable_dev_programs vdp ON ds.raw_subject = vdp.raw_subject
 WHERE ds.civ_is_deved = 1
)
, dev_ed_performance AS (
 SELECT
   fg.dim_person_id
   , dec.raw_subject
   , fg.civ_is_favorable
   , fg.civ_display_grade
   , fg.event_ts
   , ROW_NUMBER() OVER (
       PARTITION BY fg.dim_person_id, dec.raw_subject
       ORDER BY fg.event_ts
     ) AS attempt_number
 FROM data_mart.fact_grade fg
 JOIN dev_ed_courses dec ON
   fg.dim_section_id = dec.dim_section_id
)
, college_level_success AS (
 SELECT
   fg.dim_person_id
   , ds.raw_subject
   , MIN(fg.event_ts) AS first_college_level_date
   , AVG(fg.civ_gpa_points) AS college_level_gpa
 FROM data_mart.fact_grade fg
 JOIN data_mart.dim_section ds ON
   fg.dim_section_id = ds.dim_section_id
 WHERE ds.civ_is_deved = 0
   AND fg.civ_is_favorable = 1
 GROUP BY fg.dim_person_id, ds.raw_subject
)
SELECT
 dep.raw_subject
 , COUNT(DISTINCT dep.dim_person_id) AS students_enrolled
 , CAST(
     ROUND(
       COUNT(
         CASE
           WHEN dep.civ_is_favorable = 1 AND dep.attempt_number = 1
           THEN 1
         END
       ) * 100.0 /
       NULLIF(COUNT(CASE WHEN dep.attempt_number = 1 THEN 1 END), 0)
       , 2
     ) AS DECIMAL(10, 2)
   ) AS first_attempt_success_rate
 , CAST(
     ROUND(
       COUNT(CASE WHEN dep.civ_is_favorable = 1 THEN 1 END) * 100.0
       / COUNT(*)
       , 2
     ) AS DECIMAL(10, 2)
   ) AS overall_pass_rate
 , COUNT(DISTINCT cls.dim_person_id) AS progressed_to_college_level
 , CASE
     WHEN pc.program_type = 'TRANSITION_PROGRAM' THEN NULL  N/A for transition programs
     ELSE CAST(
       ROUND(
         COUNT(DISTINCT cls.dim_person_id) * 100.0 /
         NULLIF(
           COUNT(DISTINCT
             CASE WHEN dep.civ_is_favorable = 1 THEN dep.dim_person_id END
           ), 0
         )
         , 2
       ) AS DECIMAL(10, 2)
     )
   END AS progression_rate
 , CASE
     WHEN COUNT(DISTINCT cls.dim_person_id) = 0 THEN NULL
     WHEN AVG(cls.college_level_gpa) IS NULL THEN NULL
     ELSE CAST(ROUND(AVG(cls.college_level_gpa), 2) AS DECIMAL(10, 2))
   END AS avg_college_level_gpa
 , CASE
     WHEN COUNT(DISTINCT cls.dim_person_id) = 0 THEN NULL
     ELSE CAST(
       ROUND(
         AVG(
           EXTRACT(EPOCH FROM (cls.first_college_level_date - dep.event_ts)) /
           86400
         )
         , 2
       ) AS DECIMAL(10, 2)
     )
   END AS avg_days_to_progression
FROM dev_ed_performance dep
LEFT JOIN college_level_success cls ON
 dep.dim_person_id = cls.dim_person_id
 AND dep.raw_subject = cls.raw_subject
 AND cls.first_college_level_date > dep.event_ts
 AND dep.civ_is_favorable = 1
JOIN program_classification pc ON dep.raw_subject = pc.raw_subject
GROUP BY dep.raw_subject, pc.program_type
ORDER BY
 CASE WHEN pc.program_type = 'ACADEMIC_PROGRAM' THEN 1 ELSE 2 END,
 progression_rate DESC NULLS LAST;

Course Modality Performance Comparison

Purpose: Compare student success across different instruction modes

Key Metrics:

• Success rates
• Grade distributions by modality and demographics

WITH modality_performance AS (
   SELECT
       ds.civ_instruction_mode
       , fg.dim_person_id
       , fg.civ_is_favorable
       , fg.civ_gpa_points
       , fg.civ_is_withdraw
       , dp.civ_first_to_attend_college
       , dp.civ_federal_financial_aid
       , dp.civ_is_veteran
       -- Calculate age at time of course
       ,EXTRACT(YEAR FROM fg.event_ts) - EXTRACT(YEAR FROM dp.raw_birth_dt) AS age_at_course
   FROM data_mart.fact_grade fg
   JOIN data_mart.dim_section ds ON
       fg.dim_section_id = ds.dim_section_id
   JOIN data_mart.dim_person dp ON
       fg.dim_person_id = dp.dim_person_id
   JOIN data_mart.dim_career_term dct ON
       ds.civ_term_id = dct.civ_term_id
   WHERE
       ds.civ_instruction_mode IN ('Online', 'Offline', 'Blended')
       AND dct.raw_acad_year >= EXTRACT(YEAR FROM CURRENT_DATE) - 2 -- Last 2 years
       AND dp.raw_eff_ts_end >= CURRENT_DATE
)
SELECT
   civ_instruction_mode
   , COUNT(*) AS total_enrollments
   , COUNT(DISTINCT dim_person_id) AS unique_students
   , ROUND(COUNT(CASE
                   WHEN civ_is_favorable = 1
                    THEN 1
                 END
                 ) * 100.0 / COUNT(*), 2) AS success_rate
   , ROUND(COUNT(CASE
                   WHEN civ_is_withdraw = 1
                    THEN 1
                 END
                ) * 100.0 / COUNT(*), 2) AS withdrawal_rate
   , ROUND(AVG(civ_gpa_points), 2) AS avg_gpa_points
   -- Demographics breakdown
   , ROUND(COUNT(CASE
                   WHEN civ_first_to_attend_college = 1
                    THEN 1
                 END
                 ) * 100.0 / COUNT(*), 2) AS first_gen_pct
   , ROUND(COUNT(CASE
                   WHEN civ_federal_financial_aid = 1
                    THEN 1
                 END
                 ) * 100.0 / COUNT(*), 2) AS pell_pct
   , ROUND(COUNT(CASE
                   WHEN civ_is_veteran = 1
                    THEN 1
                 END
                 ) * 100.0 / COUNT(*), 2) AS veteran_pct
   , ROUND(AVG(age_at_course), 1) AS avg_student_age
   -- Success rates by demographics
   , ROUND(AVG(CASE
                 WHEN civ_first_to_attend_college = 1 AND civ_is_favorable = 1
                   THEN 1.0
                 WHEN civ_first_to_attend_college = 1
                   THEN 0.0
               END
               ), 3) AS first_gen_success_rate
   , ROUND(AVG(CASE
                 WHEN civ_federal_financial_aid = 1 AND civ_is_favorable = 1
                   THEN 1.0
                 WHEN civ_federal_financial_aid = 1
                   THEN 0.0
               END
               ), 3) AS pell_success_rate
   , ROUND(AVG(CASE
                 WHEN age_at_course >= 25 AND civ_is_favorable = 1
                   THEN 1.0
                 WHEN age_at_course >= 25
                   THEN 0.0
               END
               ), 3) AS adult_learner_success_rate
FROM modality_performance
GROUP BY civ_instruction_mode
ORDER BY success_rate DESC;

Cohort Progression Milestone Tracking

Purpose: Track entering cohorts through major academic milestones Use Case: Retention analysis, bottleneck identification, cohort comparison Business Value: Identify progression barriers and optimize student pathways

Metrics: Progression rates, time to milestones, completion patterns Expected Output: Cohort milestone analysis with progression timing insights Recommended Action: Address progression bottlenecks and optimize degree pathways

WITH cohort_students AS (
   SELECT
       fsst.dim_person_id,
       fsst.civ_start_term_id,
       EXTRACT(YEAR FROM dct.civ_career_term_start_dt) AS entry_year,
       dct.civ_term_id AS entry_season,
       dct.civ_career_term_start_dt
   FROM data_mart.fact_student_start_term fsst
   JOIN data_mart.dim_career_term dct ON fsst.civ_start_term_id = dct.civ_term_id
   WHERE dct.raw_career_id = 'UG' -- Undergraduate
   AND EXTRACT(YEAR FROM dct.civ_career_term_start_dt) >= EXTRACT(YEAR FROM CURRENT_DATE) - 6 -- Last 6 years
),
credits_running_total AS (
    SELECT
        cs.dim_person_id,
        cs.entry_year,
        cs.entry_season,
        fg.event_ts,
        (CASE WHEN fg.civ_is_earned = 1 THEN ds.raw_min_credits ELSE 0 END) AS credits_earned,
        SUM(CASE WHEN fg.civ_is_earned = 1 THEN ds.raw_min_credits ELSE 0 END)
            OVER (PARTITION BY cs.dim_person_id ORDER BY fg.event_ts
                  ROWS UNBOUNDED PRECEDING) AS running_cumulative_credits
    FROM cohort_students cs
    JOIN data_mart.fact_grade fg ON cs.dim_person_id = fg.dim_person_id
    JOIN data_mart.dim_section ds ON fg.dim_section_id = ds.dim_section_id
),
credit_milestones AS (
    SELECT
        crt.dim_person_id,
        crt.entry_year,
        crt.entry_season,
        SUM(crt.credits_earned) AS cumulative_credits,
        MIN(CASE WHEN crt.running_cumulative_credits >= 30 THEN crt.event_ts END) AS milestone_30_credits,
        MIN(CASE WHEN crt.running_cumulative_credits >= 60 THEN crt.event_ts END) AS milestone_60_credits,
        MIN(CASE WHEN crt.running_cumulative_credits >= 90 THEN crt.event_ts END) AS milestone_90_credits
    FROM credits_running_total crt
    GROUP BY crt.dim_person_id, crt.entry_year, crt.entry_season
),
graduation_milestones AS (
    SELECT
        cs.dim_person_id,
        MIN(fgr.event_ts) AS graduation_date
    FROM cohort_students cs
    JOIN data_mart.fact_graduation fgr ON cs.dim_person_id = fgr.dim_person_id
    WHERE fgr.civ_is_award_conferred = 1
    GROUP BY cs.dim_person_id
)
SELECT
    cm.entry_year,
    cm.entry_season,
    COUNT(DISTINCT cm.dim_person_id) AS cohort_size,
    -- Milestone achievement rates
    ROUND(COUNT(CASE WHEN cm.milestone_30_credits IS NOT NULL THEN 1 END) * 100.0 / COUNT(DISTINCT cm.dim_person_id), 2) AS pct_reached_30_credits,
    ROUND(COUNT(CASE WHEN cm.milestone_60_credits IS NOT NULL THEN 1 END) * 100.0 / COUNT(DISTINCT cm.dim_person_id), 2) AS pct_reached_60_credits,
    ROUND(COUNT(CASE WHEN cm.milestone_90_credits IS NOT NULL THEN 1 END) * 100.0 / COUNT(DISTINCT cm.dim_person_id), 2) AS pct_reached_90_credits,
    ROUND(COUNT(CASE WHEN gm.graduation_date IS NOT NULL THEN 1 END) * 100.0 / COUNT(DISTINCT cm.dim_person_id), 2) AS graduation_rate,
    -- Time to milestones (in years)
    ROUND(AVG(EXTRACT(EPOCH FROM (cm.milestone_30_credits - cs.civ_career_term_start_dt)) / 86400 / 365.25), 2) AS avg_years_to_30_credits,
    ROUND(AVG(EXTRACT(EPOCH FROM (cm.milestone_60_credits - cs.civ_career_term_start_dt)) / 86400 / 365.25), 2) AS avg_years_to_60_credits,
    ROUND(AVG(EXTRACT(EPOCH FROM (gm.graduation_date - cs.civ_career_term_start_dt)) / 86400 / 365.25), 2) AS avg_years_to_graduation,
    -- Student characteristics
    ROUND(COUNT(CASE WHEN dp.civ_first_to_attend_college = 1 THEN 1 END) * 100.0 / COUNT(DISTINCT cm.dim_person_id), 2) AS first_gen_pct,
    ROUND(COUNT(CASE WHEN dp.civ_federal_financial_aid = 1 THEN 1 END) * 100.0 / COUNT(DISTINCT cm.dim_person_id), 2) AS pell_recipient_pct
FROM credit_milestones cm
JOIN cohort_students cs ON cm.dim_person_id = cs.dim_person_id
LEFT JOIN graduation_milestones gm ON cm.dim_person_id = gm.dim_person_id
JOIN data_mart.dim_person dp ON cm.dim_person_id = dp.dim_person_id
JOIN data_mart.dim_career_term dct ON cs.civ_start_term_id = dct.civ_term_id
GROUP BY cm.entry_year, cm.entry_season
ORDER BY cm.entry_year DESC, cm.entry_season;

Academic Standing Recovery Patterns

Purpose: Analyze students who successfully recover from academic bad standing Use Case: Intervention strategy assessment Business Value: Design effective bad standing recovery programs and interventions

Metrics: Recovery rates, time patterns Expected Output: Recovery success factors and program effectiveness analysis Recommended Action: Implement proven recovery interventions and support strategies

WITH bad_standing_periods AS (
   SELECT
       fs.dim_person_id,
       fs.event_ts AS bad_standing_start,
       fs.civ_is_good_standing,
       LEAD(fs.event_ts) OVER (PARTITION BY fs.dim_person_id ORDER BY fs.event_ts) AS next_standing_date,
       LEAD(fs.civ_is_good_standing) OVER (PARTITION BY fs.dim_person_id ORDER BY fs.event_ts) AS next_good_standing
   FROM data_mart.fact_standing fs
   WHERE fs.civ_is_good_standing = 0
),
recovery_outcomes AS (
    SELECT
        bsp.dim_person_id,
        bsp.bad_standing_start,
        bsp.next_standing_date,
        CASE WHEN bsp.next_good_standing = 1 THEN 1 ELSE 0 END AS recovered,
        EXTRACT(EPOCH FROM (bsp.next_standing_date - bsp.bad_standing_start)) / 86400 AS days_to_resolution
    FROM bad_standing_periods bsp
    WHERE bsp.next_standing_date IS NOT NULL
),
academic_performance_during AS (
    SELECT
        ro.dim_person_id,
        ro.bad_standing_start,
        AVG(fg.civ_gpa_points) AS gpa_during_probation,
        COUNT(CASE WHEN fg.civ_is_withdraw = 1 THEN 1 END) AS withdrawals_during
    FROM recovery_outcomes ro
    JOIN data_mart.fact_grade fg ON ro.dim_person_id = fg.dim_person_id
    WHERE fg.event_ts BETWEEN ro.bad_standing_start AND ro.next_standing_date
    GROUP BY ro.dim_person_id, ro.bad_standing_start
)
SELECT
    CASE WHEN ro.recovered = 1 THEN 'Successfully Recovered' ELSE 'Did Not Recover' END AS recovery_status,
    COUNT(*) AS student_count,
    ROUND(AVG(ro.days_to_resolution), 1) AS avg_days_to_resolution,
    ROUND(AVG(apd.gpa_during_probation), 3) AS avg_gpa_during_probation,
    ROUND(AVG(apd.withdrawals_during), 2) AS avg_withdrawals_during,
    -- Demographics
    ROUND(COUNT(CASE WHEN dp.civ_first_to_attend_college = 1 THEN 1 END) * 100.0 / COUNT(*), 2) AS first_gen_pct,
    ROUND(COUNT(CASE WHEN dp.civ_federal_financial_aid = 1 THEN 1 END) * 100.0 / COUNT(*), 2) AS pell_recipient_pct,
    -- Success factors analysis
    ROUND(AVG(CASE WHEN apd.gpa_during_probation >= 2.5 THEN 1.0 ELSE 0.0 END), 3) AS strong_academic_performance_rate
FROM recovery_outcomes ro
LEFT JOIN academic_performance_during apd ON ro.dim_person_id = apd.dim_person_id
    AND ro.bad_standing_start = apd.bad_standing_start
JOIN data_mart.dim_person dp ON ro.dim_person_id = dp.dim_person_id
GROUP BY CASE WHEN ro.recovered = 1 THEN 'Successfully Recovered' ELSE 'Did Not Recover' END
ORDER BY recovery_status DESC;

Academic Outcomes

Purpose: Summarize student outcomes Use Case: Resource allocation, intervention timing, service ideas Business Value: Find averages on student success

Metrics: Outcome correlations Expected Output: Student outcome analysis Recommended Action: Suggest resource creation and expand access accordingly

WITH academic_outcomes AS (
   SELECT
       dp.dim_person_id,
       AVG(fpg.raw_gpa) AS current_gpa,
       MAX(fcs.prediction) AS latest_continuation_score,
       COUNT(CASE WHEN fg.civ_is_withdraw = 1 THEN 1 END) AS total_withdrawals
   FROM data_mart.dim_person dp
   LEFT JOIN data_mart.fact_provided_gpa fpg ON dp.dim_person_id = fpg.dim_person_id
   LEFT JOIN data_mart.fact_continuation_scores fcs ON dp.dim_person_id = fcs.dim_person_id
   LEFT JOIN data_mart.fact_grade fg ON dp.dim_person_id = fg.dim_person_id
   WHERE dp.raw_eff_ts_end >= current_date
   GROUP BY dp.dim_person_id
)
SELECT
    COUNT(*) AS student_count,
    ROUND(AVG(ao.current_gpa), 3) AS avg_current_gpa,
    ROUND(AVG(ao.latest_continuation_score), 3) AS avg_continuation_score,
    ROUND(AVG(ao.total_withdrawals), 2) AS avg_withdrawals,
    -- Risk analysis
    ROUND(COUNT(CASE WHEN ao.current_gpa < 2.0 THEN 1 END) * 100.0 / COUNT(*), 2) AS low_gpa_pct,
    ROUND(COUNT(CASE WHEN ao.latest_continuation_score < 0.5 THEN 1 END) * 100.0 / COUNT(*), 2) AS high_risk_pct
FROM data_mart.dim_person dp
LEFT JOIN academic_outcomes ao ON dp.dim_person_id = ao.dim_person_id
WHERE dp.raw_eff_ts_end > current_date
AND ao.current_gpa IS NOT NULL
ORDER BY avg_continuation_score DESC;

FTPT (Full-Time Part-Time) Risk Analysis

This query identifies students with FTPT (Full-Time Part-Time) risk patterns - currently enrolled students who are either not enrolled in the next two terms OR enrolled full-time currently but only part-time in the next term. Looking at the next two terms accounts for students who naturally skip summer to enroll in the next fall term, or decide to skip one term for other reasons but are still registered to return.

Key Risk Indicators:

• Students enrolled full-time in current term but part-time (or not at all) in future terms
• Students currently enrolled but with no future registrations in the next two terms
• Credit-based thresholds: ≥12 credits (undergraduate), ≥9 credits (graduate) for full-time status

Output includes persistence scores and detailed enrollment patterns to support early intervention strategies.

WITH current_terms AS (
  SELECT DISTINCT
civ_career_id
, civ_term_id
, civ_career_term_id
  FROM data_mart.dim_career_term
  WHERE is_active = 1
    AND civ_career_id IN ('U', 'G') -- Only undergraduate and graduate careers
)

, future_terms AS (
  SELECT
    civ_career_id
    , civ_term_id
    , civ_career_term_id
    , ROW_NUMBER() OVER (
        PARTITION BY civ_career_id
        ORDER BY civ_career_term_id)
    AS future_term_rank
  FROM (
    SELECT DISTINCT
      dct.civ_career_id
      , dct.civ_term_id
      , dct.civ_career_term_id
    FROM data_mart.dim_career_term dct
    INNER JOIN current_terms ct ON dct.civ_career_id = ct.civ_career_id
    WHERE dct.civ_career_term_id > ct.civ_career_term_id
  ) distinct_terms
)

, terms AS (
  SELECT DISTINCT
    ct.civ_career_id
    , ct.civ_term_id AS current_term_id
    , ft1.civ_term_id AS next_term_1
    , ft2.civ_term_id AS next_term_2
  FROM current_terms ct
  LEFT JOIN
    future_terms ft1
  ON ct.civ_career_id = ft1.civ_career_id AND ft1.future_term_rank = 1
  LEFT JOIN
    future_terms ft2
  ON ct.civ_career_id = ft2.civ_career_id AND ft2.future_term_rank = 2
)

, enrollment_data AS (
  SELECT
    de.raw_person_id
    , de.civ_career_id
    , ds.civ_term_id
    , SUM(de.raw_credits) AS total_credits
  FROM data_mart.dim_enrollment de
  INNER JOIN data_mart.dim_section ds ON de.raw_section_id = ds.raw_section_id
  WHERE de.civ_is_enrolled = 1
    AND de.raw_career_id <> 'Not Available'
    AND de.raw_credits IS NOT NULL
  GROUP BY de.raw_person_id, de.civ_career_id, ds.civ_term_id
)

, persistence_scores AS (
  SELECT
    dp.raw_person_id
    , fcs.civ_career AS civ_career_id
    , fcs.prediction AS persistence_score
  FROM data_mart.dim_person dp
  INNER JOIN (
    SELECT
      fcs.dim_person_id
      , fcs.civ_career
      , fcs.prediction
      , ROW_NUMBER() OVER (
        PARTITION BY fcs.dim_person_id, fcs.civ_career
        ORDER BY
          fcs.event_ts DESC
          , fcs.iteration_ts DESC
          , fcs.model DESC
          , fcs.prediction DESC
      ) AS rn
    FROM data_mart.fact_continuation_scores fcs
    INNER JOIN current_terms ct ON fcs.civ_career = ct.civ_career_id
      AND fcs.civ_term_id = ct.civ_term_id
  ) fcs ON dp.dim_person_id = fcs.dim_person_id
  WHERE fcs.rn = 1
)

SELECT DISTINCT
  dp.institution_person_id
  , t.civ_career_id
  , ps.persistence_score
  , t.current_term_id AS current_enrolled_term
  , current_ed.total_credits AS current_term_credits
  , t.next_term_1 AS next_enrolled_term_1
  , next1_ed.total_credits AS next_enrolled_term_1_credits
  , t.next_term_2 AS next_enrolled_term_2
  , next2_ed.total_credits AS next_enrolled_term_2_credits
FROM terms t
INNER JOIN enrollment_data current_ed ON
  t.civ_career_id = current_ed.civ_career_id
  AND t.current_term_id = current_ed.civ_term_id
LEFT JOIN enrollment_data next1_ed ON
  current_ed.raw_person_id = next1_ed.raw_person_id
  AND t.civ_career_id = next1_ed.civ_career_id
  AND t.next_term_1 = next1_ed.civ_term_id
LEFT JOIN enrollment_data next2_ed ON
  current_ed.raw_person_id = next2_ed.raw_person_id
  AND t.civ_career_id = next2_ed.civ_career_id
  AND t.next_term_2 = next2_ed.civ_term_id
LEFT JOIN persistence_scores ps ON
  current_ed.raw_person_id = ps.raw_person_id
  AND t.civ_career_id = ps.civ_career_id
INNER JOIN data_mart.dim_person dp ON
  current_ed.raw_person_id = dp.raw_person_id
WHERE
  -- Full-time in current term but only part-time in next term
  (
    -- Currently full-time
    (
      (t.civ_career_id = 'U' AND current_ed.total_credits >= 12)
      OR (t.civ_career_id = 'G' AND current_ed.total_credits >= 9)
    )
    AND
    -- Part-time in next term 1 (enrolled but not full-time)
    (
      (t.civ_career_id = 'U' AND next1_ed.total_credits BETWEEN 1 AND 11)
      OR (t.civ_career_id = 'G' AND next1_ed.total_credits BETWEEN 1 AND 8)
    )
  )
  OR
    -- Not enrolled at all in the next two terms
  (next1_ed.total_credits IS NULL AND next2_ed.total_credits IS NULL)
ORDER BY t.civ_career_id, dp.institution_person_id;