Movers

Summary

For a chosen metric and a pair of years (default: most recent and the previous year), shows the schools with the biggest year-over-year change in the metric. Sorted ascending or descending depending on whether you want gainers or decliners.

Data sources

• Database tableschools

  One row per school. The Movers query joins to schools only for the display name.

• Database tableschool_year_metrics

  Self-joined on school_dbn + metric_key + subgroup, with a.year = fromYear and b.year = toYear. Both rows must be non-suppressed and have a value.

• Loader scriptlib/queries/schools.ts

  Server function backing the page. Uses a raw SQL self-join so the delta can be computed and ordered in one pass.

Steps

1. Pick the metric (listMetricDefinitions()), then the from-year and to-year (listYearsForMetric(metricKey) — defaults are the two most recent years available for that metric).

2. Self-join the school_year_metrics table on school_dbn + metric_key + subgroup. Compute the delta (to_value − from_value) and order by it.

   SELECT a.school_dbn,
          s.name,
          a.value AS from_value,
          b.value AS to_value,
          (b.value - a.value) AS delta
     FROM school_year_metrics a
     JOIN school_year_metrics b ON a.school_dbn = b.school_dbn
                               AND a.metric_key = b.metric_key
                               AND a.subgroup   = b.subgroup
     JOIN schools s ON s.dbn = a.school_dbn
    WHERE a.metric_key = $metric
      AND a.year       = $fromYear
      AND b.year       = $toYear
      AND a.subgroup   = 'ALL'
      AND a.value IS NOT NULL
      AND b.value IS NOT NULL
      AND a.suppressed = false
      AND b.suppressed = false
      AND s.include_in_default_comparisons = true
    ORDER BY (b.value - a.value) DESC   -- direction='up' (gainers)
    -- ORDER BY (b.value - a.value) ASC -- direction='down' (decliners)
    LIMIT 100;

3. Direction. 'up' sorts descending by delta (largest gainers first); 'down' sorts ascending (largest decliners first). For LOWER_BETTER metrics (chronic_absenteeism_rate, bullying), a 'gainer' in raw delta is actually a school that got WORSE — the page doesn't auto-flip the direction for you. Read the metric definition.

4. The result is the top-N rows, not paginated. Re-run with different year pairs to see longer windows (e.g., 2018-19 → 2024-25 for post-COVID trajectory; 2021-22 → 2024-25 for post-peak recovery).

References

• NYC DOE — School Quality Snapshot (year-over-year comparisons in the official report)

Reproduce

Run the SELECT above against the database with parameters of your choice. To audit any specific row, look up the (school_dbn, metric_key, fromYear) and (school_dbn, metric_key, toYear) rows in school_year_metrics directly. The school's pages at /schools/<dbn> shows the full trajectory across all years for context, which can help you tell signal from noise.