This project analyzes NYC public school academic performance using multi-year standardized test scores and graduation rates to uncover trends in educational quality and inequality. The goal is to use data-driven insights to support policy decisions, resource allocation, and student success initiatives.
📌 Overview
The primary objective of this project is to evaluate academic outcomes across boroughs — from standardized test performance to graduation success. By applying relational data modeling and analytical SQL techniques, the project uncovers trends in school performance, borough-level disparities, and long-term academic progress.
This project simulates a real-world education analytics and business intelligence use case commonly encountered in public-sector, policy research, and institutional analytics teams.
📊 Dataset: Uses a synthetic NYC education dataset (schools.csv, test_scores.csv) containing anonymized school performance records across multiple academic years.
🏫 School Performance Analysis: Compares standardized test scores and graduation rates across schools and boroughs.
📈 Trend Analysis: Tracks multi-year trends in math, reading, writing, and graduation outcomes.
🌍 Borough Comparison: Evaluates academic disparities across Manhattan, Brooklyn, Queens, Bronx, and Staten Island.
🚨 Underperformance Detection: Identifies consistently underperforming schools based on KPI thresholds.
💻 SQL Analytics Layer: Implements BI-ready SQL views for key education KPIs.
📝 Documentation: Includes structured SQL scripts and analytical insights.
🔁 Reproducibility: Provides CSV datasets, schema.sql, and analytics.sql for easy setup.
Loaded and validated synthetic academic performance data from CSV files.
Ensured realistic score distributions and graduation rate ranges.
Verified referential integrity between schools and test scores.
Standardized year-based records for time-series analysis.
Designed a normalized relational schema covering:
Schools and multi-year test score performance.
Implemented:
Primary keys and foreign key constraints
Indexed analytical columns for performance optimization
Clean separation of dimension and fact tables
Designed analytical SQL views to compute:
Average test scores and graduation rates by borough
Top 15 schools by overall academic performance
Multi-year trends in academic outcomes
Underperforming schools below KPI thresholds
Ensured:
Divide-by-zero safety
BI-ready semantic layer via SQL views
Efficient joins via indexed foreign keys
In addition to raw SQL queries, a production-style analytics layer was built using:
Pre-aggregated SQL views
Reusable KPIs for BI consumption
Multi-year performance trend modeling
Scalable join design for future data growth
This mirrors how education analytics pipelines are typically implemented in modern data warehouses (SQL Server, Snowflake, Redshift, BigQuery, PostgreSQL).
Academic performance varies significantly by borough, indicating localized education disparities.
Graduation rates correlate strongly with standardized test outcomes.
Several schools consistently underperform relative to city-wide averages.
Overall academic performance shows gradual improvement over time.
Increase funding and targeted interventions for underperforming schools.
Scale best practices from top-performing schools across boroughs.
Invest in tutoring and academic enrichment programs.
Monitor graduation trends alongside academic performance metrics.
Develop early-warning indicators for declining school performance.
Run the SQL scripts in order:
- sql/schema.sql
- Load CSVs into tables
- sql/analytics.sql