NOTE: The following is a slightly modified version of a series of SQL reports that I wrote for my Foundations of Data Analytics class while operating a mock basketball analytics consultancy. If you’re wondering why this is largely written in dry manner, it was not written for a public audience. I ended up receiving an A for whatever it’s worth, so if you think this sucks, the jokes on you. Or it’s on me for spending money to go to school just to write a mediocre report with an inflated grade. Let’s go with the former.
The Importance of Basketball Analytics
With billions of dollars of international investment from companies, fans, and governments across the globe, basketball is a booming industry and living ecosystem. The NBA is the world’s premier basketball league, boasting an ecosystem of competitive franchises spread through major North American cities. Behind the competitive nature of the sport are multi-million dollar contracts as well as bonuses when players and teams succeed. However, there are several challenges inherent in this results-driven environment. Front offices of NBA franchises have to keep an eye on the future and how the sport can develop, but they also have to retain their jobs by constructing winning rosters in the short-term. Executives often find themselves in a bind where the data in one place suggests one course of action, while data in another place suggests the opposite. How can teams create successful rosters with frequently contradictory and overwhelming amounts of data?
For this report, I will explain how I built a normalized database that consolidates team performance data across multiple categories, and provides a comprehensive resource for evaluating historical data. The goal of this mock database is to serve as a foundation for advanced performance analysis, emphasizing consistency and integrity across datasets.
Database Structure and Normalization
Overview of Tables and Data
My database design consolidates data from multiple statistical categories into five primary tables, all manually imported over from Basketball Reference:
- advanced_team: Advanced metrics, such as Offensive Rating (ORTG), Defensive Rating (DRTG), and Pace.
- per_game_team_raw: Per-game statistics for each team, including points, field goal percentage, and rebounding metrics.
- per_game_opponent_raw: Per-game statistics for opponents, useful for tracking defensive performance.
- total_team_raw: Aggregated totals for team performance, such as total field goals made and total assists.
- total_opponent_raw: Aggregated totals for opponents, allowing for defensive trend analysis.
Each table is linked by two key fields: “Season_ID” and “Team_ID”. This ensures that all data is consistently tied to specific seasons and teams, enabling accurate comparisons across years.
Normalization Process
To ensure a clean and efficient database structure, I followed the principles of normalization.
First Normal Form (1NF), demonstrated in Figure 1
- Ensured that all columns contain atomic values, with no repeating groups or arrays.
- Removed duplicate rows and unnecessary summary data.
Having atomic values means that each field consists of a single piece of data. By removing duplicate rows, I’ve narrowed down our database so it’s much easier to pull data from. Through this process, I am able to store raw data and compute points dynamically with SQL.
Figure 1: Checking for null values in each table
Second Normal Form (2NF), demonstrated in Figure 2
- Created composite primary keys on “Season_ID” and “Team_ID” to eliminate partial dependencies.
- Removed derived data, such as league-wide averages, which are better calculated through queries.
By doing this I ensure that different attributes like team statistics, rankings, or performance metrics are able to be specifically linked to a team and prevent inconsistent data in a select season. Without this step, redundant data could arise as the same team could have multiple conflicting data throughout different seasons. By removing derived data, I reduced redundancy, ensure accuracy, and improve consistency to present real time accuracy.
Figure 2: Eliminating ”League Average” rows dependent on other values.
Third Normal Form (3NF), demonstrated in Figure 3
- Eliminated transitive dependencies by ensuring that all non-key attributes are fully dependent on a composite primary key (“Season_ID, Team_ID”).
- Added foreign key constraints to enforce relationships between tables and maintain data integrity (“pk_per_game_team” as an example of a constraint).
Figure 3: Adding foreign key restraints and ensuring no transitive dependencies.
Without ensuring dependency on the composite primary key, there’s a risk of data inconsistency—such as a team’s name being stored in multiple rows, where one row is updated while another isn’t. Adding foreign key constraints is equally important, as they create links between tables to ensure that referenced values in one table exist in another. This maintains referential integrity; for example, if a referenced record is updated or deleted, the database ensures that related data stays consistent. As a result, there’s greater data reliability, with no errors that could disrupt future analysis.
Primary and Foreign Keys
For each of our tables, I’ve set a composite primary key (“Season_ID, Team_ID”) to ensure data consistency. I also added foreign keys to link tables and establish relationships between each of our tables.
Figure 4: Examples of primary keys in our tables.
- Foreign Keys:
- “per_game_team_raw” references “advanced_team”.
- “per_game_opponent_raw” references “advanced_team”.
- “total_team_raw” references “advanced_team”.
- “total_opponent_raw” references “advanced_team”.
The “advanced_team” table acts as the reference table for all others. In every table that lists “Season_ID” and “Team_ID,” the fields automatically link back to our reference table, which ensures seasonal and team validity. For an example of how this works in practice, I can’t insert fictional seasons and fictional teams within our other database – for example, putting “1996” in “Season_ID” and “Mars Monstars” into one of our non-primary tables wouldn’t work because it’s not listed in our primary reference table. On a similar note, our foreign key restraints ensure that if a team changes its name, the historical records remain linked through teams with the same “Team_ID.”
EER Diagram
The EER diagram illustrates the relationships between tables, highlighting how each table is connected by Season_ID and Team_ID. Below is a visual representation that helps clarify how data flows within the database and ensures that each table has a well-defined role.
Figure 5: Our EER diagram, generated by MySQL Workbench.
As shown in Figure 5, The “advanced_team” table is the reference table that provides primary keys for the other four tables. Conversely, each of “per-game-team_raw” and “per_game_opponent_raw” contain per-game statistics, yet remain linked to advanced_team through foreign keys. In similar fashion comes both of our fields which store aggregated totals for a team (“total_team_raw”) and their cumulative opponents (‘total_opponent_raw”), which are also referencing “advanced team”.
Organizing the database in this manner helps us prevent data anomalies. It also maintains the ability for users to make queries across multiple tables. For example, I can calculate conference, division, or franchise-specific trends for average defensive rating and offensive rating across any given time span due to the consistent reference structure.
Through established relationships between our tables, none of them are left on an island. Instead, each record in the related tables corresponds to a valid season and team in the reference table.
Bridge to Queries
In the next section, I will present an in-depth SQL-based analysis of NBA team performance trends, correlating advanced and box score statistics with wins over multiple decades in. Utilizing advanced SQL queries, I’ve explored offensive and defensive efficiency, team styles, and statistical correlations. Each query demonstrates key SQL techniques while offering insights into league-wide trends.
Query 1: Offense vs Wins
On a very broad level, offense vs. defense has always been a controversial debate among basketball fans and professional analysts alike. “Defense wins championships” is a phrase you’ll often hear cited among executives and fans of the game; and in recent years, with the prevalence of three-point shooting across the league, some analysts have even concluded that shooting three point attempts on its own is how teams can quickly improve.
For this query, I take a closer look at key offensive statistics, as well as offensive rating (points scored per 100 possessions) by themselves to examine what correlations exist between wins, three point attempts, three point accuracy, assists, turnovers (making sure to reverse this stat, as lower turnovers are better), and free throw accuracy.
Figure 1a
I accomplish this query by using a “with” statement to create a temporary result set. After that, I use a select statement and a case statement (with a series of “when,” “between,” “and,” and “then” statements) to chunk years together into five-year increments (periods). Following this step, I then performed a right join from the reference table to my per-game statistical table, purely to select the primary key and organize the teams within each period respectively.
Figure 1b
Within the last half of Figure 1a and the rest of Figure 1b, I then use a combination of round statements, mathematical formulas (sum, square root, power, round statements, etc) and a count function to create statistical correlations (using a basic regression analysis) within SQL for each of our statistics. I conclude this code by then using a from statement to reference our temporary result set and both group them by and order them by period.
Figure 1c
Figure 1c illustrates that rather than being a recent fad, offensive rating has consistently had a high correlation with wins. However, rather than three-point shooting across the board recently going upwards with no halting, it’s had a more nuanced journey. Contrary to popular belief, the time when the act of attempting threes by itself had the highest correlation with wins was during the late 2000s, largely due to the Phoenix Suns of this era being trailblazers (not to be confused with the Portland Trail Blazers) and shooting far more threes than everyone else en route to consistent regular season dominance.
Though teams may shoot more threes overall today, merely attempting on their own may not be as much of an asset relative to the field as it used to be in the past – accuracy on these threes, however, matters more than ever. As a result, general managers should be skeptical of high three point volume shooters and resist the temptation to sign players who may score a lot of points, but do so at the risk of being inefficient.
Conversely, assists have declined in their correlation to wins, while avoiding turnovers and converting on free throws has taken up more importance in today’s game, when teams play at a faster pace. This reflects how the current climate for basketball is far more fast-break heavy, de-emphasizing the role of traditional half-court offenses and highlighting the increased importance of attacking the rim, drawing free throws, converting them, and accurately shooting threes. When signing free agents, general managers should be cautious toward traditional court generals and prioritize team, off-the-ball players that can shoot the occasional three, but not egregiously so.
Query 2: Defensive Trends and Their Impact on Wins
In many ways, the second query is a follow-up to the first query and fits a similar purpose, only for defense. However, the scale of measuring defensive trends takes special importance because raw defensive efficiency has gone down across the league since the early 2000s (when defensive techniques like the now-banned hand-checking and zone defense were both legal). As a result, any team that can break the recent ‘spell’ of declining defensive efficiency and figure out key opportunities for having an edge will be sure to find an advantage in understanding which elements of defense have the most opportunities for creating win conditions.
Like with Query 1 (but longer), each of our figures involves us taking a closer look at key box score and advanced defensive statistics, along with defensive rating. The purpose is to discover any potential secret edges in which of these defensive metrics matters most and has continued to correlate heavily with wins. The statistics we’re looking to measure against wins are defensive rating (points allowed per 100 possessions), steals, blocks, defensive rebounds, opposing three point accuracy, opposing three point attempts, opposing field goal percent, opposing assists, and opposing turnovers.
Figure 2a
Figure 2a shows that I can accomplish this query by using a “with” statement to create a temporary result set. After that, I use a select statement and a case statement (with a series of “when,” “between,” “and,” and “then” statements) to chunk years together into five-year increments (periods). Following this step, I then performed a regular join. In SQL, this is automatically an inner join, which I used for the purpose of variety within this report, since there’s little impact related to matching all the values in both tables.
Figure 2b
Figure 2c
Figures 2b and 2c continue from where Figure 2a left off, following in similar fashion to our first query re: using a select statement to confirm we’re aggregating this data for our filtered periods, and then running similar correlation analysis to these statistics. Note the use of negative numbers for specific statistics – I have done this to account for the fact that lower numbers in categories like opposing field goal/three point accuracy, defensive rating, assists, and three point attempts typically are ‘better’ values to have.
Figure 2d
Figure 2d shows that while defensive rating continues to have a relatively high correlation with winning, it has been far more volatile in its relationship to wins historically. Interestingly, our analysis also finds that steals, blocks, and defensive rebounds have weak to modest negative relationships with wins – likely due to the three-point revolution of recent times. This has come with an emphasis of perimeter play on both ends of the court, as well as spacing – how teams attack and defend the corners.
If there are any opportunities on the defensive end of the ball for teams to find edges, our analysis finds that allowing (or preventing) three point attempts is mostly negligible. However, efficiently defending them, contesting them, and limiting ball movement continues to play far more importance than gambling for steals, dominating the boards or trying to aggressively block shots at the rim. General managers looking to improve their teams defenses in meaningful ways should hold strong skepticism about individual playmakers with lengthy highlight-reels surrounding shot blocking or stealing ability. Instead, they should look to find more versatile, lengthy, and multi-positional talents who can play strong help defense out on the perimeter and disrupt efficient shot creation without taking risks.
Query 3: Pace vs Wins
Along with the three-point revolution, as I covered in prior reports, the pace of modern basketball is the fastest it’s ever been since the start of our data set. However, I was curious as to the 35-year-long trends on pace; is it true that faster teams have always typically performed better? Could it be possible that the more successful teams of today’s fast pace era are actually on the slower end?
Figure 3a
Figure 3a shows us using similar statements to Query 1 and Query 2 to group single-season teams across our data set together. However, I also created a subset of data specifically using a series of windows functions (NTILE and OVER) to classify teams into four specific pace categories.
Figure 3b
Following the start of the query, I used a series of math functions, CASE WHEN statements, THEN statements, END statements, and an “AS” statement to build our correlations into categories.
Figure 3c
The results from Figure 3c had me conclude that pace, for the most part, had a weak negative correlation, if any, with wins, though one that’s slightly shifted toward being slightly stronger in the last four years. . Though it’s still ultimately weak, it could hint that the most successful teams in the fast-pace era are the ones who are still able to run a half-court offense and control the clock when needed. The champion 2024 Boston Celtics, for example, ranked 23rd out of 30 teams in the NBA during their historically dominant season. As a result, general managers looking at the high pace environment of the NBA should not necessarily spend cash on completely changing their rosters for not being as high octane as everyone else. At a time when everyone is fast-paced, speed itself is not necessarily a difference maker.
Query 4: Offense vs Defense – Which Matters More?
Although Queries 1 and 2 showcased the correlations between different offensive and defensive statistics to wins, I used Query 4 to take a direct look at both offensive rating and defensive rating, side by side to see how they measured against one another. Figures 4a to Figures 4b follow in similar fashion to Query 1, with very little difference – only that defensive efficiency has a reversed correlation, due to lower values typically being better for opposing defenses. The goal was to see if there was ever a time when defensive rating mattered more, and if there was any secret trend or advantage to be gained for teams today for focusing on defense.
Figure 4a
Though Figure 4a follows in similar fashion to our previous queries, I also created a column called net rating to examine the role of both statistics in their relationships with wins.
Figure 4b
A key difference between this code and our previous one is that I applied a “having” statement for teams that appeared more than 0 times. Though the data set was complete anyway, I did this as a proof of concept to showcase an alternate way of filtering than a where statement.
Figure 4c
Figure 4c highlights how offensive rating has consistently had a higher correlation with wins, and that it’s not just a recent trend. Conversely, net rating continues to consistently play a great predictor for wins, though in the mid-to-late 90s, it suffered a brief decline in its correlation to wins. This showcases that net rating continues to be a strong predictor for future wins, but with offensive rating having greater stability over the years.
Simultaneously, however, it’s not the highest period of time that offense has ever had a strong correlation with wins. One interesting insight from this is that the late 1990s, often considered the peak of defensive basketball, is actually the period where teams that could score often had a larger relative advantage, with multiple of those years having a shortened three-point line as a rule change to encourage scoring. The following period, which saw a spike in correlation between defensive rating and wins, involved counter-rules, such as legalizing zone defense. The last five years have not yet reached the type of disparity of the late 90s, but it’s the largest one that’s existed since this period. This suggests that rule changes to make defense easier may be coming up in the short-term, and that general managers may want to anticipate these changes by quickly picking up defensive players with strong, fundamental help and perimeter defense qualities highlighted previously from Query 2.
Query 5: League-Wide Box Score Correlations
After taking a look at historic trends for offensive stats, defensive stats, and pace, I decided to examine respective correlations between box score statistics and wins, just to see the distribution of the most common ways that analysts look at basketball.
Figure 5a
In Figure 5a, I decided to use CASE WHEN statements to look at this for decades, rather than 5-year spans, and calculated averages of points, rebounds, assists, steals, blocks, and turnovers forced per game. I used regular (inner) joins in this instance and applied using clauses for each of our joins, as I was referencing three tables.
Figure 5b
Figure 5b highlights the rest of our code related to our correlation analysis. This follows in line with what I had performed for prior queries.
Figure 5c
Out of the box score stats I examined with decades as our point of comparison, Figure 5c shows that points continue to lead the way as far as predictors for success. Meanwhile, rebounds and assists are roughly about equal (though assists were far more important in the 1990s, the peak of half-court offenses). Conversely, however, steals and blocks have declined as respective correlations with wins, with opposing turnovers actually holding a negative correlation with wins. This is potentially due to teams playing from behind being more desperate and willing to gamble on steals and block opportunities on defense. However, as mentioned in Query 2, it’s also in line with shot disruption and help defense being more important in modern defense than individual playmaking. When signing new players, franchises should continue to hold skepticism toward players who may stuff a stat-sheet with flashy defensive statistics, but potentially create opportunities for opposing offenses to get ahead from their risky gambles.
Query 6: Identifying Distinct Team Play Styles
Many front offices of NBA teams perform opposition research, not just for individual teams, but ‘types’ of teams. Classifying opponents in this manner can help NBA analysts assist their respective coaching staff when it comes to building strategies for opponents. In Query 6, I showcase how people in front offices can do this effectively while keeping league-wide averages into account and not flatly comparing teams.
Figure 6a
Figure 6a is the start of our query. I begin with a “with” statement that calculates league-wide averages for offensive rating, defensive rating, and pace for each season. After that, I created a subset of our data that joins teams with respective league-wide averages, with three calculated fields in particular here: rel_ORtg, rel_DRtg, and rel_Pace. I then perform a left join from our previous subset of data onto our total team statistics table, matching it with our primary key.
Figure 6b
In Figure 6b, I continue our query by assigning teams play styles based on our three calculated fields. This is done this via a series of math functions, case statements, when statements, then statements, end as statements to then categorize everything together as a play style, directly citing it from our previous subset of data.
Figure 6c
Figure 6c shows how I conclude the query. In it, I used a where statement to account for only full seasons, also using a like statement to search for specific patterns that show up within our dataset before ordering it by season and our created field for play styles we’re looking for (in this case, offensive powerhouses and dominant teams).
Figure 6d
Figure 6d is an example of how members of a front office can classify teams and research them using historical insights. This query reflects how users of our database can use it to classify teams accordingly based on whatever metrics they deem necessary. In lieu of teams competing today and having active data that changes each week, it is useful for our clients to build historical comparisons to see how other teams like them have performed in the past and what succeeded or conquered such teams.
Query 7: Identifying Consistently Elite and Struggling Teams
In similar fashion to Query 6, Query 7 is about identifying specific sub-types of teams by evaluating which franchises over time have struggled or succeeded. Front office members – but especially newly hired general managers – can use this information to determine current trajectories of their respective franchises, build models off historical data to highlight comparison points for their franchises, and articulate to franchise leadership where the team is heading.
However, this type of query carries additional insights for the NBA itself – another client of our consulting services. For this query, I focused on primarily counting the number of teams in each overall performance category in order to highlight the state of parity across the league, as well as play style distribution. This can help the league determine whether it is having too much of a stale product, if every team is playing the same type of basketball, or if the league is becoming too top-heavy (as in an unhealthy number of teams are struggling to win and create a competitive product on the court).
Figure 7a
Figure 7a showcases the creation of a temporary subset of data that utilizes multiple basic SQL functions to evaluate season-long averages. It also highlights the creation of relative team performances to those averages in offensive rating and defensive rating, much like Query 6.
Figure 7b
Figure 7b shows us returning back to five-year-span categorizations. In it, I assigned each season to these five year spans before computing multi-season averages per team within the periods created.
Figure 7c
In Figure 7c, I categorized teams based on performance trends and use an upper statement to draw attention to each team’s specific type of classification. I follow this up with creating another subset of data meant to specifically count the number of teams per category over a five-year span to see the respective distribution of data within our categories.
Figure 7d
Figure 7d showcases the final bit of code before our output within this query. In it, I select all the rows from the temporary subset of data and show their respective distribution by five year spans.
Figure 7e
For what it’s worth, parity in terms of performance has not been a struggle over the last five years according to Figure 7e. This showcases that there is a strong spread of talent across the league. Furthermore, every team is respectively balanced in terms of play style, with only one consistently elite offensive team (the Celtics). At the same time though, the higher spread of balance within the league, as well as lack of consistently struggling teams, may reflect another reality – that teams themselves are ‘stably unstable.’
In the current age of the NBA, players hold greater leverage over franchises in contract negotiations. As such, players are likely to quickly change teams in free agency or be traded by franchises (like the Dallas Mavericks trading Luka Doncic to the Los Angeles Lakers) out of fear that they will eventually leave for nothing in return. Though parity is ultimately strong for the league, our analysis hints that parity achieved through ironic short-term volatility is a little different than pure parity. The league itself, one of our clients, may need to consider this dynamic to create rule changes for player/team contracts in order to create a more truly consistent product for viewers at home, rather than one where their favorite stars – often the chief marketing vehicles for the league – are prone to leave from location to location.
Query 8: Best & Worst Team Performances (Relative to Era)
Our next query is of special use for journalists and media organizations looking to measure the best and worst team performances relative to era. By using our database in this manner, companies built around basketball analysis and journalism can best create editorial and multimedia content tailored towards audiences that engage with data-driven storytelling. Topics, such as finding the greatest offensive and defensive teams of all-time are of special interest to basketball fans that consume multimedia content about the NBA.
Figure 8a
Figure 8a covers the start of our query in similar fashion to previous queries, as I calculate league-wide averages per season in offensive rating and defensive rating before then subsetting our original dataset even further by selecting only a few columns, and creating relative categories for offensive and defensive rating.
Figure 8b
Figure 8b continues our queries as I created another subset of data using rank, over, order by, and a mix of descending and ascending functions to filter our data accordingly. After that, I used select, from, where, or, order by, and group by statements to make our output accessible and filterable in that manner.
Figure 8c
Figure 8c showcases a further subset of our relative performance query by calling it again and then assigning quartile categorizations made via a combination of window functions previously used and basic functions.
Figure 8d
Figure 8d shows how I aggregate a summary by quartiles created in the previous step, as well as creating another column for average wins per each group in their respective category.
Figure 8e
Our first output, showcased in Figure 8e, is a list of the greatest offensive teams, relative to league standards on offensive rating, in our database. This information can shape contemporary coverage of NBA teams and contenders, as the 2024 Boston Celtics were a historically strong offensive team, as well as a historically great all-time team. Coverage of the 2025 Celtics, as a result, should keep this in mind that the team isn’t just following a typical championship team; it’s following one of the most dominant teams (and offenses) in NBA history. In similar fashion, coverage of other teams could be historically contextualized for greater analysis, which would lead to greater audience engagement.
Figure 8f
Our second output, Figure 8f, is proof of how anyone could further customize queries to not only categorize teams, but place them in quartiles. Media organizations covering professional basketball can use this to determine what type of results teams are trending for, as well as further diversity their analysis by finding key ‘breaking points’ for what successful teams look like across these metrics. For example, a team that may have less than 54 wins, according to this analysis, can still potentially be a tier 1 team if their point differential spreads (in relative net rating) fall within the criteria shown above.
Query 9: Identifying Outlier Performances & Null Data Issues
As a modified version of Query 8, I made this query to fulfill assignment requirements on remaining methods to showcase within our database.
Figure 9a
Figure 9a shows the creation of a data subset that uses “if,” “is null,” and “or” statements to ensure that there aren’t any null values within the data set before finding the best offensive rating and respective defensive ratings in league history. I then use “in” and “select distinct” calls to ensure that every season in our reference table is called.
Figure 9b
In Figure 9b, I used a union call to mix our tables together. The first table is filtered to teams with the max offensive rating and minimum defensive rating (with lower values being better) of each season, while the second table (with its calculated data status field) shows proof that none of these teams have null data.
Figure 9c
Figure 9c confirms this as a modified version of Query 8, but made to validate that I have a complete dataset for a specific subquery for teams that have the respective best offense and best defense of each year. This is of particular use for media organizations looking to shape contemporary NBA coverage with historical comparison points across league history.
Conclusion
Through SQL analysis, I’ve uncovered significant trends in NBA team performance. Offensive efficiency has become increasingly crucial in modern basketball, but defensive impact still holds weight. Play styles have evolved, with a shift toward fast-paced, high-scoring offenses, but also more conservative, disruptive, yet tactical perimeter defensive play based around team-wide versatility rather than individual playmaking. This is of particular interest for front offices, and as showcased in Query 6, even the league itself.
Meanwhile, our further queries showcase how media organizations can leverage our consulting to contextualize their current coverage of the NBA with greater historical stakes and accuracy. Ultimately, the SQL functions implemented in this report—ranging from complex joins to advanced aggregations—demonstrate the power of structured data analysis in sports industry decision-making.
I would say that I hope I never use SQL again, because this was truly a painful process that made me the data and want to kill the concept of database management forever. However, I am a masochist, and given the industry I’m trying to break into, chances are that I will run into my old enemy once again. Until that day though, cheers.