arrow_back

Exploring NCAA Data with BigQuery

로그인 가입
Quick tip: Review the prerequisites before you run the lab
Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the student account, which may cause extra charges incurred to your personal account.
지식을 테스트하고 커뮤니티와 공유하기
done
700개 이상의 실무형 실습, 기술 배지, 과정에 액세스

Exploring NCAA Data with BigQuery

실습 45분 universal_currency_alt 무료 show_chart 입문
info 이 실습에는 학습을 지원하는 AI 도구가 통합되어 있을 수 있습니다.
지식을 테스트하고 커뮤니티와 공유하기
done
700개 이상의 실무형 실습, 기술 배지, 과정에 액세스

GSP160

Google Cloud self-paced labs logo

Overview

BigQuery is Google's fully managed, NoOps, low cost analytics database. With BigQuery you can query terabytes and terabytes of data without managing infrastructure or needing a database administrator. BigQuery uses SQL and takes advantage of the pay-as-you-go model. BigQuery allows you to focus on analyzing data to find meaningful insights.

We have a newly available dataset for NCAA Basketball games, teams, and players. The game data covers play-by-play and box scores back to 2009, as well as final scores back to 1996. Additional data about wins and losses goes back to the 1894-5 season in some teams' cases.

In this lab we will find and query the NCAA dataset using BigQuery.

What you'll learn

  • Using BigQuery
  • Query the NCAA Public Dataset
  • Writing and executing queries

What you'll need

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources are made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

  • Access to a standard internet browser (Chrome browser recommended).
Note: Use an Incognito (recommended) or private browser window to run this lab. This prevents conflicts between your personal account and the student account, which may cause extra charges incurred to your personal account.
  • Time to complete the lab—remember, once you start, you cannot pause a lab.
Note: Use only the student account for this lab. If you use a different Google Cloud account, you may incur charges to that account.

How to start your lab and sign in to the Google Cloud console

  1. Click the Start Lab button. If you need to pay for the lab, a dialog opens for you to select your payment method. On the left is the Lab Details pane with the following:

    • The Open Google Cloud console button
    • Time remaining
    • The temporary credentials that you must use for this lab
    • Other information, if needed, to step through this lab

  2. Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).

    The lab spins up resources, and then opens another tab that shows the Sign in page.

    Tip: Arrange the tabs in separate windows, side-by-side.

    Note: If you see the Choose an account dialog, click Use Another Account.

  3. If necessary, copy the Username below and paste it into the Sign in dialog.

    {{{user_0.username | "Username"}}}

    You can also find the Username in the Lab Details pane.

  4. Click Next.

  5. Copy the Password below and paste it into the Welcome dialog.

    {{{user_0.password | "Password"}}}

    You can also find the Password in the Lab Details pane.

  6. Click Next.

    Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

  7. Click through the subsequent pages:

    • Accept the terms and conditions.
    • Do not add recovery options or two-factor authentication (because this is a temporary account).
    • Do not sign up for free trials.

After a few moments, the Google Cloud console opens in this tab.

Note: To access Google Cloud products and services, click the Navigation menu or type the service or product name in the Search field. Navigation menu icon and Search field

Open the BigQuery console

  1. In the Google Cloud Console, select Navigation menu > BigQuery.

The Welcome to BigQuery in the Cloud Console message box opens. This message box provides a link to the quickstart guide and the release notes.

  1. Click Done.

The BigQuery console opens.

BigQuery opens, but there's nothing in here! Luckily, there are tons of Open Datasets available in BigQuery for you to query, and of course you can upload your own data, which you'll do in the next section.

Task 1. Find the NCAA public dataset in BigQuery

In this section, you pull in some public data so you can practice running SQL commands in BigQuery.

  1. Click on the + ADD then select Public Datasets:

Expanded Add Data dropdown menu with Explore public datasets option highlighted

  1. Type ncaa basketball in the search bar and press Enter.

  2. Click on the NCAA Basketball tile, then View Dataset.

Note: A new browser tab opens, you now have a new project called bigquery-public-data added to the Explorer panel, opened to ncaa_basketball

DatasetInfo

  1. If bigquery-public-data is not present in explorer panel, click on the + ADD then select Star a project by name.

  2. Type bigquery-public-data and click STAR .

  3. Click on the bigquery-public-data > ncaa basketball to view the tables you can explore.

