Subgroup gapsMethodology recipe

Why is the Bronx better at including students with disabilities?

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

Borough-level comparison of the gap between Students With Disabilities (SWD) subgroup outcomes and the all-students average at the same schools, using ela_all_proficiency / math_all_proficiency for the ALL and SWD subgroups.

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/*.

Steps

Join SWD-subgroup proficiency with ALL-subgroup proficiency at each school for 2024-25; compute the within-school gap.

SELECT s.borough,
       AVG(swd.value - all_.value) AS avg_swd_minus_all_gap,
       COUNT(*) AS n
  FROM schools s
  JOIN school_year_metrics swd  ON swd.school_dbn=s.dbn  AND swd.subgroup='SWD'  AND swd.metric_key='ela_all_proficiency' AND swd.year='2024-25' AND swd.suppressed=false
  JOIN school_year_metrics all_ ON all_.school_dbn=s.dbn AND all_.subgroup='ALL' AND all_.metric_key='ela_all_proficiency' AND all_.year='2024-25' AND all_.suppressed=false
 GROUP BY s.borough;

Caveats

SWD is a heterogeneous category — students with Individualized Education Programs span a wide range of needs. The within-school gap reflects both the severity-mix of the SWD population and the school's instructional response; the two aren't separable here. Suppression for small subgroup-n is common; counts shown weight schools equally regardless of n.

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.