Find What Works: Schools

← Back to story

PhiladelphiaIntervention cohortsMethodology recipe

Only 4 Philly schools are NOT Title I — and they average 18 points below Title I peers

A reproducibility log: the data this analysis touched, the queries it ran, and how a third party would re-run it.

Summary

Grouped philly_schools by Title I flag and aggregated PSSA ELA. 99% of Philly is Title I; the 4 non-Title-I schools have lower outcomes (small-n caveat).

Data sources

  • Database tablephilly_schools

    One row per Philly school. PK is ulcs_code (SDP canonical, = id_eos in PSSA, = SRC_School_ID × 10). Carries aun_code + pa_code for PDE-side joins.

  • Database tablephilly_school_year_metrics

    Long-format facts. PK (school_ulcs, year, metric_key, subgroup, population_cut). Carries comparison_group_percentile + citywide_percentile (direction-adjusted).

  • External datasetSDP PSSA & Keystone (OpenDataPhilly CDN)

Steps

  1. Aggregate per Title I bucket.

    SELECT s.title_i, count(*), avg(m.value), percentile_cont(0.5) WITHIN GROUP (ORDER BY m.value)
FROM philly_schools s
JOIN philly_school_year_metrics m ON m.school_ulcs = s.ulcs_code
WHERE m.metric_key='pssa_all_ela_proficiency' AND m.subgroup='ALL' AND m.population_cut='acct'
GROUP BY s.title_i;

Caveats

n=4 for non-Title-I is too small for statistical inference. The story is the universal Title-I designation, not the proficiency gap.

Reproduce

Load Master List, run query.

The recipe lives at data/cities/philly/stories/recipes.ts in the repo.