Skip to content

Unfair Venues Solution

SELECT 
  venue_name,
  COUNT(*) AS games,
  ROUND(AVG(h_personal_fouls), 2) as home_personal_fouls,
  ROUND(AVG(a_personal_fouls), 2) as away_personal_fouls,
  ROUND(AVG(a_personal_fouls) - AVG(h_personal_fouls), 2) as diff_personal_fouls
FROM `bigquery-public-data.ncaa_basketball.mbb_games_sr`
WHERE season = 2016
GROUP BY venue_name
HAVING games >= 10
ORDER BY diff_personal_fouls DESC

Explanation

Nothing special here - just a plethora of basic SQL functions and clauses used together.

Bonus

Creating a view from a query in BigQuery is easy enough!

  1. Click Save > Save View
  2. Select a project and dataset, and enter a "table" (view) name

Access Denied Error 🤦‍♂️

If you get an error like this one,

It's probably because you're attempting to save the view in a different data location than the source table. In BigQuery, views must reside in the same location as their source table. In this case, the source data lives in the US region, so you'll need to save the view in a dataset that also lives in the US region.

Back Next