A hundred questions this archive can answer

Generated by scripts/build_question_bank.py. Do not edit.

Every query below is run against the database on each build, and the build fails if one errors or returns nothing. 108 questions across 10 subjects.

Run any of them yourself:

curl -s -X POST https://lunenburgbudgetproject.org/api/query \
  -H 'content-type: application/json' \
  -d '{"sql": "SELECT fy, COUNT(*) FROM v_staff_roster WHERE role_category = ''paraprofessional'' GROUP BY fy"}'

Or download the database: https://lunenburgbudgetproject.org/data/lunenburg.db. Read https://lunenburgbudgetproject.org/api/schema first — it states the grain of every table and the specific ways to get a confident wrong answer out of this data.

These are not answers. The numbers move when the data does, so this shows the shape of each result — its columns and a row or two — rather than repeating figures that would go stale. Several entries exist to demonstrate a rule rather than to be interesting; those carry a note and are the ones worth copying.

The school budget

What did the district budget in total, in each year and at each stage?

SELECT fy, stage, ROUND(SUM(value)) AS total FROM budget_figure GROUP BY fy, stage ORDER BY fy, stage

Returns fy, stage, total — for example: fy=2014, stage=restated, total=16500031.0

A STAGE is not a period. proposed, settled and actual are three different documents about the same year, and mixing them is the error rule 1 exists for.

Which budget lines grew fastest across the years the archive holds?

WITH span AS (SELECT line_key, MIN(fy) AS first_fy, MAX(fy) AS last_fy FROM budget_figure WHERE stage='settled' GROUP BY line_key HAVING last_fy > first_fy) SELECT b.label, s.first_fy, s.last_fy, ROUND(a.value) AS started, ROUND(z.value) AS ended, ROUND(100.0*(z.value-a.value)/a.value,1) AS pct FROM span s JOIN budget_line b USING (line_key) JOIN budget_figure a ON a.line_key=s.line_key AND a.fy=s.first_fy AND a.stage='settled' JOIN budget_figure z ON z.line_key=s.line_key AND z.fy=s.last_fy AND z.stage='settled' WHERE a.value > 1000 ORDER BY pct DESC LIMIT 20

Returns label, first_fy, last_fy, started, ended, pct — for example: label=Business Office/Clerical, firstfy=2020, lastfy=2026, started=7952.0, ended=106500.0, pct=1239.3

Both ends are the SAME stage. A rate measured from an actual to a budget is partly growth and partly the step between them, which is rule 1.

Which lines do two documents state differently, and by how much?

SELECT label, fy, stage, ROUND(spread) AS spread FROM v_budget_disagreement WHERE spread > 0 ORDER BY spread DESC LIMIT 20

Returns label, fy, stage, spread — for example: label=Special Ed Tuitions/Private, fy=2025, stage=proposed, spread=434173.0

The documents disagree with themselves by up to 1.5%, which is larger than most variances anybody wants to measure.

What does each FY27 scenario total?

SELECT variant, ROUND(SUM(value)) AS total, COUNT(*) AS lines FROM budget_figure WHERE fy=2027 AND variant IS NOT NULL GROUP BY variant ORDER BY total DESC

Returns variant, total, lines — for example: variant=Restoration, total=28364451.0, lines=250

How many budget lines are there in each section of the budget?

SELECT section, COUNT(*) AS lines FROM budget_line GROUP BY section ORDER BY lines DESC

Returns section, lines — for example: section=None, lines=350

Which function groups hold the most budget lines?

SELECT function_group, COUNT(*) AS lines FROM budget_line WHERE function_group <> '' GROUP BY function_group ORDER BY lines DESC LIMIT 15

Returns function_group, lines — for example: function_group=2415 - H.S. Other Instr. Materials, lines=16

What is in the FY27 workbook, by column?

SELECT column_kind, COUNT(*) AS rows, ROUND(SUM(value)) AS total FROM workbook_figure WHERE row_kind='line' GROUP BY column_kind ORDER BY rows DESC

Returns column_kind, rows, total — for example: column_kind=actual, rows=748, total=69737410.0

Filter row_kind='line': the sheet's own TOTAL rows are loaded too, and summing without the filter double-counts roughly fourfold.

Which budget lines appear in the workbook but not in the line catalogue?

SELECT DISTINCT w.line_key FROM workbook_figure w LEFT JOIN budget_line b USING (line_key) WHERE b.line_key IS NULL LIMIT 20

Returns line_key — for example: line_key=total actuals & budget:

How many figures rest on a document that two sources report differently?

SELECT fy, COUNT(*) AS figures FROM budget_figure WHERE documents_disagree=1 GROUP BY fy ORDER BY fy

Returns fy, figures — for example: fy=2014, figures=2

What is the total salary line in each year, and does the stage change it?

SELECT fy, stage, total FROM total_salaries_history ORDER BY fy, stage

Returns fy, stage, total — for example: fy=2014, stage=restated, total=11044481

And total expenses?

