PhiladelphiaGrowth vs statusMethodology recipe

Philly's growth-vs-proficiency quadrant: 4 categories of school worth different attention

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

Summary

Joins PVAAS Algebra growth with Keystone Algebra proficiency at the same school. Plots the four-quadrant scatter (high/low growth × high/low proficiency).

Data sources

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 datasetFuture Ready PA Index (PDE)
External datasetSDP PSSA & Keystone (OpenDataPhilly CDN)

Steps

Join the two metrics.

SELECT s.name, g.value AS growth, p.value AS proficiency
FROM philly_schools s
JOIN philly_school_year_metrics g ON g.school_ulcs = s.ulcs_code AND g.metric_key='pvaas_growth_math_algebra1' AND g.subgroup='ALL'
JOIN philly_school_year_metrics p ON p.school_ulcs = s.ulcs_code AND p.metric_key='keystone_algebra_proficiency' AND p.subgroup='ALL'
WHERE g.value IS NOT NULL AND p.value IS NOT NULL;

Caveats

PVAAS uses a fixed scale (0-100 typically); proficiency is 0-100 percent. Both axes have different distributions, so quadrant boundaries should be set at meaningful percentiles (e.g. median of each).

Reproduce

Both metrics loaded. Query above.

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