Write a SQL query to join attendance, lunch service, and enrollment tables to find operational bottlenecks by school and grade at Success Academy Charter Schools.
Use active enrollment records and evaluate attendance and lunch fulfillment rates. Treat missing attendance or lunch data as an operational bottleneck.
Output
- One row per active school and grade.
- Columns:
school_name, grade, enrolled_students, attendance_rate, lunch_service_rate, and bottleneck_status.
- Rates are percentages rounded to two decimals. Include all active school-grade combinations, order by
school_name and grade ascending, and label rows as Bottleneck when either rate is below its threshold or missing.
Output details
- Use thresholds of 90% for
attendance_rate and 95% for lunch_service_rate.
- Calculate
attendance_rate as present attendance records divided by all attendance records, multiplied by 100.
- Calculate
lunch_service_rate as total meals served divided by total meals requested, multiplied by 100.
- Preserve rates as
NULL when their denominator is zero or data is missing, but treat NULL as 0 for bottleneck classification.
