The 53 Philly CSI schools: where they stand on PSSA right now

Summary

Grouped philly_schools by `essa_designation`, aggregated on `pssa_all_ela_proficiency` (subgroup=ALL, population_cut=acct) for mean + median + count + average econ_disadv per tier.

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 datasetFuture Ready PA Index (PDE)

Steps

1. Per-tier aggregate.

   SELECT s.essa_designation::text,
          count(DISTINCT s.ulcs_code) AS n_schools,
          avg(m.value) AS avg_pssa,
          percentile_cont(0.5) WITHIN GROUP (ORDER BY m.value) AS median_pssa,
          avg(dem.value) AS avg_econ_disadv
   FROM philly_schools s
   JOIN philly_school_year_metrics m ON m.school_ulcs = s.ulcs_code
   JOIN philly_school_year_metrics dem ON dem.school_ulcs = s.ulcs_code AND dem.metric_key='pct_econ_disadv'
   WHERE m.metric_key='pssa_all_ela_proficiency' AND m.subgroup='ALL' AND m.population_cut='acct'
   GROUP BY s.essa_designation;

Reproduce

Load Master School List + Future Ready Performance file, then run the SQL above. To get the full 53-school cohort, additionally backfill `essa_designation` from Future Ready (loader work pending).