SQL DB from NFLDB

[iliketowel]

Hi all, this is less of an "issue" and more of a question, but I'm not quite sure of the best place to put it. I created a query to include most plays/results of the games. But I wanted to confirm that I'm pulling the data the best way possible. This is the (rather long) query I've made.

You'll see that I pull from PlayPlayer, and then pull the other data from there. Is this the best place to get the most accurate information?

Thanks, Eric

SELECT P.player_id, P.profile_url, P.gsis_name, P.full_name, P."position",
CASE WHEN P.birthdate = '' THEN '1/1/1900' WHEN P.birthdate = '//' THEN '1/1/1900' Else P.birthdate End as "birthdate",
P.years_pro, G.gsis_id, G.gsis_ID || PP.play_id as "GameplayID", PP.team, PP.passing_att, PP.passing_cmp, PP.passing_yds, PP.passing_tds, PP.passing_int, PP.passing_cmp_air_yds, PP.passing_twopta, PP.passing_twoptm, PP.passing_sk, PP.passing_sk_yds, PP.passing_incmp, PP.passing_incmp_air_yds, CASE WHEN PL.description like '% left%' THEN 'Left' WHEN PL.description like '% middle%' THEN 'Middle' WHEN PL.description like '% right%' THEN 'Right' END as "play_direction", PP.receiving_tar, PP.receiving_rec, PP.receiving_yds, PP.receiving_tds, PP.receiving_yac_yds, PP.receiving_twopta, PP.receiving_twoptm, PP.rushing_att, PP.rushing_yds, PP.rushing_tds, PP.rushing_twopta, PP.rushing_twoptm, PP.rushing_loss, PP.rushing_loss_yds, G.start_time, G.week, G.day_of_week, G.season_year, G.season_type, G.home_team, G.home_score, G.away_team, G.away_score, PP.fumbles_tot, PP.fumbles_lost, PP.defense_qbhit, PP.defense_tkl_loss, PP.defense_tkl_loss_yds, PP.defense_sk, PP.defense_sk_yds, PP.defense_ffum, PP.defense_frec, PP.defense_frec_yds, PP.defense_frec_tds, PP.defense_int, PP.defense_int_tds, PP.defense_int_yds, PL.time, PL.pos_team, PL.yardline, PL.down, PL.yards_to_go, PL.first_down, PL.penalty, PL.penalty_yds, PL.description, CASE WHEN PL.description like '%(Shotgun)%' THEN 'Shotgun' END as "Shotgun", PL.Note FROM public.play_player PP, public.game G, public.player P, public.play PL WHERE PP.player_id = P.player_id AND G.gsis_id = PP.gsis_id AND G.gsis_id = PL.gsis_id AND PL.play_id = PP.play_id AND (PP.passing_att > 0 or PP.rushing_att > 0 or PP.receiving_tar > 0 or PP.passing_sk > 0 or PP.passing_twopta > 0 or PP.rushing_twopta > 0 or PL.note like 'TD' or PL.note like '%FUMBLE%' or PP.defense_qbhit > 0 or PP.defense_tkl_loss > 0 or PP.defense_tkl_loss_yds > 0 or PP.defense_sk > 0 or PP.defense_sk_yds > 0 or PP.defense_ffum > 0 or PP.defense_frec > 0 or PP.defense_frec_yds > 0 or PP.defense_frec_tds > 0 or PP.defense_int > 0 or PP.defense_int_tds > 0 or PP.defense_int_yds > 0) ORDER BY G.start_time, G.gsis_id, PL.time

[BurntSushi]

Could you describe your query in English? It looks like this could be much simpler if you use the nfldb.Query interface.

[iliketowel]

I was trying to pull in every statistic recorded, other than tackles. I was trying to tie every play to public.play_player so that I would have all recorded statistics, however based on what I've seen and played with, the public.play_player may not be 100% accurate. I'm had thought you had written before that there was another way to get more accurate statistical data.

On a football level... I have to mentally get over the Jets loss on Sunday... it was very... Jetlike.

[BurntSushi]

There is no way to freely access 100% accurate data. If you really need 100%, then you've got to pay Stats, Inc. for it.

I don't have a figure on the accuracy of nfldb's data, but a very rough guess is >95%.

There are some ideas I have for future work on increasing accuracy, but they will not happen any time soon.

I will try to respond again when I have more time to actually answer your question here. :-)