Six Philly schools with high teacher climate AND high PSSA — but also six exceptions

Summary

Joined `survey_teacher_school_climate` (PSES topic rollup for 2024-25) with `pssa_all_ela_proficiency` (Acct cut) at the same school. Lists schools where both have data, ordered by climate score.

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 datasetPhilly School Experience Survey
• External datasetSDP PSSA & Keystone (OpenDataPhilly CDN)

Steps

1. Join the two metrics at the school × year level.

   SELECT s.name, ses.value AS pses_climate, pssa.value AS pssa_ela
   FROM philly_schools s
   JOIN philly_school_year_metrics ses ON ses.school_ulcs=s.ulcs_code AND ses.metric_key='survey_teacher_school_climate' AND ses.year='2024-25'
   JOIN philly_school_year_metrics pssa ON pssa.school_ulcs=s.ulcs_code AND pssa.metric_key='pssa_all_ela_proficiency' AND pssa.subgroup='ALL' AND pssa.population_cut='acct'
   ORDER BY ses.value DESC, pssa.value DESC;

2. For correlation: SELECT corr(ses.value, pssa.value) FROM the same join.

Reproduce

Load PSES topic-subtopic file via `pses.ts`, then SDP PSSA, then SQL.