School in numbers — dashboards for principals and teachers

# 🎒 School in numbers

Five official MIUR and INVALSI sources turned into dashboards for the people
who run schools or teach in them: how many students you WILL have (not just
how many you have), where the pipeline leaks, how test scores move, what
buildings classes are held in, and with what staff. The data connects on its
own via municipality, region or province. None of your data leaves the
device.

:::::page{title="My municipality" icon="🏫"}

## Your territory at a glance

Search the municipality by name and **pick it from the menu**: the tab
updates on its own.

::input[cercaComune]{value="Bologna" placeholder="Search the municipality by name…"}

::od-query{into="comuniTrovati" sql="SELECT codice_istat AS codice_istat, comune || ' (' || sigla || ')' AS etichetta FROM istat_confini_comuni WHERE upper(strip_accents(comune)) LIKE '%' || upper(strip_accents('{#cercaComune}')) || '%' ORDER BY length(comune), comune" limit="20"}

::select-from[comune]{from="comuniTrovati" label="etichetta" value="codice_istat"}

::od-query{into="kpiAlunni" sql="SELECT a.alunni AS alunni, round(100.0*(a.alunni-b.alunni)/b.alunni,1) AS variazione FROM (SELECT sum(alunni) AS alunni FROM iscrizioni_scolastiche WHERE codice_istat='{#comune}' AND anno_scolastico=(SELECT max(anno_scolastico) FROM iscrizioni_scolastiche)) a, (SELECT sum(alunni) AS alunni FROM iscrizioni_scolastiche WHERE codice_istat='{#comune}' AND anno_scolastico=(SELECT min(anno_scolastico) FROM iscrizioni_scolastiche)) b"}

::od-query{into="kpiScuole" sql="SELECT (SELECT count(*) FROM scuole WHERE codice_istat='{#comune}') AS scuole, (SELECT count(*) FROM edilizia_scolastica WHERE codice_istat='{#comune}') AS edifici"}

::od-query{into="kpiEdilizia" sql="SELECT round(avg(anno_costruzione)) AS anno_medio, count(*) FILTER (WHERE classificazione_sismica<=2) AS sismica_alta FROM edilizia_scolastica WHERE codice_istat='{#comune}' AND (anno_costruzione IS NULL OR anno_costruzione BETWEEN 1500 AND 2026)"}

::::grid{cols="3"}
:::cards{path="kpiAlunni" search="false"}
🎓 **{alunni}** students today — 10-year change **{variazione}%**
:::
---
:::cards{path="kpiScuole" search="false"}
🏫 **{scuole}** state schools in **{edifici}** registered buildings
:::
---
:::cards{path="kpiEdilizia" search="false"}
🏛️ Average construction year **{anno_medio}** — **{sismica_alta}** buildings in seismic zone 1-2
:::
::::

## How many students you'll have: the forecast

The number you need for planning staff and classes: the enrollment trend
extended forward. Move the slider for the horizon (the first run downloads
the compute engine, then it updates on its own).

::od-query{into="trendIscrizioni" sql="SELECT substr(anno_scolastico,1,4)::INTEGER AS anno, sum(alunni) AS alunni FROM iscrizioni_scolastiche WHERE codice_istat='{#comune}' GROUP BY 1 ORDER BY 1"}

::range[anniPrev]{min="1" max="5" value="3" legend="Forecast years"}

:::select{id="algoPrev" placeholder="Linear trend (default)"}
- ARIMA/SARIMA
- Holt-Winters (ETS)
:::

::ml-forecast{data="trendIscrizioni" x="anno" y="alunni" horizon="#anniPrev" model="#algoPrev" into="trendPrev"}

::chart-line{data="trendPrev" x="anno" y="alunni,previsione" height="16rem"}

::od-query{into="gradoComune" sql="SELECT grado AS grado, alunni AS alunni FROM iscrizioni_scolastiche WHERE codice_istat='{#comune}' AND anno_scolastico=(SELECT max(anno_scolastico) FROM iscrizioni_scolastiche) ORDER BY grado"}

:::table{path="gradoComune" headers="Grade,Students (latest year)" search="false" pagesize="5"}
{grado} | {alunni}
:::

## The buildings, by construction period

::od-query{into="periodiComune" sql="SELECT periodo_costruzione AS periodo, count(*) AS edifici FROM edilizia_scolastica WHERE codice_istat='{#comune}' AND periodo_costruzione IS NOT NULL GROUP BY 1 ORDER BY min(coalesce(anno_costruzione,2100))"}

::chart-bar{data="periodiComune" x="periodo" y="edifici" height="16rem"}

## The municipality's schools

