The 32-percentage-point gap between Philly's best and worst council districts on PSSA ELA

Summary

Grouped philly_schools by `city_council_district` and aggregated `pssa_all_ela_proficiency` (subgroup=ALL, population_cut=acct) for median per district. Restricted to districts with ≥5 schools.

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. Per-district median + econ-disadv.

   SELECT s.city_council_district, count(*),
          percentile_cont(0.5) WITHIN GROUP (ORDER BY m.value) AS med_pssa,
          percentile_cont(0.5) WITHIN GROUP (ORDER BY dem.value) AS med_econ
   FROM philly_schools s
   JOIN philly_school_year_metrics m ON m.school_ulcs = s.ulcs_code
   LEFT JOIN philly_school_year_metrics dem ON dem.school_ulcs = s.ulcs_code AND dem.metric_key='pct_econ_disadv'
   WHERE m.metric_key='pssa_all_ela_proficiency' AND m.subgroup='ALL' AND m.population_cut='acct'
     AND s.city_council_district IS NOT NULL
   GROUP BY s.city_council_district;

Reproduce

Load Master School List + SDP PSSA, run the query.