SELECT fy, stage, total FROM total_expenses_history ORDER BY fy, stage

Returns fy, stage, total — for example: fy=2014, stage=restated, total=5146641

What is the biggest single line in the budget, in each year?

SELECT f.fy, b.label, ROUND(MAX(f.value)) AS value FROM budget_figure f JOIN budget_line b USING (line_key) WHERE f.stage='settled' GROUP BY f.fy ORDER BY f.fy DESC

Returns fy, label, value — for example: fy=2026, label=Health Insurance, value=3701195.0

How many lines does each budget document state?

SELECT doc_id, COUNT(*) AS figures, COUNT(DISTINCT fy) AS years FROM budget_figure GROUP BY doc_id ORDER BY figures DESC LIMIT 15

Returns doc_id, figures, years — for example: doc_id=sources/district-budget/text/budget-hearing-fy20-proposed-lps-budget.txt, figures=1148, years=6

Which lines exist in one scenario but not another?

SELECT line_key, COUNT(DISTINCT variant) AS variants FROM budget_figure WHERE fy=2027 AND variant IS NOT NULL GROUP BY line_key ORDER BY variants LIMIT 15

Returns line_key, variants — for example: line_key=hs audio visual supplies, variants=1

What does the FY27 workbook say each line was in FY25?

SELECT line_key, column_kind, ROUND(value) AS value FROM workbook_figure WHERE fy=2025 AND row_kind='line' ORDER BY value DESC LIMIT 20

Returns line_key, column_kind, value — for example: linekey=health insurance, columnkind=actual, value=3248744.0

Special education

How many special education paraprofessionals were budgeted, by school and year?

SELECT fy, stage, ps, es, ms, hs, total FROM sped_para_history ORDER BY fy, stage

Returns fy, stage, ps, es, ms, hs, total — for example: fy=2014, stage=restated, ps=153425, es=0, ms=162721, hs=50819, total=366965

This is the line the 12.8% escalator rests on, and it is dollars, not people.

And special education teachers?

SELECT fy, stage, ps, es, ms, hs, total FROM sped_teacher_history ORDER BY fy, stage

Returns fy, stage, ps, es, ms, hs, total — for example: fy=2017, stage=restated, ps=473861, es=307620, ms=375364, hs=380738, total=1537583

What has out-of-district tuition done, year by year?

SELECT fy, stage, private, collaborative, total FROM ood_tuition_history ORDER BY fy, stage

Returns fy, stage, private, collaborative, total — for example: fy=2014, stage=restated, private=1025404, collaborative=236285, total=1261689

How many children were placed outside the district, and where?

SELECT fy, as_of, total, collaborative, day, residential FROM placement_counts ORDER BY fy

Returns fy, as_of, total, collaborative, day, residential — for example: fy=2011, as_of=2011-03-01, total=15, collaborative=, day=13, residential=2

A count of children placed. It says nothing about which fund paid or what a placement cost, so it does not settle the money.

Do the placement counts tie to their own parts, and to the prior year?

SELECT fy, parts_tie, chain_agrees, report_says_prior_year FROM placement_counts ORDER BY fy

Returns fy, parts_tie, chain_agrees, report_says_prior_year — for example: fy=2011, partstie=yes, chainagrees=n/a, reportsaysprior_year=

What has special education transportation cost, by year?

SELECT fy, stage, system, total FROM sped_transport_history ORDER BY fy, stage

Returns fy, stage, system, total — for example: fy=2015, stage=restated, system=480536, total=480536

Does each year of the placement series agree with what the next report says of it?

SELECT fy, total, report_says_prior_year, chain_agrees FROM placement_counts ORDER BY fy

Returns fy, total, report_says_prior_year, chain_agrees — for example: fy=2011, total=15, reportsaysprioryear=, chainagrees=n/a

Two checks travel with this series: the parts sum to the total, and each year states the previous year's figure. n/a is the first year, which has nothing before it.

The town's books

What did each department spend against its budget, in the latest period held?

SELECT a.dept, a.name, l.fy, l.period, ROUND(l.revised) AS revised, ROUND(l.expended) AS expended, ROUND(l.available) AS available FROM ledger_snapshot l JOIN account a USING (account_id) WHERE a.level='department' ORDER BY l.fy DESC, l.period DESC, l.revised DESC LIMIT 20

Returns dept, name, fy, period, revised, expended, available — for example: dept=300, name=SCHOOL DEPARTMENT, fy=2026, period=9, revised=26323868.0, expended=15736641.0, available=8919184.0

Which departments are spending faster than the year is elapsing?

SELECT dept, name, fy, period, ROUND(year_elapsed,2) AS year_elapsed, ROUND(spent_share,2) AS spent_share, ROUND(pace_gap,2) AS pace_gap FROM v_burn WHERE pace_gap IS NOT NULL ORDER BY pace_gap DESC LIMIT 20

