PhiladelphiaGrowth vs statusMethodology recipe

Average PSSA ELA goes UP from grade 5 to grade 6 in Philly — selection, not learning

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

Summary

Grouped philly_school_year_metrics by metric_key for grades 3-8 PSSA ELA (subgroup=ALL, Acct). Computes count of schools per grade and average proficiency.

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

Per-grade aggregate.

SELECT metric_key, count(DISTINCT school_ulcs) AS n, avg(value)
FROM philly_school_year_metrics
WHERE metric_key LIKE 'pssa_grade%_ela_proficiency'
  AND subgroup='ALL' AND population_cut='acct'
GROUP BY metric_key ORDER BY metric_key;

Caveats

The grade-5 to grade-6 jump is an artifact of school structure (K-5 elementary schools drop out of the count at grade 6). The story explains this explicitly. To compare apples-to-apples, restrict to K-8 schools only.

Reproduce

Load SDP PSSA, query above.

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