When teachers want to leave, students stop showing up
A reproducibility log: the data this analysis touched, the queries it ran, the outside sources it leaned on, and the steps a third party would follow to re-run it.
Summary
Computed school-level correlation between survey_teacher_recommend_school (or survey_teacher_trust) and chronic_absenteeism_rate using the most recent year both metrics are available at the same school.
Data sources
- Database tableschool_year_metrics
Long-format per-school per-year metric facts (school_dbn, year, metric_key, subgroup, value, suppressed). Loaded from DOE/NYSED public files via scripts/loaders/*.
Steps
Self-join survey + absenteeism rows; compute Pearson r and a scatter for the chart.
SELECT s.dbn, t.value AS teacher_recommend, a.value AS abs_rate FROM schools s JOIN school_year_metrics t ON t.school_dbn=s.dbn AND t.metric_key='survey_teacher_recommend_school' AND t.year='2023-24' AND t.suppressed=false JOIN school_year_metrics a ON a.school_dbn=s.dbn AND a.metric_key='chronic_absenteeism_rate' AND a.year='2023-24' AND a.suppressed=false;
Caveats
Cross-sectional correlation, not causal. Schools with high teacher dissatisfaction may also serve harder-to-serve students, so the correlation absorbs both effects.
Reproduce
Clone the repo, set DATABASE_URL to a Postgres with the project schema loaded, run `npx tsx scripts/loaders/<source>.ts` for any not-yet-loaded data, then issue the queries in the Steps section. The story page also lists the exact `metric_key`/`subgroup`/`year` filters used. Searchable by the answer's headline number — every figure is recomputable from the queries shown.
The recipe lives at data/stories/recipes.ts in the repo. Corrections welcome.