Explorer panel with ncaa_basketball dataset highlighted and various tables listed below the dataset

  1. Click on mbb_games_sr (men's NCAA game results table) and then click the Preview tab to see sample rows of data. Click the Details tab to get metadata about the table.

  2. Click the Details tab to get metadata about the table.

TableInfo

Question: How many games does the dataset contain? How big is the table?

Answer: The table is about 50 MB and there are over 29k games for us to explore.

Question: But how many individual plays can we analyze?

Hint:

  • Click on the mbb_pbp_sr (play-by-play) dataset.

ncaa dataset with mbb_pbp_sr table highlighted

  • Then click Details.

mbb_pbr_sr_Details

Answer: Over 4 million individual play basketball.

Let’s write some SQL to see what types of plays are there for us to explore.

Task 2. Writing queries

What types of basketball play events are there?

  1. Click "+" (Compose New Query) icon.

  2. Copy and paste the below query into the editor:

#standardSQL SELECT event_type, COUNT(*) AS event_count FROM `bigquery-public-data.ncaa_basketball.mbb_pbp_sr` GROUP BY 1 ORDER BY event_count DESC;
  1. Now click Run.

Looking at your results, how many historical shots were TWOPOINTMADE or FREETHROWMISS?

Query results table with columns for Row, event_type, and event_count. Data in rows 4 and 11 are highlighted

Click Check my progress to verify the objective.

Writing queries

Task 3. Fun queries to run

Which 5 games featured the most three point shots made? How accurate were all the attempts?

  1. Click "+" (Compose New Query) icon and add in the below query:
#standardSQL #most three points made SELECT scheduled_date, name, market, alias, three_points_att, three_points_made, three_points_pct, opp_name, opp_market, opp_alias, opp_three_points_att, opp_three_points_made, opp_three_points_pct, (three_points_made + opp_three_points_made) AS total_threes FROM `bigquery-public-data.ncaa_basketball.mbb_teams_games_sr` WHERE season > 2010 ORDER BY total_threes DESC LIMIT 5;
  1. Click Run.

Query results table

Wow! The Tigers made over 50% of their three point shots on 11-22-2016.

Click Check my progress to verify the objective.

Query 1

Which 5 basketball venues have the highest seating capacity?

  1. Click "+" (Compose New Query) icon and add the below query:
#standardSQL SELECT venue_name, venue_capacity, venue_city, venue_state FROM `bigquery-public-data.ncaa_basketball.mbb_teams_games_sr` GROUP BY 1,2,3,4 ORDER BY venue_capacity DESC LIMIT 5;
  1. Click Run.

Query results table

Imagine taking a shot with 80,000 people watching you!

Click Check my progress to verify the objective.

Query 2

Which teams played in the highest scoring game since 2010?

  1. Click "+" (Compose New Query) icon and add the below query:
#standardSQL #highest scoring game of all time SELECT scheduled_date, name, market, alias, points_game AS team_points, opp_name, opp_market, opp_alias, opp_points_game AS opposing_team_points, points_game + opp_points_game AS point_total FROM `bigquery-public-data.ncaa_basketball.mbb_teams_games_sr` WHERE season > 2010 ORDER BY point_total DESC LIMIT 5;
  1. Click Run.

Query results table

The Bulldogs and Terriers played in a game that scored 258 total points!

Click Check my progress to verify the objective.

Query 3

Since 2015, what was the biggest difference in final score for a National Championship?

  1. Click "+" (Compose New Query) icon and add the below query:
#standardSQL #biggest point difference in a championship game SELECT scheduled_date, name, market, alias, points_game AS team_points, opp_name, opp_market, opp_alias, opp_points_game AS opposing_team_points, ABS(points_game - opp_points_game) AS point_difference FROM `bigquery-public-data.ncaa_basketball.mbb_teams_games_sr` WHERE season > 2015 AND tournament_type = 'National Championship' ORDER BY point_difference DESC LIMIT 5;
  1. Click Run.

Query results table

The finals games are surprisingly close! The biggest difference was in 2018 with a delta of 17 points.

Click Check my progress to verify the objective.

Query 4

Congratulations!

You've learned how to query the NCAA basketball dataset inside of BigQuery. We encourage you to modify the above queries and write your own to further your understanding. Looking for more NCAA query practice? Checkout the GitHub repo here.

Next steps / learn more

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated November 05, 2024

Lab Last Tested November 05, 2024

Copyright 2025 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.

Before you begin

  1. Labs create a Google Cloud project and resources for a fixed time
  2. Labs have a time limit and no pause feature. If you end the lab, you'll have to restart from the beginning.
  3. On the top left of your screen, click Start lab to begin

Use private browsing

  1. Copy the provided Username and Password for the lab
  2. Click Open console in private mode

Sign in to the Console

  1. Sign in using your lab credentials. Using other credentials might cause errors or incur charges.
  2. Accept the terms, and skip the recovery resource page
  3. Don't click End lab unless you've finished the lab or want to restart it, as it will clear your work and remove the project

현재 이 콘텐츠를 이용할 수 없습니다

이용할 수 있게 되면 이메일로 알려드리겠습니다.

감사합니다

이용할 수 있게 되면 이메일로 알려드리겠습니다.

One lab at a time

Confirm to end all existing labs and start this one

Setup your console before you begin

Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.