12 Philly schools punching above demographic expectation on PSSA ELA

Summary

Joined philly_school_year_metrics for `pct_econ_disadv` (filter ≥80) with `pssa_all_ela_proficiency` (subgroup=ALL, population_cut=acct) where `comparison_group_percentile ≥ 0.85`, ordered by peer percentile then value descending.

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).

• Database tablephilly_comparison_groups

  K-nearest-neighbor peer groups (k=20/30/40 derived; k=40 default). Hard filters: school level + admission bucket + sector. Demographic Euclidean across 7 features.

• External datasetSDP PSSA & Keystone (OpenDataPhilly CDN)

Steps

1. Identify high-need schools (≥80% econ-disadv).

   SELECT school_ulcs FROM philly_school_year_metrics
   WHERE metric_key = 'pct_econ_disadv' AND value >= 80;

2. Join those to PSSA ELA, filter to peer percentile ≥0.85, order desc.

   SELECT s.name, m.value AS pssa, m.comparison_group_percentile AS peer
   FROM philly_school_year_metrics m
   JOIN philly_schools s ON s.ulcs_code = m.school_ulcs
   WHERE m.metric_key = 'pssa_all_ela_proficiency'
     AND m.subgroup = 'ALL'
     AND m.population_cut = 'acct'
     AND m.comparison_group_percentile >= 0.85
   ORDER BY m.comparison_group_percentile DESC LIMIT 12;

Reproduce

Run `make philly-discover-all` to fetch sources, then `make philly-load-directory` + outcome loaders + `derive_peer_groups.ts` + `compute_percentiles.ts`. The query above is then directly runnable.