PhiladelphiaDistrict vs charterMethodology recipe

Philly charters have a 10-point attendance advantage over district schools

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

Summary

Grouped philly_schools by school_type and aggregated `attendance_persistence_rate` (Future Ready: % students ≥90% attendance). Compares charter vs district medians.

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

Aggregate per sector bucket.

SELECT s.school_type::text, count(*), avg(m.value), percentile_cont(0.5) WITHIN GROUP (ORDER BY m.value)
FROM philly_schools s
JOIN philly_school_year_metrics m ON m.school_ulcs = s.ulcs_code
WHERE m.metric_key='attendance_persistence_rate' AND m.subgroup='ALL'
GROUP BY s.school_type;

Caveats

Lottery self-selection is the major confound — charters serve students whose families opted in. A peer-matched re-comparison shrinks the gap; the story acknowledges this.

Reproduce

Run `futurereadypa_performance.ts` loader, then the SQL above.

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