Movers
Data sources, queries, caveats, and steps for the year-over-year delta ranking.
Summary
Year-over-year delta on the chosen metric: self-joins philly_school_year_metrics on (school, year=from) and (school, year=to), filtered to ALL subgroup + chosen population_cut, ordered by delta. NOT direction-adjusted: 'biggest gains' on a lower-better metric means biggest increase (which is worse).
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).
Steps
Self-join the metric table on school_ulcs + metric_key + subgroup + population_cut, with a.year=from and b.year=to.
Order by (b.value - a.value) DESC for gains, ASC for losses.
Caveats
Cross-year comparisons should respect the comparability regimes (see Philly's `pipeline_philly/config/comparability.yaml`): PSSA recalibration in 2014-15 and 2022-23 make raw cross-year deltas noisier across regime boundaries. Direction is NOT auto-flipped — read the metric definition before interpreting `biggest gains` for OSS, chronic absenteeism, etc.
Reproduce
Backing query is `lib/queries/philly-schools.ts:getPhillyMovers`. Raw SQL is a single self-join + ORDER BY; the function inlines it.
Recipe lives at data/cities/philly/stories/recipes.ts.