Returns dept, name, fy, period, year_elapsed, spent_share, pace_gap — for example: dept=None, name=PS TUITION, fy=2026, period=9, yearelapsed=0.75, spentshare=7.53, pace_gap=6.78

What funds does the town keep, and what restricts them?

SELECT kind, COUNT(*) AS funds FROM fund GROUP BY kind ORDER BY funds DESC

Returns kind, funds — for example: kind=None, funds=59

What moved through the special revenue funds in each year?

SELECT fund, fy, period, ROUND(opening_balance) AS opening, ROUND(revenue) AS revenue, ROUND(expenditure) AS spent, ROUND(closing_balance) AS closing FROM fund_activity ORDER BY fy DESC, revenue DESC LIMIT 20

Returns fund, fy, period, opening, revenue, spent, closing — for example: fund=2200, fy=2026, period=9, opening=None, revenue=572231.0, spent=521910.0, closing=287771.0

How many accounts are there at each level of the chart?

SELECT level, account_type, COUNT(*) AS accounts FROM account GROUP BY level, account_type ORDER BY accounts DESC

Returns level, account_type, accounts — for example: level=account, account_type=expense, accounts=692

Which grants did the district receive, and who owns them?

SELECT fy, kind, name, ROUND(amount) AS amount, owner FROM grant_award ORDER BY fy DESC, amount DESC LIMIT 20

Returns fy, kind, name, amount, owner — for example: fy=FY21-24, kind=federal, name=ESSER 3, amount=1351034.0, owner=

What does the ledger hold for each fiscal year and period?

SELECT fy, period, COUNT(*) AS rows, COUNT(DISTINCT account_id) AS accounts FROM ledger_snapshot GROUP BY fy, period ORDER BY fy, period

Returns fy, period, rows, accounts — for example: fy=2026, period=9, rows=348, accounts=346

Period 13 is the year-end close, after purchase orders are cleared. Period 12 is not the end of the year.

Which function codes can be compared between the budget and the ledger?

SELECT function_code, fy, period, ROUND(ledger_revised) AS revised, ROUND(ledger_expended) AS expended, budget_lines FROM v_function_budget_vs_ledger ORDER BY ledger_revised DESC LIMIT 20

Returns function_code, fy, period, revised, expended, budget_lines — for example: functioncode=2305, fy=2026, period=12, revised=7929717.0, expended=7867027.0, budgetlines=7

This is the level at which the two systems join. Below it they do not: the town shortens account names to ten characters.

How much did each function group budget and spend across all years?

SELECT function_group, years, ROUND(budgeted) AS budgeted, ROUND(spent) AS spent, ROUND(net) AS net, worst_year FROM variance_by_group ORDER BY ABS(net) DESC LIMIT 20

Returns function_group, years, budgeted, spent, net, worst_year — for example: functiongroup=7400 - Replace Equipment, years=5, budgeted=728952.0, spent=1277394.0, net=548442.0, worstyear=-0.0103

What went through fund 1301, and when?

SELECT fy, period, eff_date, src_meaning, COUNT(*) AS entries FROM fund_1301_cash_journal GROUP BY fy, period, eff_date, src_meaning ORDER BY fy DESC, eff_date DESC LIMIT 20

Returns fy, period, eff_date, src_meaning, entries — for example: fy=2026, period=12, effdate=2026-06-12, srcmeaning=payroll journal, entries=1

Which accounts had the largest unspent balance at the latest period?

SELECT a.name, a.fund_name, l.fy, l.period, ROUND(l.available) AS available FROM ledger_snapshot l JOIN account a USING (account_id) ORDER BY l.fy DESC, l.period DESC, l.available DESC LIMIT 20

Returns name, fund_name, fy, period, available — for example: name=SPED PRIVA, fund_name=GENERAL FUND, fy=2026, period=12, available=522629.0

How much was transferred into or out of each account?

SELECT a.name, l.fy, l.period, ROUND(l.transfers) AS transfers FROM ledger_snapshot l JOIN account a USING (account_id) WHERE l.transfers <> 0 ORDER BY ABS(l.transfers) DESC LIMIT 20

Returns name, fy, period, transfers — for example: name=TR CAP PRO, fy=2026, period=12, transfers=1240820.0

transfers is CUMULATIVE. Movement between two periods is the difference of the column, never the later value.

Which accounts are revenue rather than expenditure?

SELECT account_type, level, COUNT(*) AS accounts FROM account GROUP BY account_type, level ORDER BY accounts DESC

Returns account_type, level, accounts — for example: account_type=expense, level=account, accounts=692

Revenue rows are stored NEGATIVE, exactly as MUNIS prints them. Check account_type before doing arithmetic across types.

What share of its budget had each department used?

SELECT a.dept, a.name, l.fy, l.period, ROUND(l.pct_used,1) AS pct_used FROM ledger_snapshot l JOIN account a USING (account_id) WHERE l.pct_used IS NOT NULL ORDER BY l.pct_used DESC LIMIT 20

Returns dept, name, fy, period, pct_used — for example: dept=None, name=PS TUITION, fy=2026, period=9, pct_used=752.8