::od-query{into="scuoleComune" sql="SELECT nome AS nome, grado AS grado, indirizzo AS indirizzo FROM scuole WHERE codice_istat = '{#comune}' ORDER BY grado, nome" limit="300"}

:::table{path="scuoleComune" headers="School,Grade,Address" pagesize="10"}
{nome} | {grado} | {indirizzo}
:::

Enrollment covers state schools only (kindergarten excluded by the source);
the forecast is a linear trend, not an oracle — the R² in the status says
how much of the series the trend explains. Seismic classification runs from
1 (highest risk) to 4.

:::::

:::::page{title="Drop-out" icon="📉"}

## School drop-out, year by year

The national series from school year 2013/2014: steadily falling for lower
secondary and the cycle transition, with a rebound in the last period for
upper secondary.

::od-query{into="trendNazionale" sql="SELECT anno_frequenza AS anno, max(CASE WHEN grado='I grado' THEN tasso_abbandono_perc END) AS medie, max(CASE WHEN grado='passaggio cicli' THEN tasso_abbandono_perc END) AS passaggio, max(CASE WHEN grado='II grado' THEN tasso_abbandono_perc END) AS superiori FROM dispersione_scolastica WHERE regione = 'ITALIA' GROUP BY anno_frequenza ORDER BY anno_frequenza"}

::chart-line{data="trendNazionale" x="anno" y="medie,passaggio,superiori" height="16rem"}

## The regional comparison — click a bar

