Utility Queries

Active Student Subquery

Use the subquery below to filter your results, returning only active or currently enrolled students for the current term.

WHERE dp.raw_person_id IN (
SELECT DISTINCT
de.raw_person_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
dct.raw_term_id = ds.raw_term_id
WHERE dct.is_active = 1
AND de.raw_career_id <> 'Not Available'
--AND e.civ_career_id IN ('U', 'G') -- Limit to certain civ_career_id's as desired
AND de.civ_is_enrolled = 1)

See example below for how you would use the subquery in the 'Student Persistence Predictions' example.

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.raw_person_id IN (
SELECT DISTINCT
de.raw_person_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
dct.raw_term_id = ds.raw_term_id
WHERE dct.is_active = 1
AND de.raw_career_id <> 'Not Available'
--AND e.civ_career_id IN ('U', 'G') -- Limit to certain civ_career_id's as desired
AND de.civ_is_enrolled = 1)
ORDER BY dp.institution_person_id, fcs.event_ts DESC;

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;
Edit this page