Staff on the rosters

How many people of each kind did the town print on a roster, by year?

SELECT fy, role_category, COUNT(*) AS people FROM v_staff_roster WHERE role_category <> 'unknown' GROUP BY fy, role_category ORDER BY fy, people DESC

Returns fy, role_category, people — for example: fy=2011, role_category=teacher, people=69

A count of names the town printed. It carries no FTE and no funding source, so it is not a staffing level.

How many paraprofessionals, by school and year?

SELECT fy, school, COUNT(*) AS paras FROM v_staff_roster WHERE role_category='paraprofessional' GROUP BY fy, school ORDER BY fy, paras DESC

Returns fy, school, paras — for example: fy=2011, school=primary, paras=16

How many paraprofessionals were tied to a named grade?

SELECT fy, role_grade, COUNT(*) AS paras FROM v_staff_roster WHERE role_category='paraprofessional' AND role_grade <> '' GROUP BY fy, role_grade ORDER BY fy, role_grade

Returns fy, role_grade, paras — for example: fy=2011, role_grade=2, paras=2

What did the town print as the title for a paraprofessional, year by year?

SELECT fy, role_raw, COUNT(*) AS rows FROM v_staff_roster WHERE role_category='paraprofessional' AND role_raw <> '' GROUP BY fy, role_raw ORDER BY fy, rows DESC

Returns fy, role_raw, rows — for example: fy=2011, role_raw=Tutors/Aides, rows=17

Tutor, Aide, Tutors/Aides, Paraprofessional, Para, (para), Sped Para. Five names for one job across fifteen years, which is why role_category exists.

Which roster titles could not be classified at all?

SELECT role_raw, grade_or_dept, rows FROM role_classification WHERE role_category='unknown' ORDER BY rows DESC LIMIT 20

Returns role_raw, grade_or_dept, rows — for example: roleraw=, gradeor_dept=Extended Day, rows=8

Left unknown rather than guessed. 8% of rows.

Which rule decided each classification, and how much rests on the weakest ones?

SELECT classified_by, role_category, SUM(rows) AS rows FROM role_classification GROUP BY classified_by, role_category ORDER BY rows DESC

Returns classified_by, role_category, rows — for example: classifiedby=heading-department, rolecategory=teacher, rows=1103

A rule beginning heading- read the section heading rather than a printed title, which is weaker evidence.

How many names appear on each school roster in each year?

SELECT fy, school, position, count FROM staff_roster_counts ORDER BY fy DESC, count DESC LIMIT 20

Returns fy, school, position, count — for example: fy=2025, school=turkey-hill, position=(unmapped), count=6

How many teachers were printed against a specific grade?

SELECT fy, role_grade, COUNT(*) AS teachers FROM v_staff_roster WHERE role_category='teacher' AND role_grade <> '' GROUP BY fy, role_grade ORDER BY fy DESC, role_grade

Returns fy, role_grade, teachers — for example: fy=2025, role_grade=1, teachers=5

Which schools have roster entries, and for which years?

SELECT school, COUNT(DISTINCT fy) AS years, MIN(fy) AS first, MAX(fy) AS last, COUNT(*) AS rows FROM staff_roster_entries GROUP BY school ORDER BY rows DESC

Returns school, years, first, last, rows — for example: school=high, years=15, first=2011, last=2025, rows=1070

How many counselors, nurses, psychologists and social workers, by year?

SELECT fy, role_category, COUNT(*) AS people FROM v_staff_roster WHERE role_category IN ('counselor','nurse','psychologist','social_worker','speech_therapist') GROUP BY fy, role_category ORDER BY fy, role_category

Returns fy, role_category, people — for example: fy=2011, role_category=counselor, people=9

How many people did the town print on a roster in total, by year?

SELECT fy, COUNT(*) AS names, COUNT(DISTINCT school) AS schools FROM v_staff_roster GROUP BY fy ORDER BY fy

Returns fy, names, schools — for example: fy=2011, names=216, schools=5

Which names appear across the most years?

SELECT name, COUNT(DISTINCT fy) AS years, MIN(fy) AS first, MAX(fy) AS last FROM staff_roster_entries WHERE name <> '' GROUP BY name ORDER BY years DESC, name LIMIT 20

Returns name, years, first, last — for example: name=Erin Blanchette, years=15, first=2011, last=2025

A name printed in a public annual report. It is not a claim about employment, and a roster gives no FTE.

How many administrators did each school print?

SELECT fy, school, COUNT(*) AS admins FROM v_staff_roster WHERE role_category='administrator' GROUP BY fy, school ORDER BY fy DESC, admins DESC LIMIT 20

Returns fy, school, admins — for example: fy=2025, school=middle, admins=5

Which grades appear anywhere on the rosters?

SELECT role_grade, COUNT(*) AS rows FROM v_staff_roster WHERE role_grade <> '' GROUP BY role_grade ORDER BY rows DESC

