Analyzing NYC Public School Test Result Scores (SQL)

Introduction

Every year, American high school students take SATs, which are standardized tests intended to measure literacy, numeracy, and writing skills. There are three sections - reading, math, and writing, each with a maximum score of 800 points. These tests are extremely important for students and colleges, as they play a pivotal role in the admissions process.

Analyzing the performance of schools is important for a variety of stakeholders, including policy and education professionals, researchers, government, and even parents considering which school their children should attend.

In this project, and using SQL, I will take a look at data on SATs across public schools in New York City.

You can check the notebook on Github by clicking on the button below:

1- Inspecting the Data

Our database contains a single table (schools):

Le'ts familiarize ourselves with the data by taking a looking at the first few schools!

-- Select all columns from the database

Select *

From schools

-- Display only 10 results

limit 10;

2- Finding Missing Values

It looks like the first school in our database had no data in the percent_tested column!

Let's identify how many schools have missing data for this column, indicating schools that did not report the percentage of students tested.

To understand whether this missing data problem is widespread in New York, we will also calculate the total number of schools in the database.

-- Count rows with percent_tested missing and total number of schools

Select count(school_name) - count(percent_tested) as num_tested_missing, count(school_name) as num_schools

From schools;

3- Schools by Building Code

There are 20 schools with missing data for percent_tested, which only makes up 5% of all rows in the database.

Now let's turn our attention to how many schools there are. When we displayed the first ten rows of the database, several had the same value in the building_code column, suggesting there are multiple schools based in the same location. Let's find out how many unique school locations exist in our database.

-- Count the number of unique building_code values

Select count(Distinct building_code) as num_school_buildings

From schools;

4- Best Schools for Math

Out of 375 schools, only 233 (62%) have a unique building_code!

Now let's start our analysis of school performance. As each school reports individually, we will treat them this way rather than grouping them by building_code.

First, let's find all schools with an average math score of at least 80% (out of 800).

-- Select school and average_math

Select school_name, average_math

From schools

-- Filter for average_math 640 or higher

Where average_math >= 640

-- Display from largest to smallest average_math

Order by average_math desc;

5- Lowest Reading Score

Wow, there are only ten public schools in New York City with an average math score of at least 640!

Now let's look at the other end of the spectrum and find the single lowest score for reading. We will only select the score, not the school, to avoid naming and shaming!

-- Alias lowest average_reading as lowest_reading

Select min(average_reading) as lowest_reading

From schools;

6- Best Writing School

The lowest average score for reading across schools in New York City is less than 40% of the total available points!

Now let's find the school with the highest average writing score.

-- Select school_name and the top score for average_writing

Select school_name, max(average_writing) as max_writing

From schools

-- Group the results by school_name

Group by school_name

-- Sort by max_writing in descending order

Order by max_writing desc

-- Reduce the output to one school

Limit 1;

7- Top 10 Schools

An average writing score of 693 is pretty impressive!

This top math score was at the same school that got the top writing score, Stuyvesant High School. Stuyvesant is widely known as a perennial top school in New York.

What other schools are also excellent across the board? Let's look at scores across reading, writing, and math to find out.

-- Select school_name and calculate average_sat

Select school_name, average_math + average_reading + average_writing as average_sat

From schools

-- Group by school_name

Group by school_name

-- Sort by average_sat in descending order

Order by average_sat desc

-- Display the top 10 results

Limit 10;

8- Ranking Boroughs

There are four schools with average SAT scores of over 2000! Now let's analyze performance by New York City borough.

We will build a query that calculates the number of schools and the average SAT score per borough!

-- Select borough and a count of all schools, aliased as num_schools

Select borough, count(*) as num_schools,

-- Calculate the sum of average_math, average_reading, and average_writing, divided by a count of all schools, aliased as average_borough_sat

(sum(average_math) + sum(average_reading) + sum(average_writing)) / count(*) as average_borough_sat

From schools

-- Organize the results by borough

Group by borough

-- Display by average_borough_sat in descending order

Order by average_borough_sat desc;

9- Brooklyn Numbers

It appears that schools in Staten Island, on average, produce higher scores across all three categories. However, there are only 10 schools in Staten Island, compared to an average of 91 schools in the other four boroughs!

For our final query of the database, let's focus on Brooklyn, which has 109 schools. We wish to find the top five schools for math performance.

-- Select school_name and average_math

Select school_name, average_math

From schools

-- Filter for schools in Brooklyn

Where borough = 'Brooklyn'

-- Aggregate by school_name

Group by school_name

-- Display results from highest to lowest average_math and restrict output to five rows

Order by average_math desc

Limit 5;

Conclusion

In this project, we looked at how performance varies by borough, identified how many schools fail to report information, and found the top ten performing schools across the city...

This project is showcasing how even basic SQL queries (selecting columns, filtering rows, aggregating, sorting, and grouping) can give interesting insights that will help us explore the dataset.