Student Performance Risk Analysis

Interactive SQL storyboard · 19 features & 4 cohorts

Use the tabs below to inspect the exact PostgreSQL query outputs across the 6,607-student dataset: the 19-feature driver leaderboard, the 4 behavioral cohorts, and the Attendance × Study Hours risk matrix.

1 · Data hygiene & mode imputation in SQL

Before running any aggregations, Section 2 of sql/student_performance_analysis.sql audits completeness and domain bounds across all 6,607 rows:

• Range violation (1 row): One student record carried Exam_Score = 101 on a 100-point scale. Rather than dropping the student, LEAST(GREATEST(exam_score, 0), 100) caps the value cleanly at 100.
• Missing categoricals (235 nulls across 3 columns): Parental_Education_Level (90 nulls, 1.36%), Teacher_Quality (78 nulls, 1.18%), and Distance_from_Home (67 nulls, 1.01%). A CTE computes each column’s statistical mode via PostgreSQL’s ordered-set aggregate MODE() WITHIN GROUP (ORDER BY col) and imputes missing values via COALESCE ('High School', 'Medium', and 'Near').
• Ordinal & binary encoding: Every qualitative scale (Low / Medium / High, Negative / Neutral / Positive, Near / Moderate / Far) is mapped to 0 / 1 / 2 alongside the human-readable label so downstream views can compute composite indices and Pearson correlations directly in SQL.

CREATE OR REPLACE VIEW vw_student_factors_clean AS
WITH modal_defaults AS (
    SELECT
        MODE() WITHIN GROUP (ORDER BY teacher_quality)          AS mode_teacher_quality,
        MODE() WITHIN GROUP (ORDER BY parental_education_level) AS mode_parental_ed,
        MODE() WITHIN GROUP (ORDER BY distance_from_home)       AS mode_distance
    FROM raw_student_performance
)
SELECT
    ROW_NUMBER() OVER (
        ORDER BY r.exam_score DESC, r.attendance DESC, r.hours_studied DESC
    )                                                           AS student_id,
    r.hours_studied,
    r.attendance,
    r.previous_scores,
    r.sleep_hours,
    r.tutoring_sessions,
    r.physical_activity,
    LEAST(GREATEST(r.exam_score, 0), 100)                       AS exam_score,
    COALESCE(r.teacher_quality, m.mode_teacher_quality)         AS teacher_quality,
    COALESCE(r.parental_education_level, m.mode_parental_ed)    AS parental_education_level,
    COALESCE(r.distance_from_home, m.mode_distance)             AS distance_from_home
    -- + ordinal (0/1/2) and binary (0/1) encoded columns
FROM raw_student_performance AS r
CROSS JOIN modal_defaults AS m;

2 · Window functions & cohort segmentation

Instead of black-box clustering that advisors cannot audit, vw_student_analytical_profile segments students using transparent SQL rules and window functions that compare each student against their own school_type × family_income peer group:

Interactive simulator · run the SQL triage logic

Test how vw_student_analytical_profile and vw_advising_triage_queue classify a student row and estimate at-risk probability from the dataset’s empirical cohort cells:

3 · The operational takeaway: reallocate tutoring to the “Silent Slip”

When you group the 6,607 students by Attendance Tier × Study Hours Tier (Section 7 of the SQL script), two facts jump off the page:

• Prior scores do not protect against disengagement: Average Previous_Scores sits between 73.4 and 76.0 in every single cell of the 3×3 Attendance × Study matrix. Yet the at-risk rate (Exam_Score <= 65) swings from 97.1% (Attendance < 70%, Study < 15h) down to 0.2% (Attendance ≥ 85%, Study ≥ 25h).
• 687 high-prior-score students failed the threshold: Out of 2,131 total at-risk students, 687 entered with Previous_Scores >= 80 (mean 89.1) but averaged 69.3% attendance and 16.1 study hours. Any advising filter that screens out students with strong prior grades misses nearly a third of all bad outcomes.
• Tutoring is untargeted: Students with 3+ monthly tutoring sessions score +1.64 points higher and cut their at-risk rate nearly in half (40.3% → 22.4%), yet average tutoring across cohorts is completely flat (1.47/mo for Disengaged At-Risk and Drifting High-Potential vs. 1.50/mo for High-Discipline Anchors).

CREATE OR REPLACE VIEW vw_advising_triage_queue AS
SELECT
    student_id,
    cohort_segment,
    CASE
        WHEN previous_scores >= 80 AND attendance < 75 AND hours_studied < 18
            THEN 'P1: Silent Slip (High Prior Score, Acute Attendance/Study Drop)'
        WHEN attendance < 70 AND hours_studied < 15
            THEN 'P1: Critical Disengagement (97% Historical Risk Band)'
        WHEN access_to_resources = 'Low' AND internet_access = 'No'
            THEN 'P2: Resource & Connectivity Barrier'
        ELSE 'P3: Moderate Behavioral Drift'
    END AS triage_priority,
    previous_scores,
    attendance,
    hours_studied,
    tutoring_sessions,
    exam_score AS observed_exam_score,
    score_vs_income_school_peers
FROM vw_student_analytical_profile
WHERE attendance < 80
  AND hours_studied < 20
  AND tutoring_sessions < 2
ORDER BY
    CASE
        WHEN previous_scores >= 80 AND attendance < 75 AND hours_studied < 18 THEN 1
        WHEN attendance < 70 AND hours_studied < 15 THEN 2
        ELSE 3
    END,
    attendance ASC,
    hours_studied ASC;

4 · Conclusion: structuring classrooms to close the gap

A report is only useful if it tells a principal or academic dean what to change on Monday morning. Because a school cannot directly rewrite a student’s Family_Income or Parental_Education_Level, Section 9 of sql/student_performance_analysis.sql tests which classroom-level structures offset those baseline disadvantages inside the data:

Limitations & next steps in production

• Cross-sectional grain: Each row is one student snapshot. In a live registrar warehouse, I would join weekly LMS login and attendance deltas (Week 4 vs. Week 1) so vw_advising_triage_queue triggers before midterms.
• Self-reported study and sleep hours: Surveyed study hours can carry reporting bias, whereas registrar attendance and LMS submissions are system-logged.
• Closed-loop tracking: Add an intervention_log table to measure whether reallocating tutoring vouchers to P1/P2 queue students actually recovers exam scores.