Returns role_grade, rows — for example: role_grade=K, rows=140

Athletics and fees

What did each sport cost, and how many played?

SELECT fy, season, level, sport, metric, value FROM athletics_by_sport WHERE is_numeric='1' ORDER BY fy DESC, value DESC LIMIT 20

Returns fy, season, level, sport, metric, value — for example: fy=2026, season=Spring, level=MS, sport=Track, metric=Total Athletes, value=46.0

Do the per-sport figures add up to the totals the district printed?

SELECT season, scope, metric, fy, printed, summed_from_rows, difference, ties FROM athletics_by_sport_reconciliation ORDER BY ABS(CAST(difference AS REAL)) DESC LIMIT 20

Returns season, scope, metric, fy, printed, summed_from_rows, difference, ties — for example: season=Fall, scope=ALL, metric=Full Pay, fy=2024, printed=45050.0, summedfromrows=197.0, difference=44853.0, ties=0

What has athletics cost and raised, year by year?

SELECT fy, side, item, amount, basis FROM athletics_history ORDER BY fy DESC, side

Returns fy, side, item, amount, basis — for example: fy=2026, side=general, item=Athletic Coaches, amount=159444.0, basis=budget

What has the athletic fee been, by year and tier?

SELECT fy, school_year, level, item, amount, unit, verified FROM athletic_fee_schedule ORDER BY fy DESC, level

Returns fy, school_year, level, item, amount, unit, verified — for example: fy=2027, schoolyear=2026-27, level=ANY, item=familycap, amount=1500.00, unit=not established, verified=source not held

Which rates does this project know about, and which does it use?

SELECT fy, category, item, value, value_type, set_on FROM rate_register ORDER BY fy DESC, category LIMIT 20

Returns fy, category, item, value, value_type, set_on — for example: fy=2029, category=contractcola, item=cost-of-living adjustment to the salary scale, value=2.5, valuetype=percent, set_on=

It deliberately includes rates the model does NOT use, and the ones that cannot be stated at all.

Which rates were set by a document we hold, and which were not?

SELECT category, COUNT(*) AS rates, SUM(CASE WHEN source_file <> '' THEN 1 ELSE 0 END) AS with_a_document FROM rate_register GROUP BY category ORDER BY rates DESC

Returns category, rates, with_a_document — for example: category=athleticfee, rates=28, witha_document=24

The annual town reports

What appropriations did each annual report print, and are the rows checked?

SELECT edition, status, COUNT(*) AS rows FROM report_appropriations GROUP BY edition, status ORDER BY edition, rows DESC

Returns edition, status, rows — for example: edition=FY2011, status=check failed, rows=298

ALWAYS split on status. checked, check failed and no check are three different claims and nothing may be aggregated across them.

What did the town pay in gross wages, and to how many people?

SELECT edition, status, COUNT(*) AS rows FROM report_gross_wages GROUP BY edition, status ORDER BY edition

Returns edition, status, rows — for example: edition=FY2011, status=no check, rows=326

What debt has the town carried?

SELECT edition, COUNT(*) AS rows, SUM(CASE WHEN status='checked' THEN 1 ELSE 0 END) AS checked FROM report_debt GROUP BY edition ORDER BY edition

Returns edition, rows, checked — for example: edition=FY2011, rows=20, checked=0

What capital projects did the reports list?

SELECT edition, label, status FROM report_capital_projects WHERE label <> '' ORDER BY edition DESC LIMIT 20

Returns edition, label, status — for example: edition=FY2025, label=3006 8/2STM & 6/6 STM Dev Cem, status=no check

What is in the trust funds?

SELECT edition, COUNT(*) AS rows FROM report_trust_funds GROUP BY edition ORDER BY edition

Returns edition, rows — for example: edition=FY2011, rows=66

What did the town value its property at?

SELECT edition, label, status FROM report_valuation WHERE label <> '' ORDER BY edition DESC LIMIT 20

Returns edition, label, status — for example: edition=FY2019, label=_Personal Property, status=no check

What enrollment and MCAS results were printed?

SELECT edition, COUNT(*) AS rows FROM report_enrollment_mcas GROUP BY edition ORDER BY edition

Returns edition, rows — for example: edition=FY2011, rows=5

What did the town assess for Monty Tech?

SELECT fy, v1 AS appropriated, column_meaning, status FROM report_appropriations WHERE label = 'Monty Tech Assessment' AND table_family = 'accountant-schedule' ORDER BY fy DESC LIMIT 20

Returns fy, appropriated, column_meaning, status — for example: fy=2023, appropriated=1054376.00, column_meaning=not established -- this page states no identity that fixes which printed column is which, status=check failed

What is in the Monty Tech pages of the annual town report?

SELECT edition, label, status FROM report_monty_tech WHERE label <> '' ORDER BY edition DESC LIMIT 20

Returns edition, label, status — for example: edition=FY2017, label=Chapter 70 13,764,000, status=no check

How many births, deaths and marriages were recorded?