Upper-secondary drop-out rate, latest available period. **Click a region**
to filter the historical detail below (second click removes the filter —
it's the cross-filtering of real BI tools).

::od-query{into="rankingRegioni" sql="SELECT regione AS regione, tasso_abbandono_perc AS tasso FROM dispersione_scolastica WHERE grado = 'II grado' AND anno_frequenza = (SELECT max(anno_frequenza) FROM dispersione_scolastica WHERE regione != 'ITALIA' AND grado = 'II grado') AND regione != 'ITALIA' ORDER BY tasso DESC" limit="20"}

::od-query{into="dispDettaglio" sql="SELECT regione AS regione, grado AS grado, periodo AS periodo, tasso_abbandono_perc AS tasso FROM dispersione_scolastica WHERE regione != 'ITALIA' ORDER BY regione, grado, anno_frequenza" limit="300"}

::::dashboard{path="rankingRegioni"}
::chart-bar{data="rankingRegioni" x="regione" y="tasso" height="20rem"}

:::table{path="dispDettaglio" headers="Region,Grade,Period,Rate %" pagesize="9"}
{regione} | {grado} | {periodo} | {tasso}
:::
::::

Valle d'Aosta and Trentino-Alto Adige are absent from the ministry source
for the whole series; regional detail covers the periods from 2015/2016
onwards (the Ministry doesn't republish earlier years with each new
edition).

:::::

:::::page{title="INVALSI" icon="📊"}

## Your region, test by test

Enter a region (Italian name): e.g. `Lombardia`, `Campania`, `Sicilia`,
`Veneto`.

::input[regione]{value="Lombardia" placeholder="Region name (Italian)"}

**Italian and Mathematics in 5th-grade primary**, year by year:

::od-query{into="trendInvalsi" sql="SELECT anno AS anno, max(CASE WHEN materia='Italiano' THEN round(punteggio_medio,1) END) AS italiano, max(CASE WHEN materia='Matematica' THEN round(punteggio_medio,1) END) AS matematica FROM invalsi_regionale WHERE territorio = '{#regione}' AND grado = '5ª primaria' GROUP BY anno ORDER BY anno"}

::chart-line{data="trendInvalsi" x="anno" y="italiano,matematica" height="16rem"}

**Mathematics along the pipeline** — 5th primary, 8th grade, final year of
upper secondary:

::od-query{into="trendGradi" sql="SELECT anno AS anno, max(CASE WHEN grado='5ª primaria' THEN round(punteggio_medio,1) END) AS primaria, max(CASE WHEN grado='3ª secondaria I grado' THEN round(punteggio_medio,1) END) AS media, max(CASE WHEN grado='5ª secondaria II grado' THEN round(punteggio_medio,1) END) AS superiore FROM invalsi_regionale WHERE territorio = '{#regione}' AND materia = 'Matematica' GROUP BY anno ORDER BY anno"}

::chart-line{data="trendGradi" x="anno" y="primaria,media,superiore" height="16rem"}

## The regional comparison — click a bar

Mathematics, 5th-grade primary, latest year. **Click a region** to see all
its tests in the detail below.

::od-query{into="rankingInvalsi" sql="SELECT territorio AS regione, round(punteggio_medio,1) AS punteggio FROM invalsi_regionale WHERE livello = 'regione' AND grado = '5ª primaria' AND materia = 'Matematica' AND anno = (SELECT max(anno) FROM invalsi_regionale) ORDER BY punteggio DESC" limit="20"}

::od-query{into="invDettaglio" sql="SELECT territorio AS regione, grado AS grado, materia AS materia, round(punteggio_medio,1) AS punteggio FROM invalsi_regionale WHERE livello = 'regione' AND anno = (SELECT max(anno) FROM invalsi_regionale) ORDER BY territorio, grado, materia" limit="300"}

::::dashboard{path="rankingInvalsi"}
::chart-bar{data="rankingInvalsi" x="regione" y="punteggio" height="20rem"}

:::table{path="invDettaglio" headers="Region,Grade,Subject,Score" pagesize="10"}
{regione} | {grado} | {materia} | {punteggio}
:::
::::

SAMPLE-based data (a survey on a sample of schools), WLE scale with national
mean ≈200: useful for comparisons over time and across territories, not for
a single school. School year 2019/2020 missing (no tests, COVID).

:::::

:::::page{title="Staffing" icon="👩‍🏫"}

## Your province's teachers

Enter a province (Italian name): e.g. `Milano`, `Roma`, `Napoli`, `Torino`.

::input[provincia]{value="Milano" placeholder="Province name (Italian)"}

::od-query{into="kpiDocenti" sql="SELECT a.docenti AS docenti, round(100.0*(a.docenti-b.docenti)/b.docenti,1) AS variazione FROM (SELECT sum(totale) AS docenti FROM personale_scuola WHERE provincia='{#provincia}' AND categoria='Docente' AND anno_scolastico=(SELECT max(anno_scolastico) FROM personale_scuola)) a, (SELECT sum(totale) AS docenti FROM personale_scuola WHERE provincia='{#provincia}' AND categoria='Docente' AND anno_scolastico=(SELECT min(anno_scolastico) FROM personale_scuola)) b"}

::od-query{into="kpiRapporto" sql="SELECT round(a.alunni::DOUBLE / p.docenti, 1) AS rapporto FROM (SELECT sum(alunni) AS alunni FROM iscrizioni_scolastiche WHERE provincia = '{#provincia}' AND anno_scolastico = (SELECT max(anno_scolastico) FROM iscrizioni_scolastiche)) a, (SELECT sum(totale) AS docenti FROM personale_scuola WHERE provincia = '{#provincia}' AND categoria = 'Docente' AND anno_scolastico = (SELECT max(anno_scolastico) FROM personale_scuola)) p"}

::od-query{into="kpiSostegno" sql="SELECT round(100.0*(sum(totale) FILTER (WHERE tipo_posto='Sostegno'))/sum(totale),1) AS perc_sostegno, (SELECT sum(totale) FROM personale_scuola WHERE provincia='{#provincia}' AND categoria='ATA' AND anno_scolastico=(SELECT max(anno_scolastico) FROM personale_scuola)) AS ata FROM personale_scuola WHERE provincia='{#provincia}' AND categoria='Docente' AND anno_scolastico=(SELECT max(anno_scolastico) FROM personale_scuola)"}

::::grid{cols="3"}
:::cards{path="kpiDocenti" search="false"}
👩‍🏫 **{docenti}** tenured teachers — 10-year change **{variazione}%**
:::
---
:::cards{path="kpiRapporto" search="false"}
👥 **{rapporto}** students per teacher (enrollment ÷ tenured staff, latest year)
:::
---
:::cards{path="kpiSostegno" search="false"}
🤝 **{perc_sostegno}%** special-needs support posts — **{ata}** administrative/technical (ATA) staff
:::
::::

## The staff you'll have: the projection

The same tool as the enrollment page, applied to tenured teachers: the trend
extended forward, to reason about retirements and needs.

::od-query{into="trendDocenti" sql="SELECT substr(anno_scolastico,1,4)::INTEGER AS anno, sum(totale) AS docenti FROM personale_scuola WHERE provincia = '{#provincia}' AND categoria = 'Docente' GROUP BY 1 ORDER BY 1"}

::range[anniOrg]{min="1" max="5" value="3" legend="Projection years"}

:::select{id="algoOrg" placeholder="Linear trend (default)"}
- ARIMA/SARIMA
- Holt-Winters (ETS)
:::

::ml-forecast{data="trendDocenti" x="anno" y="docenti" horizon="#anniOrg" model="#algoOrg" into="trendDocPrev"}

::chart-line{data="trendDocPrev" x="anno" y="docenti,previsione" height="16rem"}

::::grid{cols="2"}
**By age band** (the retirement wave shows up here):

::od-query{into="etaDocenti" sql="SELECT fascia_eta AS fascia, sum(totale) AS docenti FROM personale_scuola WHERE provincia = '{#provincia}' AND categoria = 'Docente' AND anno_scolastico = (SELECT max(anno_scolastico) FROM personale_scuola) GROUP BY fascia_eta ORDER BY min(CASE fascia_eta WHEN 'Fino a 34' THEN 1 WHEN 'Tra 35 e 44' THEN 2 WHEN 'Tra 45 e 54' THEN 3 ELSE 4 END)"}

::chart-bar{data="etaDocenti" x="fascia" y="docenti" height="15rem"}
---
**By school grade**:

::od-query{into="gradoDocenti" sql="SELECT grado AS grado, sum(totale) AS docenti FROM personale_scuola WHERE provincia = '{#provincia}' AND categoria = 'Docente' AND anno_scolastico = (SELECT max(anno_scolastico) FROM personale_scuola) GROUP BY grado ORDER BY grado"}

::chart-bar{data="gradoDocenti" x="grado" y="docenti" height="15rem"}
::::

Tenured staff only: temporary teachers aren't in the source. The
student-teacher ratio crosses two datasets (state enrollment without
kindergarten ÷ tenured staff with kindergarten): a trend indicator, not an
official staffing figure.

:::::

:::::page{title="The map" icon="🗺️"}

## School Italy, colored by the data

**Mathematics in 5th-grade primary** (INVALSI score, latest year) — hover
for the value:

::od-query{into="mappaInvalsi" sql="SELECT r.regione AS regione, r.geojson AS geojson, round(i.punteggio_medio) AS punteggio FROM istat_confini_regioni r JOIN invalsi_regionale i ON i.regione = r.regione WHERE i.grado='5ª primaria' AND i.materia='Matematica' AND i.anno=(SELECT max(anno) FROM invalsi_regionale)" limit="30"}

:::map{path="mappaInvalsi" geojson="geojson" fill="punteggio" height="26rem"}
**{regione}** — score {punteggio}
:::

**Upper-secondary drop-out** (rate %, latest period) — here the dark color
IS the problem:

::od-query{into="mappaDispersione" sql="SELECT r.regione AS regione, r.geojson AS geojson, d.tasso_abbandono_perc AS tasso FROM istat_confini_regioni r JOIN dispersione_scolastica d ON d.regione = r.regione WHERE d.grado='II grado' AND d.anno_frequenza=(SELECT max(anno_frequenza) FROM dispersione_scolastica WHERE regione != 'ITALIA' AND grado='II grado')" limit="30"}

:::map{path="mappaDispersione" geojson="geojson" fill="tasso" height="26rem"}
**{regione}** — drop-out {tasso}%
:::

Uncolored regions have no data in the source (Valle d'Aosta and
Trentino-Alto Adige for drop-out; INVALSI publishes Trentino-Alto Adige as
two autonomous provinces, not as one region).

:::::

:::::page{title="Explore" icon="🔍"}

## The pivot: ask your own questions by dragging

Enrollment by region, grade and year (600 combinations): **drag the
columns** to group, switch chart type, filter — like a pivot table, even in
Use mode.

::od-query{into="pivotIscrizioni" sql="SELECT regione AS regione, grado AS grado, substr(anno_scolastico,1,4)::INTEGER AS anno, sum(alunni) AS alunni FROM iscrizioni_scolastiche WHERE regione IS NOT NULL AND codice_istat IS NOT NULL GROUP BY 1,2,3 ORDER BY 1,2,3" limit="1000"}

:::explore{path="pivotIscrizioni" view="bar" group-by="regione" columns="alunni" height="30rem"}
:::

## Or just ask

With an AI engine configured (AI menu at the top), natural-language
questions on the same data: "how many students does Lombardia have in
2024?", "the 5 regions that lost the most students".

::ai-query{data="pivotIscrizioni" into="rispostaAi"}

:::table{path="rispostaAi" pagesize="10"}
{regione} {grado} {anno} {alunni} {valore}
:::

:::::

---

The data is official and open: **enrollment, school buildings and staff**
from MIUR — School Data Portal (Open Data IODL 2.0), **school drop-out**
from MIM — Statistics Office (free reuse with attribution, extracted from
the official PDFs via text-layer coordinate parsing, never a vision model),
**regional INVALSI results** from INVALSI — Statistical Service (CC BY 4.0
IT), **boundaries** ISTAT. They connect on their own via municipality,
region or province. None of your data leaves the device.