Attendly Vault

2025-12-17 - 21st CCLC APR Queries for Winters

5 min read

2025-12-17: Built Complete 21st CCLC APR Query Suite for Winters Joint USD

For: Nicole Jordan (Winters Joint Unified School District) Impact: Automated all data requirements for federal 21st Century Community Learning Centers Annual Performance Report, eliminating manual data extraction from multiple systems and reducing APR preparation time from days to minutes. Context: Federal APR requires precise student counts across grade levels, attendance hour bands, demographics (race/ethnicity, sex), and special populations (English learners, economically disadvantaged, disabilities, family members). Data spans Attendly attendance system and Aeries SIS, requiring complex joins and federal reporting standards compliance.

Deliverables

Built 5 production-ready SQL queries with Explo parameter filtering:

  1. Grade Level Totals (grade-level-totals.sql)

    • Unique student counts by grade (PK-12th with ordinal labels: 1st, 2nd, 3rd, etc.)
    • Shows all grades including zeros
    • Filter: [[{{site}}]]

  2. Grade Level by Hour Band (grade-level-by-hour-band.sql)

    • Students distributed across 6 dosage bands: <15hrs, 15-44hrs, 45-89hrs, 90-179hrs, 180-269hrs, 270+hrs
    • Calculates total hours per student by summing all attendance session durations
    • Pivoted format: grades as rows, hour bands as columns
    • Filter: [[{{sites_dropdown}}]]

  3. Race/Ethnicity by Grade Band (race-ethnicity-by-grade-band.sql)

    • 8 federally defined categories: American Indian/Alaska Native, Asian, Black/African American, Hispanic/Latino, Native Hawaiian/Pacific Islander, White, Two or more races, Data not provided
    • Pivoted format: race/ethnicity as rows, grade bands (PK-5th, 6th-12th) as columns
    • Hispanic/Latino takes precedence per federal standards
    • Asian includes Filipino (code 400)
    • Detects “Two or more races” when multiple RaceCode fields populated
    • Filter: [[{{sites_dropdown}}]]

  4. Sex by Grade Band (sex-by-grade-band.sql)

    • 3 categories: Male, Female, Data not provided
    • Nonbinary (code ‘N’) maps to Data not provided per APR requirements
    • Pivoted format: sex as rows, grade bands (PK-5th, 6th-12th) as columns
    • Filter: [[{{sites_dropdown}}]]

  5. Population Specifics (population-specifics.sql)

    • English learners: LanguageFluencyCode = ‘L’ (32 students at Waggoner Elementary)
    • Economically disadvantaged: FRE_status = ‘F’ or ‘R’ (77 students)
    • Students with disabilities: UserCode1 or UserCode2 has any value (20 students)
    • Family members served: Students with siblings in 21st CCLC using FamilyKey matching (4 students)
    • Pivoted format: populations as rows, grade bands (PK-5th, 6th-12th) as columns
    • Filter: [[{{sites_dropdown}}]]

Technical Implementation

Database Architecture Discovery:

  • Programs tagged via JSONB: metadata->'tags' @> '["21st CCLC"]' (18 programs found)
  • Location join: (program.metadata->>'locationId')::uuid → location.id
  • Student ID extraction: (dependent.metadata->>'schoolStudentId')::integer → aeries_students_mirror.student_id
  • Grade data in aeries_students_mirror.grade (-2=PK, -1=TK, 0=K, 1-12)

Aeries Code Mappings (Winters-specific):

  • Race codes differ from standard: Asian is 201-299 (not 200), Black is 600 (not 300), Pacific Islander is 301-399 (not 400), White is 700 (not 500)
  • Filipino (code 400) categorized as Asian per APR requirements
  • Gender stored in aeries_data->>'Gender' (M/F/N)
  • Ethnicity priority: EthnicityCode=‘Y’ overrides race codes
  • FRE_status in dependent.metadata (not aeries_data): F=Free lunch, R=Reduced lunch
  • Disability indicators: UserCode1 or UserCode2 non-empty
  • Language fluency: LanguageFluencyCode=‘L’ for English learners (E=English only, R=Reclassified, F=Initially fluent, W=Waiting)

Query Patterns:

  • All queries use CTEs for clarity: program_students → demographics → aggregation
  • “Show all categories with zeros” pattern: Generate complete category list, LEFT JOIN to actual counts, COALESCE to 0
  • Pivoted demographics: CASE WHEN grade_band for columns, GROUP BY category for rows
  • Hour calculation: EXTRACT(EPOCH FROM (end_time - start_time)) / 3600 summed per student

Data Validation Results (Waggoner Elementary test):

  • 149 total unique students (TK-2nd grades only, as expected for elementary school)
  • Hour band distribution: Most students in 180-269hr range (87 students), indicating strong engagement
  • Demographics: 92 Hispanic/Latino (62%), 48 White (32%), 4 Asian, 4 Black, 1 Pacific Islander
  • Special populations: 32 English learners (21%), 77 economically disadvantaged (52%), 20 with disabilities (13%)

User Documentation

Created 3 explanatory blurbs in styled HTML matching existing report standards:

  1. Main Introduction - Report purpose (federal APR requirement), what it shows (5 data sections), how to use (select school dropdown)
  2. Grade Level Calculations - Student counting logic, hour accumulation methodology, dosage band interpretation
  3. Demographics Calculations - Federal race/ethnicity precedence rules, sex categorization, special population definitions (including data sources: Aeries vs Attendly)

Each blurb includes:

  • Structured sections with headers
  • Bullet lists for scanability
  • Highlighted tip/note boxes
  • Data source transparency (Aeries fields, Attendly metadata)
  • Data quality guidance (verify Aeries demographics, check program tags)

Next Steps

  • Deploy queries to Winters Explo instance and verify sites_dropdown filter
  • Test with other school sites (Shirley Rominger Intermediate, Winters Middle School, Winters High School) to validate 6th-12th grade band
  • Coordinate with Nicole Jordan on APR submission timeline
  • Consider automating APR PDF generation if federal form structure is stable

Technical Notes

Explo Parameter Syntax: [[AND l.name = {{sites_dropdown}}]] - double brackets make entire WHERE clause optional (empty filter = all sites)

Performance: All queries execute in <40ms on Waggoner Elementary dataset (149 students)

Data Dependencies:

  • Requires aeries_students_mirror table synced from Aeries API
  • Depends on program.metadata tags being accurate (staff must tag programs as “21st CCLC”)
  • FRE_status must be populated in dependent.metadata for economically disadvantaged counts
  • UserCode1/UserCode2 usage varies by district - confirmed working for Winters

Edge Cases Handled:

  • Students with multiple race codes → “Two or more races”
  • Missing demographic data → “Data not provided” category
  • Attendance sessions without end_time → excluded from hour calculations
  • FamilyKey = ‘0’ or empty → not counted as family member
  • Grade levels outside PK-12 range → excluded via WHERE clause

Tags: reporting winters 21st-cclc federal-compliance apr sql demographics explo

Related: Winters Hub, 21st CCLC Hub, Federal Reporting Hub

Graph View

Backlinks