SELECT edition, label, status FROM report_vital_records WHERE label <> '' ORDER BY edition DESC LIMIT 20

Returns edition, label, status — for example: edition=FY2025, label=Annual Town Meeting - May, status=no check

Who held town office, and when?

SELECT edition, label FROM report_officials WHERE label <> '' ORDER BY edition DESC LIMIT 20

Returns edition, label — for example: edition=FY2023, label=Unemployment

What did each department report doing?

SELECT edition, COUNT(*) AS rows FROM report_dept_activity GROUP BY edition ORDER BY edition

Returns edition, rows — for example: edition=FY2011, rows=3

What receipts did the town record, and from what source?

SELECT fy, source, amount, status FROM annual_report_receipts ORDER BY fy DESC, CAST(amount AS REAL) DESC LIMIT 20

Returns fy, source, amount, status — for example: fy=2023, source=TRAILER PAARE, amount=14124.00, status=no check

What tables does each annual report contain?

SELECT fy, [table], pages, figure_rows, checkable FROM annual_report_contents ORDER BY fy DESC, figure_rows DESC LIMIT 20

Returns fy, table, pages, figure_rows, checkable — for example: fy=2025, table=enterprise, pages=25,35,53,64,67,140,142-143,152, figure_rows=96, checkable=no

Which tables in the reports were read, and which are still uncaptured?

SELECT dataset, COUNT(*) AS editions, SUM(CASE WHEN extractable='yes' THEN 1 ELSE 0 END) AS extractable FROM extraction_plan GROUP BY dataset ORDER BY editions DESC LIMIT 20

Returns dataset, editions, extractable — for example: dataset=enrollment_mcas, editions=94, extractable=0

What did the survey find on each page of each report?

SELECT fy, mode, COUNT(*) AS pages, SUM(money) AS money_tokens FROM annual_report_survey GROUP BY fy, mode ORDER BY fy, pages DESC

Returns fy, mode, pages, money_tokens — for example: fy=2011, mode=ocr, pages=101, money_tokens=2304

What is catalogued in each report, by printed heading?

SELECT fy, printed_heading, pages, grain FROM annual_report_catalogue WHERE printed_heading <> '' ORDER BY fy DESC LIMIT 20

Returns fy, printed_heading, pages, grain — for example: fy=2025, printed_heading=LUNENBURG PROFILE, pages=9, grain=One statistic

Where did the extraction find something it could not reconcile?

SELECT fy, edition, [table], kind, detail FROM report_anomalies ORDER BY fy DESC LIMIT 20

Returns fy, edition, table, kind, detail — for example: fy=2025, edition=FY2025, table=FY2026 program of capital projects, Option 1, kind=announced but absent, detail=CPC rankings are not contiguous — 14, 18 and 21 are absent from the list, so it is a filtered ranking, not a complete one.

An anomaly is a finding about our reading of the page as much as about the page.

Which special revenue funds appear in the reports, and in which years?

SELECT fy, [group], COUNT(*) AS rows FROM special_revenue_funds GROUP BY fy, [group] ORDER BY fy DESC, rows DESC LIMIT 20

Returns fy, group, rows — for example: fy=2025, group=, rows=177

How many figures did each annual report yield, and how many were checked?

SELECT edition, COUNT(*) AS rows, SUM(CASE WHEN status='checked' THEN 1 ELSE 0 END) AS checked FROM report_appropriations GROUP BY edition ORDER BY edition

Returns edition, rows, checked — for example: edition=FY2011, rows=298, checked=0

Which report tables print a total we can reconcile to?

SELECT fy, [table], printed_total, checkable FROM annual_report_contents WHERE printed_total <> '' ORDER BY fy DESC LIMIT 20

Returns fy, table, printed_total, checkable — for example: fy=2025, table=capitalproject, printedtotal=TOTAL CAPITAL PROJECT FUND BALANCE=3,096,913.16, checkable=yes

How many pages of each report carried figures at all?

SELECT fy, COUNT(*) AS pages, SUM(CASE WHEN money > 0 THEN 1 ELSE 0 END) AS with_money FROM annual_report_survey GROUP BY fy ORDER BY fy

Returns fy, pages, with_money — for example: fy=2011, pages=101, with_money=44

What kinds of anomaly did the extraction find, and how often?

SELECT kind, COUNT(*) AS occurrences FROM report_anomalies GROUP BY kind ORDER BY occurrences DESC

Returns kind, occurrences — for example: kind=unreadable / OCR damage, occurrences=167

Revenue, tax base and free cash

What free cash has each town certified, and from what?

SELECT town, year, line, amount, role FROM free_cash_proof ORDER BY year DESC, town LIMIT 20

Returns town, year, line, amount, role — for example: town=Ayer, year=2025, line=Free Cash Certified Prior Year, amount=3261808.00, role=prioryearcertified

Absolute dollars with no denominator, so they do not compare between towns of different size. The composition does compare, because a share has no size.

How does Lunenburg free cash compare with its neighbours, by composition?

