Analyzing NYC Public School Test Result Scores
Photo by Jannis Lucas
on Unsplash.
1. Inspecting the data
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 notebook, we will take a look at data on SATs across public schools in New York City. Our database contains a single table:
schools
| column | type | description |
|---|---|---|
school_name
|
varchar
|
Name of school |
borough
|
varchar
|
Borough that the school is located in |
building_code
|
varchar
|
Code for the building |
average_math
|
int
|
Average math score for SATs |
average_reading
|
int
|
Average reading score for SATs |
average_writing
|
int
|
Average writing score for SATs |
percent_tested
|
numeric
|
Percentage of students completing SATs |
Let’s familiarize ourselves with the data by taking a looking at the first few schools!
%%sql
postgresql:///schools
-- Select all columns from the database
-- Display only the first ten rows
SELECT *
FROM schools
LIMIT 10
| school_name | borough | building_code | average_math | average_reading | average_writing | percent_tested |
|---|---|---|---|---|---|---|
| New Explorations into Science, Technology and Math High School | Manhattan | M022 | 657 | 601 | 601 | None |
| Essex Street Academy | Manhattan | M445 | 395 | 411 | 387 | 78.9 |
| Lower Manhattan Arts Academy | Manhattan | M445 | 418 | 428 | 415 | 65.1 |
| High School for Dual Language and Asian Studies | Manhattan | M445 | 613 | 453 | 463 | 95.9 |
| Henry Street School for International Studies | Manhattan | M056 | 410 | 406 | 381 | 59.7 |
| Bard High School Early College | Manhattan | M097 | 634 | 641 | 639 | 70.8 |
| Urban Assembly Academy of Government and Law | Manhattan | M445 | 389 | 395 | 381 | 80.8 |
| Marta Valle High School | Manhattan | M025 | 438 | 413 | 394 | 35.6 |
| University Neighborhood High School | Manhattan | M446 | 437 | 355 | 352 | 69.9 |
| New Design High School | Manhattan | M445 | 381 | 396 | 372 | 73.7 |
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.
%%sql
-- Count rows with percent_tested missing and total number of schools
SELECT COUNT(*) - COUNT(percent_tested) AS num_tested_missing, COUNT(*) AS num_schools
FROM schools
| num_tested_missing | num_schools |
|---|---|
| 20 | 375 |
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.
%%sql
-- Count the number of unique building_code values
SELECT COUNT(DISTINCT building_code) AS num_school_buildings
FROM schools| num_school_buildings |
|---|
| 233 |
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).
%%sql
-- 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| school_name | average_math |
|---|---|
| Stuyvesant High School | 754 |
| Bronx High School of Science | 714 |
| Staten Island Technical High School | 711 |
| Queens High School for the Sciences at York College | 701 |
| High School for Mathematics, Science, and Engineering at City College | 683 |
| Brooklyn Technical High School | 682 |
| Townsend Harris High School | 680 |
| High School of American Studies at Lehman College | 669 |
| New Explorations into Science, Technology and Math High School | 657 |
| Eleanor Roosevelt High School | 641 |
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!
%%sql
-- Find lowest average_reading
SELECT MIN(average_reading) AS lowest_reading
FROM schools
| lowest_reading |
|---|
| 302 |
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.
%%sql
-- Find the top score for average_writing
SELECT school_name, MAX(average_writing) AS max_writing
FROM schools
-- Group the results by school
GROUP BY school_name
-- Sort by max_writing in descending order
ORDER BY max_writing DESC
-- Reduce output to one school
LIMIT 1
| school_name | max_writing |
|---|---|
| Stuyvesant High School | 693 |
7. Top 10 schools
An average writing score of 693 is pretty impressive!
This top writing score was at the same school that got the top math 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.
%%sql
-- Calculate average_sat
SELECT school_name, SUM(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 ten results
LIMIT 10
| school_name | average_sat |
|---|---|
| Stuyvesant High School | 2144 |
| Staten Island Technical High School | 2041 |
| Bronx High School of Science | 2041 |
| High School of American Studies at Lehman College | 2013 |
| Townsend Harris High School | 1981 |
| Queens High School for the Sciences at York College | 1947 |
| Bard High School Early College | 1914 |
| Brooklyn Technical High School | 1896 |
| High School for Mathematics, Science, and Engineering at City College | 1889 |
| Eleanor Roosevelt High School | 1889 |
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!
%%sql
-- Select borough and a count of all schools, aliased 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
-- Organize results by borough
-- Display by average_borough_sat in descending order
SELECT borough, COUNT(*) AS num_schools, SUM(average_math + average_reading + average_writing) / COUNT(*) AS average_borough_sat
FROM schools
GROUP BY borough
ORDER BY average_borough_sat DESC
| borough | num_schools | average_borough_sat |
|---|---|---|
| Staten Island | 10 | 1439 |
| Queens | 69 | 1345 |
| Manhattan | 89 | 1340 |
| Brooklyn | 109 | 1230 |
| Bronx | 98 | 1202 |
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.
%%sql
-- Select school and average_math
-- Filter for schools in Brooklyn
-- Aggregate on school_name
-- Display results from highest average_math and restrict output to five rows
SELECT school_name, average_math
FROM schools
WHERE borough = 'Brooklyn'
ORDER BY average_math DESC
LIMIT 5| school_name | average_math |
|---|---|
| Brooklyn Technical High School | 682 |
| Brooklyn Latin School | 625 |
| Leon M. Goldstein High School for the Sciences | 563 |
| Millennium Brooklyn High School | 553 |
| Midwood High School | 550 |