Education cohort analytics: attendance, completion and missing records

An attendance rate needs a population of sessions each learner was eligible to attend. Counting only recorded attendance rows can hide missing records; counting every scheduled session can penalize learners for canceled classes or sessions before enrollment.

Completion requires a separate cohort and outcome horizon. A learner can attend frequently without completing an assessment, and a missing assessment record is not automatically a failure.

Build the expected learner-session grid

The original synthetic learners, sessions and attendance records do not describe actual NeuraPath students.

Three sessions were held and a fourth was canceled. L3 enrolled after the first session. L4 withdrew after the second. For attendance, include held sessions on or after enrollment and strictly before withdrawal, where applicable.

SELECT l.learner_id,s.session_id,COALESCE(a.status,'unknown') AS attendance_status
FROM learners l CROSS JOIN class_sessions s
LEFT JOIN attendance a
 ON a.learner_id=l.learner_id AND a.session_id=s.session_id
WHERE s.status='held' AND s.session_date<'2026-02-01'
 AND s.session_date>=l.enrolled_at
 AND (l.withdrawn_at IS NULL OR s.session_date<l.withdrawn_at)
ORDER BY l.learner_id,s.session_id;

The grid contains ten eligible learner-session pairs: three each for L1 and L2, two for L3 and two for L4. Seven are present, two absent and one unknown. L3's missing third-session record becomes visible because the expected grid exists independently of the attendance table.

Verify attendance and coverage separately

from build_and_verify import database

db = database()
expected = []
for learner,enrolled,withdrawn,due,assessment in db.execute('SELECT * FROM learners'):
    for session,day,status in db.execute("SELECT * FROM class_sessions WHERE status='held' AND session_date<'2026-02-01'"):
        if day>=enrolled and (withdrawn is None or day<withdrawn):
            row = db.execute('SELECT status FROM attendance WHERE learner_id=? AND session_id=?',(learner,session)).fetchone()
            expected.append((learner,session,row[0] if row else 'unknown'))
mature = db.execute("SELECT learner_id,assessment_status FROM learners WHERE assessment_due<'2026-02-01'").fetchall()
db.close()
present = sum(r[2]=='present' for r in expected)
absent = sum(r[2]=='absent' for r in expected)
unknown = sum(r[2]=='unknown' for r in expected)
assert (len(expected),present,absent,unknown)==(10,7,2,1)
assert {r[0] for r in mature}=={'L1','L2','L4'}
assert sum(status=='passed' for learner,status in mature)==1
print({'attendance_among_recorded':present/(present+absent),
       'coverage':(present+absent)/len(expected),
       'all_eligible_attendance_bounds':(present/10,(present+unknown)/10),
       'confirmed_completions':1,'mature_enrollment_population':len(mature)})

Recorded attendance is 7/9 ≈ 77.8% with 90% record coverage. Across all ten eligible pairs, attendance lies between 70% and 80% depending on the unresolved record. That range describes missing-data uncertainty, not a confidence interval.

Keep completion's denominator independent

By the cutoff, L1, L2 and L4 have reached their assessment deadline. L1 has a recorded pass, L2 has no recorded assessment outcome, and L4 withdrew. L3's later deadline has not arrived and is not yet mature for this completion view.

The confirmed completion share is one of three mature original enrollments. Do not remove L4 merely because withdrawal makes the rate less favorable. Conversely, do not describe L2 as an assessment failure when the outcome is missing.

Publish the status breakdown: confirmed completed, withdrawn, outcome unrecorded and not yet mature. If the program permits extensions or re-entry, define their effect on the cohort and cutoff before comparing batches.

Use the analysis to improve support

Attendance gaps can identify a need to verify records or offer appropriate support. They do not prove motivation, ability or future employment outcomes. The synthetic dataset contains no basis for those claims.

At the operational level, investigate whether attendance collection failed for a session, whether timetable changes were recorded, and whether enrollment dates align with actual access. A missing entire class register should not become a sudden drop in learner engagement.

For public reporting, define completion criteria and disclose the observation window. Comparing an ongoing cohort with a fully finished cohort can create an artificial performance gap.

Exercise: remove every attendance row for S2. Verify that the expected grid remains unchanged and the missing-record count increases. Then explain why an inner join would conceal the collection failure.

NeuraPath's Data Analytics with Generative AI course connects SQL population design with education reporting. A credible cohort dashboard separates participation, evidence coverage and verified completion.

Continue learning

This article is part of the Domain analytics and business cases sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Analyze appointment no-shows without making clinical claims.
• Continue with Manufacturing defect rates with changing inspection volumes.