Example Queries

Here are a set of queries that can pull interesting data.

Triple Doubles

Here is a simple way to pull triple doubles for a player

SELECT SUM(td3) 
FROM player_game_log 
LEFT JOIN player ON player.player_id = player_game_log.player_id 
WHERE player.player_name = 'Russell Westbrook';

Shot Distance Relative To Seconds Remaining In A Period

select
	((minutes_remaining * 60) + seconds_remaining) as total_time_remaining,
	shot_distance
from shot_chart_detail scd
left join player on player.player_id = scd.player_id
where
	player.player_name = 'LeBron James' and 
	scd.shot_made_flag = false and 
	period <= 4
order by total_time_remaining desc;

Shot Distance Relative To Seconds Remaining In A Game

This is similar to the query above, but we introduce a formula based on the period of the shot to calculate the seconds into the game that the shot was made:

minutes_in_period = 12 * 60

(minutes_in_period * (5 - period)) - (minutes_in_period - ((minutes_remaining * 60) + seconds_remaining))

With that in mind, here is the query:

select
	(12 * 60 * (5 - period)) - ((12 * 60) - ((minutes_remaining * 60) + seconds_remaining)) as total_time_remaining,
	shot_distance
from shot_chart_detail scd
left join player on player.player_id = scd.player_id
where
	player.player_name = 'Stephen Curry' and 
	scd.shot_made_flag = true and 
	period <= 4
order by total_time_remaining desc;

Counting Misses

Want to find the players with the most misses?

SELECT p.player_name, count(*) AS total 
FROM play_by_play 
LEFT JOIN player p on p.player_id = player1_id 
WHERE 
  home_description LIKE 'MISS%' 
  OR visitor_description like 'MISS%' 
GROUP BY p.player_name 
ORDER BY total DESC;

How about by period?

SELECT p.player_name, count(*) AS total, period
FROM play_by_play
LEFT JOIN player p on p.player_id = player1_id
WHERE
  home_description LIKE 'MISS%'
  OR visitor_description like 'MISS%'
GROUP BY p.player_name, period
ORDER BY total DESC;

Unique Stat Lines

SELECT COUNT(*) FROM (
  SELECT DISTINCT fgm, fga, fgm, fga, fg3m, fg3a, ftm, fta, oreb, dreb, stl, bl
  FROM player_game_log
) AS stat_line