Philly schools with 1000+ students average 42% PSSA ELA — 17 points above 200-500 schools

Summary

Joined philly_schools.enrollment (from demographics aggregate) with PSSA, bucketed by 200/500/1000 thresholds.

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

1. Aggregate per enrollment bucket.

   SELECT CASE WHEN e.value <= 200 THEN '1. ≤200' WHEN e.value <= 500 THEN '2. 201-500'
              WHEN e.value <= 1000 THEN '3. 501-1000' ELSE '4. 1000+' END AS bucket,
          count(*), avg(m.value)
   FROM philly_schools s
   JOIN philly_school_year_metrics m ON m.school_ulcs = s.ulcs_code
   JOIN philly_school_year_metrics e ON e.school_ulcs = s.ulcs_code AND e.metric_key='enrollment'
   WHERE m.metric_key='pssa_all_ela_proficiency' AND m.subgroup='ALL' AND m.population_cut='acct'
   GROUP BY bucket ORDER BY bucket;

Reproduce

Demographics + SDP PSSA loaded, query above.