Utility Queries
Active Student Subquery
Use the subquery below to filter your results, returning only active or currently enrolled students for the current term.
See example below for how you would use the subquery in the 'Student Persistence Predictions' example.
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.
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.
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 (SELECTds.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_courseFROM data_mart.fact_grade fgJOIN data_mart.dim_section ds ONfg.dim_section_id = ds.dim_section_idJOIN data_mart.dim_person dp ONfg.dim_person_id = dp.dim_person_idJOIN data_mart.dim_career_term dct ONds.civ_term_id = dct.civ_term_idWHEREds.civ_instruction_mode IN ('Online', 'Offline', 'Blended')AND dct.raw_acad_year >= EXTRACT(YEAR FROM CURRENT_DATE) - 2 -- Last 2 yearsAND dp.raw_eff_ts_end >= CURRENT_DATE)SELECTciv_instruction_mode, COUNT(*) AS total_enrollments, COUNT(DISTINCT dim_person_id) AS unique_students, ROUND(COUNT(CASEWHEN civ_is_favorable = 1THEN 1END) * 100.0 / COUNT(*), 2) AS success_rate, ROUND(COUNT(CASEWHEN civ_is_withdraw = 1THEN 1END) * 100.0 / COUNT(*), 2) AS withdrawal_rate, ROUND(AVG(civ_gpa_points), 2) AS avg_gpa_points-- Demographics breakdown, ROUND(COUNT(CASEWHEN civ_first_to_attend_college = 1THEN 1END) * 100.0 / COUNT(*), 2) AS first_gen_pct, ROUND(COUNT(CASEWHEN civ_federal_financial_aid = 1THEN 1END) * 100.0 / COUNT(*), 2) AS pell_pct, ROUND(COUNT(CASEWHEN civ_is_veteran = 1THEN 1END) * 100.0 / COUNT(*), 2) AS veteran_pct, ROUND(AVG(age_at_course), 1) AS avg_student_age-- Success rates by demographics, ROUND(AVG(CASEWHEN civ_first_to_attend_college = 1 AND civ_is_favorable = 1THEN 1.0WHEN civ_first_to_attend_college = 1THEN 0.0END), 3) AS first_gen_success_rate, ROUND(AVG(CASEWHEN civ_federal_financial_aid = 1 AND civ_is_favorable = 1THEN 1.0WHEN civ_federal_financial_aid = 1THEN 0.0END), 3) AS pell_success_rate, ROUND(AVG(CASEWHEN age_at_course >= 25 AND civ_is_favorable = 1THEN 1.0WHEN age_at_course >= 25THEN 0.0END), 3) AS adult_learner_success_rateFROM modality_performanceGROUP BY civ_instruction_modeORDER BY success_rate DESC;