YES Sports Demographic Program Report

2025-12-12: YES! Sports Demographic Program Report Dashboard

For: Chen (Oakland Unified) Impact: Delivers comprehensive demographic and participation visibility for YES! Sports programs, enabling equity analysis and activity impact reporting Context: Chen requested demographic data showing who participates in programs, gender/race breakdowns, multi-program engagement, and connection to OUSD’s physical activity goals (only 14% of Oakland kids meet 60-min daily activity recommendation vs 23% nationally)

Implementation

Built 9 SQL queries for Explo dashboard:

Query	Purpose
01_total_participants.sql	Unique students enrolled
02_gender_breakdown.sql	Boys/Girls/Non-Binary distribution
03_race_ethnicity.sql	Race/ethnicity with Hispanic/Latino precedence per federal standards
04_schools_represented.sql	Top 10 schools by student participation
05_multi_program_engagement.sql	Students in 1/2/3/4+ programs
06_activity_minutes.sql	Total and average activity time
07_program_enrollment.sql	Enrollment by sport/program
08_grade_distribution.sql	Participation by grade level (TK-8)
09_student_participation_detail.sql	Individual student table with demographics and engagement metrics

Key technical details:

• Enrollment state is 'complete' (not ‘ENROLLED’) in Oakland schema
• Demographics stored in dependent.metadata JSONB (gender, raceCode1-5, ethnicityCode, grade, mainLocationId)
• Verified via Aeries API: Gender code N = Nonbinary (not “Not Reported” as initially assumed)
• Activity minutes calculated from attendance.start_time to end_time — avg ~132 min/session
• Race/ethnicity logic: Hispanic/Latino (ethnicityCode=‘Y’) takes precedence per federal reporting standards

Data volume: 3,134 unique participants, 4,806 enrollments, 6,350 attendance records, 17 YES! Sports programs

Files location: ~/projects/oakland-dashboard/

Dashboard Layout

┌─────────────────┬─────────────────┬─────────────────────────┐
│ Total Participants │ Activity Minutes │ Gender Breakdown (Pie) │
├─────────────────────────────────────────────────────────────┤
│ Race/Ethnicity Breakdown (Horizontal Bar)                   │
├────────────────────────────┬────────────────────────────────┤
│ Top 10 Schools (Bar)       │ Multi-Program Engagement (Donut)│
├────────────────────────────┼────────────────────────────────┤
│ Program Enrollment (Bar)   │ Grade Distribution (Bar)       │
└────────────────────────────┴────────────────────────────────┘

Also created HTML description blurb explaining report purpose and how to interpret metrics.

Related: Hub, 2025-W50 - Impact

Tags: reporting oakland chen-oakland yes-sports demographics