COVID recoveryMethodology recipe

How D15 unscreening 5 years in: outcomes, equity, both?

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

Compared all 8 still-open District 15 middle schools (lottery since 2019) with all 9 still-open District 13 middle schools (mostly still SCREENED). Three lenses: per-school test-score trajectory, between-school spread (standard deviation across each district), and subgroup-average gaps.

Data sources

Database tableschools
One row per New York City public school (DBN, name, district, borough, school_type, grade_band, latest_enrollment, proclivity_decile, proclivity_score, closed_at, admission_category).
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/*.
Loader scriptscripts/loaders/_d15_query.ts
Pulls middle-school rosters, per-school 2018-19 ↔ 2024-25 trajectories, percentile spreads, subgroup averages, and absenteeism series for both districts.
Cached outputdata/stories/d15-data.json
External datasetNYC DOE, District 15 Diversity Plan (2018) — policy that ended D15 middle-school screening

Steps

Identify the middle-school cohort in each district (grade_band ∈ {MS, K8}, district = '15' or '13', not closed).

SELECT dbn, name, grade_band::text, admission_category::text
  FROM schools
 WHERE district = '15' AND grade_band::text IN ('MS','K8') AND closed_at IS NULL;

Per-school ELA + math proficiency trajectory, 2018-19 → 2024-25, all-student subgroup.

SELECT s.dbn, s.name,
       MAX(CASE WHEN year='2018-19' AND metric_key='ela_all_proficiency' THEN value END) AS ela_2018,
       MAX(CASE WHEN year='2024-25' AND metric_key='ela_all_proficiency' THEN value END) AS ela_2024,
       MAX(CASE WHEN year='2018-19' AND metric_key='math_all_proficiency' THEN value END) AS math_2018,
       MAX(CASE WHEN year='2024-25' AND metric_key='math_all_proficiency' THEN value END) AS math_2024
  FROM schools s
  JOIN school_year_metrics m ON m.school_dbn = s.dbn
 WHERE s.dbn IN (<district MS DBNs>)
   AND m.subgroup='ALL' AND m.suppressed=false
 GROUP BY s.dbn, s.name;

Between-school spread per year: percentile_cont(0.10/0.50/0.90), stddev, range across each district's schools, for 2017-18 through 2024-25.

SELECT year, metric_key,
       PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY value) AS p10,
       PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY value) AS p50,
       PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY value) AS p90,
       STDDEV(value) AS sd
  FROM school_year_metrics
 WHERE school_dbn IN (<district MS DBNs>)
   AND metric_key IN ('ela_all_proficiency','math_all_proficiency')
   AND subgroup='ALL' AND suppressed=false
 GROUP BY year, metric_key;

Subgroup averages: average per-school proficiency for WHITE/BLACK/HISPANIC/ASIAN, 2018-19 vs 2024-25.

Caveats

D15 has 8 MS schools and D13 has 9 — per-school medians are noisy. The 2022-23 state-test recalibration raised proficiency rates citywide; changes are absolute. Subgroup averages weight schools equally regardless of subgroup-n; D15's Black-student average is especially noisy because some D15 schools have very few Black students. Most importantly, we don't yet have per-school demographic-share data loaded, so we can't verify the integration outcome that prior reporting documented.

References

Reproduce

Run `npx tsx scripts/loaders/_d15_query.ts > data/stories/d15-data.json`. Inspect d15_traj / d13_traj for per-school changes, d15_spread / d13_spread for between-school variation. To extend to other districts: change the district filter in D15_MS_DBNS / D13_MS_DBNS.

The recipe lives at data/stories/recipes.ts in the repo. Corrections welcome.