SELECT town, year, line, amount FROM free_cash_proof WHERE role='component' ORDER BY year DESC, town LIMIT 20

Returns town, year, line, amount — for example: town=Ayer, year=2025, line=Revenue Deficits, amount=0.00

What has the town spent on capital, and where did the money come from?

SELECT fy, total, free_cash, taxation, unexpended_prior_year_capital, other FROM capital_funding_history ORDER BY fy

Returns fy, total, free_cash, taxation, unexpended_prior_year_capital, other — for example: fy=2017, total=619475.00, freecash=250000.00, taxation=349023.05, unexpendedprioryearcapital=20451.95, other=0.00

What is in the FY27 capital plan, and what is funded?

SELECT rank, dept, project, cost, funded, funding FROM capital_plan_fy27 ORDER BY rank

Returns rank, dept, project, cost, funded, funding — for example: rank=1, dept=DPW, project=Flat Hill Rd Bridge Completion (Engineering and Construction), cost=350000.00, funded=yes, funding=freecashor_taxation

How do state measures compare Lunenburg with other districts?

SELECT district, fy, measure, value FROM dese_measure WHERE district <> '' ORDER BY fy DESC LIMIT 20

Returns district, fy, measure, value — for example: district=Harvard, fy=2025, measure=In-District FTE Pupils, value=1016.4

Which DESE measures reconcile against the printed totals, and which do not?

SELECT measure, COUNT(*) AS rows, SUM(CASE WHEN reconciles='1' THEN 1 ELSE 0 END) AS reconciling FROM dese_measure GROUP BY measure ORDER BY rows DESC LIMIT 15

Returns measure, rows, reconciling — for example: measure=Total In-District Expenditures, rows=102, reconciling=0

How has free cash moved for Lunenburg specifically?

SELECT year, line, amount, role FROM free_cash_proof WHERE town='Lunenburg' ORDER BY year DESC, line

