Philly PSSA: 2023-24 vs 2024-25 year-over-year — which schools moved most

Summary

With 2023-24 and 2024-25 SDP PSSA both in DB, compute per-school year-over-year deltas on each metric × subgroup.

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 datasetSDP PSSA & Keystone (OpenDataPhilly CDN)

Steps

1. Self-join PSSA metrics across the two years.

   SELECT a.school_ulcs, a.metric_key, a.subgroup::text, b.value - a.value AS delta
   FROM philly_school_year_metrics a
   JOIN philly_school_year_metrics b ON b.school_ulcs = a.school_ulcs
    AND b.metric_key = a.metric_key AND b.subgroup = a.subgroup AND b.population_cut = a.population_cut
   WHERE a.year='2023-24' AND b.year='2024-25' AND a.metric_key LIKE 'pssa_%' AND a.subgroup='ALL' AND a.population_cut='acct';

Reproduce

Both years of SDP PSSA loaded. Query above.