This cookbook of queries is meant to highlight how the synthetic data is related and give inspiration for query creation. Each query is a start and could be edited in a few ways to tease out different trends. Queries include averages, but medians/a 5 number summary would give more detail to analysis.
Data dictionaries from the National Clearing House
-- Retention rate by cohort
SELECT
Cohort,
COUNT(DISTINCT "Student GUID") as total_students,
SUM(CASE WHEN Retention = 1 THEN 1 ELSE 0 END) as retained,
ROUND(100.0 * SUM(CASE WHEN Retention = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) as retention_rate
FROM cohorts
GROUP BY Cohort
ORDER BY Cohort;Result:
Cohort total_students retained retention_rate
2016-17 2038 1653 81.11
2017-18 1960 1602 81.73
2018-19 2030 1663 81.92
2019-20 2029 1659 81.76
2020-21 1943 1593 81.99
-- Gateway completion impact on retention
SELECT
"Gateway Math Status",
"Gateway English Status",
COUNT(*) as students,
ROUND(AVG(Retention) * 100, 2) as retention_rate,
ROUND(AVG("GPA Group Year 1"), 2) as avg_gpa
FROM cohort
WHERE "Gateway Math Status" IS NOT NULL
GROUP BY "Gateway Math Status", "Gateway English Status"
ORDER BY retention_rate DESC;Result:
Gateway Math Status Gateway English Status students retention_rate avg_gpa
N UK 126 87.3 3.12
R UK 103 86.41 3.1
N N 3536 84.19 3.32
UK N 161 81.37 3.04
R N 2443 80.31 3.08
N R 1897 80.23 3.1
R R 1612 79.9 3.07
UK R 116 75.0 3.05
UK UK 6 66.67 2.56
-- Students who completed gateway in Year 1
SELECT
COUNT(*) as total_students,
SUM(CASE WHEN CompletedGatewayMathYear1 = 'C' THEN 1 ELSE 0 END) as completed_math,
SUM(CASE WHEN CompletedGatewayEnglishYear1 = 'C' THEN 1 ELSE 0 END) as completed_english,
SUM(CASE WHEN CompletedGatewayMathYear1 = 'C'
AND CompletedGatewayEnglishYear1 = 'C' THEN 1 ELSE 0 END) as completed_both
FROM cohort;
Result:
total_students completed_math completed_english completed_both
10000 1982 2085 461
-- Gateway completion impact on retention
SELECT
"Gateway Math Status",
"Gateway English Status",
COUNT(*) as students,
ROUND(AVG(Retention) * 100, 2) as retention_rate,
ROUND(AVG("GPA Group Year 1"), 2) as avg_gpa
FROM cohort
WHERE "Gateway Math Status" IS NOT NULL
GROUP BY "Gateway Math Status", "Gateway English Status"
ORDER BY retention_rate DESC;
-- Students who completed gateway in Year 1
SELECT
COUNT(*) as total_students,
SUM(CASE WHEN CompletedGatewayMathYear1 = 'C' THEN 1 ELSE 0 END) as completed_math,
SUM(CASE WHEN CompletedGatewayEnglishYear1 = 'C' THEN 1 ELSE 0 END) as completed_english,
SUM(CASE WHEN CompletedGatewayMathYear1 = 'C'
AND CompletedGatewayEnglishYear1 = 'C' THEN 1 ELSE 0 END) as completed_both
FROM cohort;
-- Credit completion ratio (strong success predictor)
SELECT
"Student GUID",
"Number of Credits Attempted Year 1",
"Number of Credits Earned Year 1",
ROUND(100.0 * "Number of Credits Earned Year 1" /
NULLIF("Number of Credits Attempted Year 1", 0), 2) as completion_ratio,
"GPA Group Year 1",
Retention
FROM cohort
WHERE "Number of Credits Attempted Year 1" > 0
ORDER BY completion_ratio DESC;
-- Average credits by enrollment intensity (part time vs full time)
SELECT
"Enrollment Intensity First Term",
COUNT(*) as students,
ROUND(AVG("Number of Credits Earned Year 1"), 2) as avg_credits_earned,
ROUND(AVG(Retention) * 100, 2) as retention_rate
FROM cohort
GROUP BY "Enrollment Intensity First Term";
-- Success rates by demographic groups
SELECT
Race,
Ethnicity,
COUNT(*) as students,
ROUND(AVG("GPA Group Year 1"), 2) as avg_gpa,
ROUND(AVG(Retention) * 100, 2) as retention_rate,
ROUND(100.0 * SUM(CASE WHEN "Years to Bachelors at cohort inst." > 0
OR "Years to Associates or Certificate at cohort inst." > 0
THEN 1 ELSE 0 END) / COUNT(*), 2) as completion_rate
FROM cohort
GROUP BY Race, Ethnicity
HAVING COUNT(*) >= 10 -- Only groups with 10+ students
ORDER BY retention_rate DESC;
-- First-gen vs non-first-gen comparison
SELECT
"First Gen",
"Pell Status First Year",
COUNT(*) as students,
ROUND(AVG("GPA Group Year 1"), 2) as avg_gpa,
ROUND(AVG(Retention) * 100, 2) as retention_rate
FROM cohort
GROUP BY "First Gen", "Pell Status First Year";
-- Courses with highest withdrawal rates
SELECT
"Course Prefix",
"Course Number",
"Course Name",
COUNT(*) as enrollments,
SUM(CASE WHEN Grade = 'W' THEN 1 ELSE 0 END) as withdrawals,
ROUND(100.0 * SUM(CASE WHEN Grade = 'W' THEN 1 ELSE 0 END) / COUNT(*), 2) as withdrawal_rate,
ROUND(AVG(CASE WHEN Grade IN ('A', 'B', 'C', '3', '4') THEN 1.0
WHEN Grade IN ('D', 'F', 'W') THEN 0.0 END) * 100, 2) as success_rate
FROM courses
WHERE Grade IS NOT NULL
GROUP BY "Course Prefix", "Course Number", "Course Name"
HAVING COUNT(*) >= 20 -- Only courses with 20+ enrollments
ORDER BY withdrawal_rate DESC
LIMIT 20;
-- Delivery method impact
SELECT
"Delivery Method",
COUNT(*) as enrollments,
ROUND(AVG(CASE WHEN Grade IN ('3', '4') THEN 1.0 ELSE 0.0 END) * 100, 2) as success_rate,
ROUND(AVG("Number of Credits Earned"), 2) as avg_credits_earned
FROM courses
GROUP BY "Delivery Method";
-- Students needing intervention (multiple risk factors)
SELECT
c."Student GUID",
c."First Gen",
c."Pell Status First Year",
c."GPA Group Year 1",
c."Number of Credits Earned Year 1",
c."Number of Credits Attempted Year 1",
ROUND(100.0 * c."Number of Credits Earned Year 1" /
NULLIF(c."Number of Credits Attempted Year 1", 0), 2) as completion_ratio,
COUNT(CASE WHEN co.Grade = 'W' THEN 1 END) as withdrawal_count,
c.CompletedGatewayMathYear1,
c.CompletedGatewayEnglishYear1
FROM cohort c
LEFT JOIN courses co ON c."Student GUID" = co."Student GUID"
WHERE c."GPA Group Year 1" < 2.5
OR (c."Number of Credits Earned Year 1" * 1.0 /
NULLIF(c."Number of Credits Attempted Year 1", 0)) < 0.67
GROUP BY c."Student GUID"
HAVING COUNT(CASE WHEN co.Grade = 'W' THEN 1 END) >= 2
ORDER BY completion_ratio ASC;
-- Best and worst performing programs
SELECT
"Program of Study Term 1",
COUNT(*) as students,
ROUND(AVG("GPA Group Year 1"), 2) as avg_gpa,
ROUND(AVG(Retention) * 100, 2) as retention_rate,
ROUND(AVG("Number of Credits Earned Year 1"), 2) as avg_credits_earned
FROM cohort
WHERE "Program of Study Term 1" IS NOT NULL
GROUP BY "Program of Study Term 1"
HAVING COUNT(*) >= 5
ORDER BY retention_rate DESC;
-- Analyze toxic (same department overload) vs synergetic (diverse) course combinations
WITH student_term_diversity AS (
SELECT
co."Student GUID",
co."Academic Year",
co."Academic Term",
-- Count courses and departments
COUNT(DISTINCT co."Course Number") as total_courses,
COUNT(DISTINCT co."Course Prefix") as unique_departments,
-- Diversity ratio: 1.0 = all different depts, 0.25 = all same dept (if 4 courses)
ROUND(COUNT(DISTINCT co."Course Prefix") * 1.0 /
NULLIF(COUNT(DISTINCT co."Course Number"), 0), 2) as diversity_ratio,
-- List departments taken
STRING_AGG(co."Course Prefix", ', ') as departments,
-- Identify heavy concentrations
SUM(CASE WHEN co."Course Prefix" IN ('CHEM', 'BIO', 'PHYS', 'MATH', 'STAT')
THEN 1 ELSE 0 END) as stem_count,
SUM(CASE WHEN co."Course Prefix" IN ('ENG', 'ENGL', 'WRIT', 'LIT', 'HIST', 'PHIL')
THEN 1 ELSE 0 END) as writing_count,
-- Success metrics
SUM(co."Number of Credits Attempted") as credits_attempted,
SUM(co."Number of Credits Earned") as credits_earned,
SUM(CASE WHEN co."Number of Credits Earned" = 0 AND co."Number of Credits Attempted" > 0
THEN 1 ELSE 0 END) as failures_withdrawals,
ROUND(AVG(CASE WHEN co."Number of Credits Earned" > 0 THEN 1.0 ELSE 0.0 END) * 100, 2) as success_rate
FROM courses co
GROUP BY co."Student GUID", co."Academic Year", co."Academic Term"
HAVING COUNT(DISTINCT co."Course Number") >= 3 -- Analyze 3+ courses only
)
SELECT
-- Classify pattern
CASE
WHEN diversity_ratio <= 0.34 THEN 'TOXIC: Single Dept Overload'
WHEN stem_count >= 4 THEN 'TOXIC: 4+ STEM courses'
WHEN writing_count >= 4 THEN 'TOXIC: 4+ Writing courses'
WHEN diversity_ratio >= 0.75 THEN 'SYNERGETIC: High Diversity (75%+)'
WHEN diversity_ratio >= 0.6 THEN 'SYNERGETIC: Balanced Mix (60%+)'
ELSE 'NEUTRAL'
END as pattern_type,
COUNT(*) as student_term_occurrences,
ROUND(AVG(diversity_ratio), 2) as avg_diversity_ratio,
ROUND(AVG(total_courses), 1) as avg_courses_per_term,
ROUND(AVG(success_rate), 2) as avg_course_success_rate,
ROUND(AVG(credits_earned * 100.0 / NULLIF(credits_attempted, 0)), 2) as credit_completion_rate,
ROUND(AVG(failures_withdrawals), 2) as avg_failures_or_withdrawals,
-- Join to cohort for retention
ROUND(AVG(c.Retention) * 100, 2) as retention_rate,
-- Show example department combinations (first 3 examples)
MAX(SUBSTRING(departments, 1, 50)) as example_dept_combo
FROM student_term_diversity std
LEFT JOIN cohorts c ON std."Student GUID" = c."Student GUID"
GROUP BY pattern_type
ORDER BY avg_course_success_rate ASC;