Returns year, line, amount, role — for example: year=2025, line=Add Actual Revenue Received but not Estimated (CL#7), amount=7139.00, role=component

Which towns does the free cash comparison cover?

SELECT town, COUNT(DISTINCT year) AS years, MIN(year) AS first, MAX(year) AS last FROM free_cash_proof GROUP BY town ORDER BY town

Returns town, years, first, last — for example: town=Ayer, years=5, first=2021, last=2025

What measures does the state publish about this district?

SELECT measure, COUNT(*) AS rows, MIN(fy) AS first, MAX(fy) AS last FROM dese_measure GROUP BY measure ORDER BY rows DESC LIMIT 15

Returns measure, rows, first, last — for example: measure=Total In-District Expenditures, rows=102, first=2009, last=2025

Votes and elections

What has the town been asked to fund, and did it agree?

SELECT date, election, question, type, amount, yes, no, total FROM ballot_questions ORDER BY date DESC

Returns date, election, question, type, amount, yes, no, total — for example: date=2025-05, election=Annual Town Meeting, question=ARTICLE 11 (Citizens Petition), type=Proposition 2½ override, amount=2099337, yes=, no=, total=

What turnout did each ballot question draw?

SELECT date, question, total, registered, ROUND(100.0*total/registered,1) AS turnout_pct FROM ballot_questions WHERE registered > 0 ORDER BY date DESC

Returns date, question, total, registered, turnout_pct — for example: date=2014-01-11, question=QUESTION 1. DEBT EXCLUSION, total=1957, registered=7059, turnout_pct=27.7

What election results did the annual reports print?

SELECT edition, COUNT(*) AS rows FROM report_elections GROUP BY edition ORDER BY edition

Returns edition, rows — for example: edition=FY2011, rows=16

Provenance, and what is not established

Where did a figure in this dataset come from?

SELECT dataset, edition, document, publisher_label, sha256 FROM dataset_document ORDER BY dataset, edition LIMIT 20

Returns dataset, edition, document, publisher_label, sha256 — for example: dataset=annual-report-catalogue, edition=FY2011, document=4117-fy-2011-annual-town-report.pdf, publisher_label=FY 2011 Annual Town Report, sha256=551f9edd75d051ae4d863a5d5c13925c0b9a2f93459e63268c507c5226398b81

This is the join that gives an annual-report row an address. Use it in any query whose answer somebody might want to check.

Which documents does the archive hold, and how were they obtained?

SELECT source_type, basis, COUNT(*) AS documents FROM document GROUP BY source_type, basis ORDER BY documents DESC

Returns source_type, basis, documents — for example: source_type=primary, basis=None, documents=317

Which documents no longer open at the publisher, or no longer match our copy?

SELECT doc_id, link_state, copy_state, url FROM document WHERE copy_state NOT IN ('identical','') OR link_state NOT IN ('200','') LIMIT 20

Returns doc_id, link_state, copy_state, url — for example: docid=sources/district-budget/docs/fy26-superintendent-39-s-proposed-budget-2-26-25-updated-3-12-25.pdf, linkstate=200, copystate=reflowed, url=https://drive.google.com/file/d/1sqlWrNsH43AE8JqAAnqNPpOt1mi3gCDU/view?usp=drivelink

Which documents have no upstream address at all?

SELECT doc_id, source_type FROM document WHERE url IS NULL OR url='' LIMIT 20

Returns doc_id, source_type — for example: docid=sources/budget-workbooks/fy27-budget-projection-2-24-26.xlsx, sourcetype=restatement

A gap on our side, not the town's: they were gathered before the mirror existed and nobody wrote down where they came from.

Where do two sources state the same budget line differently?

SELECT label, fy, stage, source, value, is_kept FROM line_history_disagreements ORDER BY fy DESC, label LIMIT 20

Returns label, fy, stage, source, value, is_kept — for example: label=Admin Tech Contracted Services, fy=2027, stage=proposed, source=fy27-budget-projections-as-of-2-16-26-with-restorations.txt, value=138202, is_kept=0

How much of each dataset has been checked against the page it came from?

SELECT dataset, reconciled, partial, SUM(CAST(rows AS INTEGER)) AS rows FROM dataset_document GROUP BY dataset, reconciled, partial ORDER BY rows DESC LIMIT 20

Returns dataset, reconciled, partial, rows — for example: dataset=report-appropriations, reconciled=0, partial=0, rows=4665

Which figures has somebody stated publicly, and on what basis?

SELECT fy, metric, amount, stated_by, stated_on, basis FROM stated_figure ORDER BY fy DESC

Returns fy, metric, amount, stated_by, stated_on, basis — for example: fy=2025, metric=schoolsurplus, amount=603885.97, statedby=Business Administrator (Mr. McNamara) to the School Committee, stated_on=2025-09-17, basis=close

Which budget lines have no ledger account mapped to them?

SELECT COUNT(*) AS budget_lines, (SELECT COUNT(*) FROM crosswalk) AS mapped FROM budget_line

Returns budget_lines, mapped — for example: budget_lines=688, mapped=0

The crosswalk is empty ON PURPOSE. District lines are named, MUNIS rows are coded, and no published document maps one to the other. Budget-to-actual at line level cannot be answered from this data.

Which pages of the annual reports have columns we could not establish?

SELECT edition, COUNT(*) AS rows FROM report_appropriations WHERE column_meaning LIKE 'not established%' GROUP BY edition ORDER BY rows DESC

Returns edition, rows — for example: edition=FY2014, rows=90

v1 is an ordinal -- the first column of THIS page that held figures -- not a column name. Read column_meaning before summing anything.

How many rows of each report table failed their own check?

SELECT 'appropriations' AS t, status, COUNT(*) AS rows FROM report_appropriations GROUP BY status UNION ALL SELECT 'gross_wages', status, COUNT(*) FROM report_gross_wages GROUP BY status ORDER BY t, rows DESC

Returns t, status, rows — for example: t=appropriations, status=check failed, rows=4530

Which documents were obtained by records request rather than published?

SELECT source_type, COUNT(*) AS documents FROM document GROUP BY source_type ORDER BY documents DESC

Returns source_type, documents — for example: source_type=primary, documents=317

What basis does each document have for the figures it prints?

SELECT basis, COUNT(*) AS documents FROM document WHERE basis IS NOT NULL GROUP BY basis ORDER BY documents DESC

Returns basis, documents — for example: basis=budget, documents=173

ledger means a figure exists because a transaction did. restatement means a prior year re-presented by the party that spent it. They are not interchangeable.

Which datasets have a document for every edition, and which do not?

SELECT dataset, COUNT(*) AS editions, SUM(CASE WHEN document <> '' THEN 1 ELSE 0 END) AS with_a_document FROM dataset_document GROUP BY dataset ORDER BY editions DESC

Returns dataset, editions, with_a_document — for example: dataset=annual-report-catalogue, editions=16, withadocument=16

How many rows of each dataset came off each page?

SELECT dataset, edition, pages, rows FROM dataset_document ORDER BY CAST(rows AS INTEGER) DESC LIMIT 20

Returns dataset, edition, pages, rows — for example: dataset=report-gross-wages, edition=FY2025, pages=177,178,179,180,181,182, rows=607

Finding your way

What tables are there, and how big are they?

SELECT name FROM sqlite_master WHERE type IN ('table','view') ORDER BY type, name

Returns name — for example: name=account

What fiscal years does the archive cover, per dataset?

SELECT dataset, MIN(edition) AS first, MAX(edition) AS last, COUNT(*) AS editions FROM dataset_document GROUP BY dataset ORDER BY dataset

Returns dataset, first, last, editions — for example: dataset=annual-report-catalogue, first=FY2011, last=FY2025, editions=16

What does one fiscal period mean?

SELECT period, label, months_elapsed, is_final FROM fiscal_period ORDER BY period

Returns period, label, months_elapsed, is_final — for example: period=1, label=July, monthselapsed=1.0, isfinal=0