Creating Ranked Position Time Series with SQL
The Los Angeles Lakers were the top NBA team in 2008 but the last in 2016. We wanted to compare their downfall to the rise of other teams in the league, so we built a comparison of each team’s rank in the standings over the past ten years.
Let’s explore how to create this chart of rankings of the teams in the western conference!
We have data of team, year, and number of wins, located in a table named nba_rankings_west. Here is a portion of what the nba_rankings_west looks like:
Using the rank function in SQL, we can build a new table with a rank column where the team that has the greatest number of wins will be ranked the highest.
, rank() over(partition by year order by wins asc)
We split up the data using partition by year, and order by the number of wins. This will create the ranking of best-to-worst team each year. If there is a tie for wins, then the tied rows receive the same rank. Using our SQL statement, we get our new table with the ranks:
We can plot the data on a line chart and compare the teams. We can see that some teams’ rankings are relatively stable: the Sacramento Kings hover near the end every year and the San Antonio Spurs have always been in the top half of the conference. Other teams have much greater volatility, the Golden State Warriors had a low point in 2010 - 2012 but then they shoot to the top in 2015 - 2016!
Want to discuss this article? Join the Periscope Data Community!