evaluation_queries.md

Local First Education Data Framework — Evaluation Query Bank

A set of 15 real-world, moderately complex queries for manual evaluation. Inspired by the California School Dashboard and common school-data analytics questions. Each query exercises joins, filtering, aggregation, subgroup comparison, or ranking across one or more of the five domains:

• Students (demographics, program flags)
• Enrollment (school-year, grade-level, school assignments)
• Attendance (absence counts, chronic absenteeism)
• Discipline (incidents, types, dates)
• Grades (course grades, GPA, subject areas)

---

Query 1: Chronic absenteeism by grade level

For the 2023-2024 school year, what percent of students at Lincoln Elementary are chronically absent, broken down by grade level?

Domains: attendance + enrollment Expected shape: one row per grade, with a rate/percentage.

---

Query 2: Suspension gap by race/ethnicity

At Washington Middle School in 2023-2024, what is the suspension rate for each race/ethnicity group? Order from highest to lowest.

Domains: discipline + students Expected shape: grouped bar/table by subgroup, descending rate.

---

Query 3: English learner attendance vs. non-English learner attendance

Compare the chronic absenteeism rate for English learners versus non-English learners district-wide during 2022-2023.

Domains: attendance + students Expected shape: two rows (EL / not EL) with rates.

---

Query 4: Grade trends for students with multiple discipline incidents

For students at Roosevelt High School who had more than one discipline incident in 2023-2024, what was their average GPA that year?

Domains: discipline + grades + enrollment Expected shape: single aggregate value or list of students.

---

Query 5: Top 10 schools by chronic absenteeism rate

Across all schools in 2023-2024, which 10 schools have the highest chronic absenteeism rates, and what are those rates?

Domains: attendance + enrollment Expected shape: ranked table, top 10.

---

Query 6: Grade-level math performance

For the 2023-2024 school year, what is the average math grade and the percent of students earning a C or below in math at each middle school?

Domains: grades + enrollment Expected shape: one row per school with avg and % below threshold.

---

Query 7: Foster/homeless student discipline exposure

In 2023-2024, how many foster or homeless students had at least one discipline incident, and what percent of all foster/homeless students is that?

Domains: discipline + students Expected shape: count + rate.

---

Query 8: Longitudinal attendance improvement

For Lincoln Elementary, compare the chronic absenteeism rate in 2022-2023 to 2023-2024. Show both rates and the percentage-point change.

Domains: attendance + enrollment Expected shape: two rows with delta column.

---

Query 9: Course grade distribution

For Roosevelt High School in 2023-2024, show the distribution of letter grades (A, B, C, D, F) across all courses.

Domains: grades + enrollment Expected shape: grade counts table.

---

Query 10: Discipline type breakdown

At Washington Middle School in 2023-2024, what are the three most common discipline incident types, and how many of each occurred?

Domains: discipline + enrollment Expected shape: top-3 table with counts.

---

Query 11: Student group enrollment imbalance

In 2023-2024, what is the percentage of socioeconomically disadvantaged students at each elementary school? Order by highest percentage.

Domains: students + enrollment Expected shape: school list with percentages, sorted descending.

---

Query 12: Attendance-GPA correlation cohort

For 2023-2024, list students at Roosevelt High School with 10 or more absences and a GPA below 2.0. Include their race/ethnicity and grade level.

Domains: attendance + grades + enrollment + students Expected shape: student-level cohort table.

---

Query 13: Repeating discipline offenders

How many students had three or more discipline incidents in 2023-2024, and which school had the most such students?

Domains: discipline + enrollment Expected shape: count + school with highest count.

---

Query 14: English learner progress proxy

For English learners enrolled in 2023-2024, what is their average ELA grade compared to their average math grade? Break out by grade level.

Domains: grades + students + enrollment Expected shape: rows by grade with ELA avg and math avg.

---

Query 15: Equity gap in honors participation

In 2023-2024, what percent of students at each high school earned at least one A in an honors-eligible course? Compare rates for students with and without IEPs.

Domains: grades + students + enrollment Expected shape: school × IEP status table with participation rates.

---

Evaluation notes

• Good answers should join the right tables, filter to the requested school year and school, and return a clear aggregate or ranked table.
• Watch for SQL that ignores the school-year filter, double-counts students enrolled in multiple grades, or drops the subgroup breakdown.
• Some queries are intentionally ambiguous (e.g., "honors-eligible course") to test how the model handles missing or inferred schema concepts.

---

Suggested scoring rubric (per query)

Criterion	Points	Notes
Correct tables joined	2	Uses the tables actually needed for the question.
Correct filters applied	2	School year, school name, program flags are respected.
Correct aggregation/grouping	2	Rates, averages, counts, or rankings are computed correctly.
Output is interpretable	2	Summary sentence and table make sense without extra explanation.
No forbidden operations	1	Query is read-only and uses allowed tables/columns.

Total: 9 points per query.