SQL Practice in BigQuery 🙋

We all know the situation: at work, we have the most advanced tools, our setup is comfortable, and everything is great. But when it comes to personal projects, something else gets in the way—whether it’s a computer that isn’t working, the development environment, or the software on our machine. Today, I’ll talk about how we can take a schema, build a fictional dataset, run SQL in Google BigQuery, and answer some questions. The questions we’ll answer come from Arena Games - Data Analysis, created by Ram Kedem and published for practice. All credit for the questions goes to UpScale Analytics.

The database structure includes data about games and players’ seasons:

Datacamp #

Before we begin, I recommend “SQL Fundamentals” on Datacamp. It’s a collection of courses that teach you how to write complex queries, with short videos and practice throughout. I used Workflowy to take notes on the courses.

Google BigQuery #

Google BigQuery is one of the most advanced tools on the market for querying large databases quickly and easily. Let’s create a dataset and build it step by step:

Go to Google Cloud and sign up with your Google account.
Open BigQuery and create a new, empty dataset. I called mine “Arena.”
The link I shared above includes the database schema. It is designed for an MSSQL database, while BigQuery supports “BigQuery Standard SQL,” a slightly different version with changes and improvements. So we’ll need to convert the structure into a format BigQuery recognizes.
I copied the schema into ChatGPT (using the GPT-4 model) and asked it to perform the necessary conversion into a format BigQuery recognizes. I’ve included the result below. Note that Arena is preceded by the project ID containing the dataset we’re building.

-- Structure for game_sessions table
CREATE TABLE `teak-ellipse-401213.Arena.game_sessions` (
    session_id INT64 NOT NULL,
    session_begin_date TIMESTAMP,
    session_end_date TIMESTAMP,
    player_id INT64,
    game_id INT64
);

-- Structure for games table
CREATE TABLE `teak-ellipse-401213.Arena.games` (
    id INT64 NOT NULL,
    game_name STRING
);

-- Structure for paying_method table
CREATE TABLE `teak-ellipse-401213.Arena.paying_method` (
    player_id INT64 NOT NULL,
    credit_card_type STRING NOT NULL,
    credit_card_number STRING NOT NULL
);

-- Structure for players table
CREATE TABLE `teak-ellipse-401213.Arena.players` (
    player_id INT64 NOT NULL,
    first_name STRING,
    last_name STRING,
    email_address STRING,
    gender STRING,
    age_group STRING,
    country STRING,
    city STRING,
    street_address STRING
);

-- Structure for session_details table
CREATE TABLE `teak-ellipse-401213.Arena.session_details` (
    session_id INT64 NOT NULL,
    action_id INT64 NOT NULL,
    action_type STRING NOT NULL,
    amount NUMERIC
);

Now that we’ve created the table structures, all that’s left is to generate synthetic data using ChatGPT. I think it’s important to stick as closely as possible to the original database format, so I included examples in the prompt, including some from the database file. I then built Python functions that helped generate queries to insert the data into our tables using the Faker library:
1. generate_game_sessions - A function that generates the sessions. We have a start and end date, a player, and a game.
2. generate_games - A function that generates the games. We have the game’s ID and name.
3. generate_paying_method - A function that generates the payment method for the game. Faker even supports generating a fictional credit card number.
4. generate_players - A function that generates the players and their identifying details.
5. generate_session_details - A function that generates the details for each session and its profit/loss data.

from faker import Faker
import random

fake = Faker()

def generate_game_sessions(num_rows=100):
    query = "INSERT INTO `teak-ellipse-401213.Arena.game_sessions` (session_id, session_begin_date, session_end_date, player_id, game_id) VALUES "
    values = []
    for i in range(1, num_rows + 1):
        session_id = i
        begin_date = fake.date_time_this_decade()
        end_date = fake.date_time_between_dates(begin_date)
        player_id = random.randint(1, 100)
        game_id = random.randint(1, 4)
        values.append(f"({session_id}, TIMESTAMP('{begin_date}'), TIMESTAMP('{end_date}'), {player_id}, {game_id})")
    query += ", ".join(values)
    return query

def generate_games(num_rows=100):
    query = "INSERT INTO `teak-ellipse-401213.Arena.games` (id, game_name) VALUES "
    values = []
    for i in range(1, num_rows + 1):
        game_name = fake.word()
        values.append(f"({i}, '{game_name}')")
    query += ", ".join(values)
    return query

def generate_paying_method(num_rows=100):
    query = "INSERT INTO teak-ellipse-401213.Arena.paying_method (player_id, credit_card_type, credit_card_number) VALUES "
    values = []
    card_types = ['visa', 'mastercard', 'amex']
    for i in range(1, num_rows + 1):
        player_id = random.randint(1, 100)
        card_type = random.choice(card_types)
        card_number = fake.credit_card_number(card_type=card_type)
        values.append(f"({player_id}, '{card_type}', '{card_number}')")
    query += ", ".join(values)
    return query

def generate_players(num_rows=100):
    query = "INSERT INTO `teak-ellipse-401213.Arena.players` (player_id, first_name, last_name, email_address, gender, age_group, country, city, street_address) VALUES "
    values = []
    for i in range(1, num_rows + 1):
        first_name = fake.first_name()
        last_name = fake.last_name()
        email = fake.email()
        gender = random.choice(['Male', 'Female'])
        age_group = random.choice(['10-21', '21-30', '31-40', '41-50', '51-60'])
        country = fake.country()
        city = fake.city()
        address = fake.street_address()
        values.append(f"({i}, '{first_name}', '{last_name}', '{email}', '{gender}', '{age_group}', '{country}', '{city}', '{address}')")
    query += ", ".join(values)
    return query

def generate_session_details(num_rows=100):
    query = "INSERT INTO `teak-ellipse-401213.Arena.session_details` (session_id, action_id, action_type, amount) VALUES "
    values = []
    action_types = ['gain', 'loss']
    for i in range(1, num_rows + 1):
        session_id = random.randint(1, 100)
        action_id = i
        action_type = random.choice(action_types)
        amount = round(random.uniform(100, 1000), 2)
        values.append(f"({session_id}, {action_id}, '{action_type}', {amount})")
    query += ", ".join(values)
    return query

# Function to get all the insert queries
def get_insert_queries(num_rows=100):
    return {
        'game_sessions': generate_game_sessions(num_rows),
        'games': generate_games(num_rows),
        'paying_method': generate_paying_method(num_rows),
        'players': generate_players(num_rows),
        'session_details': generate_session_details(num_rows)
    }

# Get all insert queries
insert_queries = get_insert_queries(100)

# File path where you want to save the queries
file_path = "insert_queries.sql"

# Open the file in write mode
with open(file_path, 'w') as file:
    # Iterate over each table and its corresponding query
    for table, query in insert_queries.items():
        # Write the comment and the query to the file
        file.write(f'-- Insert data into {table}\n')
        file.write(query)
        file.write('\n\n')

After running this code, we’ll get 5 queries saved in a file called “insert_queries.sql.” We’ll run the queries in BigQuery, and then we’ll be ready to start answering the questions.

-- Insert data into game_sessions
INSERT INTO `teak-ellipse-401213.Arena.game_sessions` (session_id, session_begin_date, session_end_date, player_id, game_id) VALUES (1, TIMESTAMP('2022-04-18 07:48:12')

-- Insert data into games
INSERT INTO `teak-ellipse-401213.Arena.games` (id, game_name) VALUES (1, 'letter')

-- Insert data into players
INSERT INTO `teak-ellipse-401213.Arena.players` (player_id, first_name, last_name, email_address, gender, age_group, country, city, street_address) VALUES (1, 'Misty', 'Harper', 'melissawarner@example.net', 'Female', '41-50', 'Western Sahara', 'Howardstad', '53600 Craig Key')

-- Insert data into paying_method
INSERT INTO `teak-ellipse-401213.Arena.paying_method` (player_id, credit_card_type, credit_card_number) VALUES (2, 'visa', '4812645610428841')

-- Insert data into session_details
INSERT INTO `teak-ellipse-401213.Arena.session_details` (session_id, action_id, action_type, amount) VALUES (36, 1, 'gain', 890.87)

Now that we’ve set up and populated the database, we can start running queries.

Questions #

As I work through the questions, I’ll add more detailed notes about my approach whenever I feel they’re needed. If you’d like to see another approach to solving them and a more in-depth explanation, I recommend watching the Livestream by my good friend Amit Grinson, who solved the same questions a few months ago.

Question One - Payment Ranking #

Create a report that displays for each player a single payment method, according to the following preference: American Express, Mastercard, Visa. That is, if the player has an American Express payment method, we will display it. Otherwise, we will display Mastercard, and if none of the above, we will display Visa.

To solve this question, I used ROW_NUMBER(), which helped me number the credit cards in order for each player’s PARTITION. With this ordering, I could filter for the first position, payment_index, in the query itself, giving me the player’s payment method according to the requested order.

WITH PAYMENT_RANK AS (
  SELECT 
    player_id,
    credit_card_number,
    credit_card_type,
    ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY CASE credit_card_type 
                                              WHEN "visa" THEN 1
                                              WHEN "mastercard" THEN 2
                                              WHEN "amex" THEN 3
                                              ELSE 4 END) AS payment_index
  FROM `teak-ellipse-401213.Arena.paying_method`
)

SELECT
  payment_rank.player_id,
  players.email_address,
  payment_rank.credit_card_type,
  payment_rank.credit_card_number
FROM PAYMENT_RANK payment_rank
JOIN `teak-ellipse-401213.Arena.players` players
  ON payment_rank.player_id = players.player_id
WHERE payment_rank.payment_index = 1;

#

Question Two - Payment Grouping #

Create a report that displays the number of players who carry each type of card. Break it down by each gender and age group.

What I found interesting about this question was creating the `PIVOT`: before the `FOR`, we specify which aggregate operation we want to perform, and after it, we specify what to apply it to.

-- Question Number 2: Payment Grouping
WITH players_payment AS (
  SELECT
    players.player_id AS player_id,
    gender,
    credit_card_type,
    age_group
  FROM `teak-ellipse-401213.Arena.paying_method` AS payment
  JOIN `teak-ellipse-401213.Arena.players` as players
    ON payment.player_id = players.player_id
)

SELECT * FROM players_payment
PIVOT (
  COUNT(DISTINCT player_id) 
  FOR credit_card_type IN ('visa', 'mastercard', 'amex')
)

Question Three - Games per Session #

Display the number of sessions for each game. Rank the output according to the number of game sessions, from highest to lowest.

-- Question Number 3: Games per Session
WITH session_rank AS (
  SELECT 
    game_name, 
    COUNT(game_id) AS num_sessions
  FROM `teak-ellipse-401213.Arena.game_sessions` as game_sessions
  JOIN `teak-ellipse-401213.Arena.games` as games
  ON game_sessions.game_id = games.ID
  GROUP BY game_name
)

SELECT
  game_name,
  num_sessions,
  RANK() OVER (ORDER BY num_sessions DESC) AS row_n
FROM session_rank
ORDER BY num_sessions DESC

Question Four - Total Game Duration #

Rank the games according to the total amount of minutes played in each one.

-- Question Number 4: Total Game Duration
SELECT
  game_name,
  duration,
  RANK() OVER (ORDER BY duration DESC) AS row_n
FROM (
  SELECT 
    game_id,
    SUM(TIMESTAMP_DIFF(session_end_date, session_begin_date, MINUTE)) AS duration
  FROM `teak-ellipse-401213.Arena.game_sessions` as game_sessions
  GROUP BY game_id
) AS games_durations
JOIN `teak-ellipse-401213.Arena.games` as games
ON games_durations.game_id = games.ID
ORDER BY duration DESC

Question Five - Duration per Age Group #

For each age-group display the game in which most time was spent

-- Question Number 5: Duration per Age Group
WITH games_durations AS (
  SELECT 
    age_group,
    game_name,
    SUM(TIMESTAMP_DIFF(session_end_date, session_begin_date, MINUTE)) AS duration
  FROM `teak-ellipse-401213.Arena.game_sessions` as game_sessions
  JOIN `teak-ellipse-401213.Arena.players` as players
    ON game_sessions.player_id = players.player_id
  JOIN `teak-ellipse-401213.Arena.games` as games
    ON game_sessions.game_id = games.ID 
  GROUP BY age_group, game_name
)

SELECT
  age_group,
  game_name,
  duration
FROM (
  SELECT
    age_group,
    game_name,
    duration,
    ROW_NUMBER() OVER (PARTITION BY game_name ORDER BY duration DESC) AS rank
  FROM games_durations
)
WHERE rank = 1;

Question Six - Balance per Game #

Display the balance throughout each game session.

-- Question Number 6: Balance per Game
SELECT
  session_id,
  action_id,
  action_type,
  amount,
  SUM(
    CASE 
      WHEN action_type = "gain" THEN amount
      ELSE -amount
    END
  ) OVER (ORDER BY session_id, action_id) AS total_amount
FROM `teak-ellipse-401213.Arena.session_details` AS session_details
ORDER BY session_id, action_id

Question Seven - Action Type Stats #

How many game sessions ended with a profit, how many game sessions ended with a loss, and how many ended in a draw?

Honestly, I prefer to avoid hard-coding column values in the query itself. This might be a classic case for integrating Python to check the values in the `action_type` column. For our purposes, I checked them in a separate query.

-- Question Number 7: Action Type Stats
-- Check distinct values of 'action_type'
SELECT DISTINCT action_type
FROM `teak-ellipse-401213.Arena.session_details` AS session_details;

SELECT 
  COUNTIF(action_type = 'gain') AS profit_total,
  COUNTIF(action_type = 'loss') AS loss_total
FROM `teak-ellipse-401213.Arena.session_details` AS session_details;

Question Eight - Game Sessions Stats #

How many game sessions ended with a profit, how many game sessions ended with a loss, and how many ended in a draw. Break down the result for each gender and age group.

-- Question Number 8: Game Sessions Stats
WITH game_stats AS (
  SELECT
    gender,
    age_group,
    action_type,
    game_id
  FROM`teak-ellipse-401213.Arena.session_details` AS session_details
  JOIN`teak-ellipse-401213.Arena.game_sessions` AS game_sessions
    ON session_details.session_id = game_sessions.session_id
  JOIN`teak-ellipse-401213.Arena.players` AS players
    ON game_sessions.player_id = players.player_id
)

SELECT * FROM game_stats
PIVOT (
  COUNT(DISTINCT game_id)
  FOR action_type IN ('gain', 'loss')
)
ORDER BY gender, age_group

Question Nine - Total Profit/Loss for each player #

What is the total profit/loss amount for each player?

-- Question Number 9: Total Profit/Loss for each player
SELECT 
  player_id,
  SUM(profit) AS total_gain_loss
FROM (
  SELECT
    players.player_id,
    IF(action_type = 'gain', amount, -amount) AS profit,
  FROM`teak-ellipse-401213.Arena.session_details` AS session_details
  JOIN`teak-ellipse-401213.Arena.game_sessions` AS game_sessions
    ON session_details.session_id = game_sessions.session_id
  JOIN`teak-ellipse-401213.Arena.players` AS players
    ON game_sessions.player_id = players.player_id
)
GROUP BY player_id

Question Ten - House Profit #

When a player wins, the house loses, and when a player loses, the house wins. Based on the available information, is the company currently in profit or loss?

At first, I wasn’t sure how to approach this question, and I started building subqueries. After a while, I realized it was a classic case of aggregate operations with `CASE` inside them.

-- Question Number 10: House Profit
SELECT
  SUM(CASE WHEN action_type = 'loss' THEN amount ELSE 0 END) AS house_gains,
  -SUM(CASE WHEN action_type = 'gain' THEN amount ELSE 0 END) AS house_losses,
  SUM(CASE WHEN action_type = 'loss' THEN amount ELSE 0 END) - 
  SUM(CASE WHEN action_type = 'gain' THEN amount ELSE 0 END) AS overall_gain_loss
FROM `teak-ellipse-401213.Arena.session_details`;

Question Eleven - House Earnings by Quarters #

Present the company's profits/losses by year and quarter.

In this query, I first noticed a feature unique to BigQuery: you don’t have to repeat the aggregate operations from the `SELECT` inside the `GROUP BY`. It’s a clever feature I didn’t know existed, and it makes the code more readable.

-- Question Number 11: House Earnings by Quarters
SELECT
  EXTRACT(YEAR FROM session_begin_date) as year,
  EXTRACT(QUARTER FROM session_begin_date) as quarter,
  SUM(CASE WHEN action_type = 'loss' THEN amount ELSE 0 END) AS house_gains,
  -SUM(CASE WHEN action_type = 'gain' THEN amount ELSE 0 END) AS house_losses,
  SUM(CASE WHEN action_type = 'loss' THEN amount ELSE 0 END) - 
  SUM(CASE WHEN action_type = 'gain' THEN amount ELSE 0 END) AS overall_gain_loss
FROM `teak-ellipse-401213.Arena.session_details` AS session_details
JOIN `teak-ellipse-401213.Arena.game_sessions` AS game_sessions
  ON session_details.session_id = game_sessions.session_id
GROUP BY year, quarter -- using aliases in GROUP BY, unique to BigQuery
ORDER BY year, quarter

Question Twelve - Best / Worst 3 Months #

Present the company's top 3 best and worst months (in terms of profit and loss).

I decided to split the query into three parts: first, retrieve the data as in the previous question. Then, add numbering, and in the third part, retrieve the relevant numbered rows.

-- Question Number 12: Best / Worst 3 Months
  WITH MonthEarnings AS (
  SELECT
        EXTRACT(YEAR FROM session_begin_date) as year,
        EXTRACT(MONTH FROM session_begin_date) as month,
        SUM(CASE WHEN action_type = 'loss' THEN amount ELSE 0 END) AS house_gains,
        -SUM(CASE WHEN action_type = 'gain' THEN amount ELSE 0 END) AS house_losses,
        SUM(CASE WHEN action_type = 'loss' THEN amount ELSE 0 END) - 
        SUM(CASE WHEN action_type = 'gain' THEN amount ELSE 0 END) AS overall_gain_loss
      FROM `teak-ellipse-401213.Arena.session_details` AS session_details
      JOIN `teak-ellipse-401213.Arena.game_sessions` AS game_sessions
        ON session_details.session_id = game_sessions.session_id
      GROUP BY year, MONTH
      ORDER BY overall_gain_loss
  ),

  RankedResults AS (
    SELECT
      year,
      month,
      house_gains,
      house_losses,
      overall_gain_loss,
      ROW_NUMBER() OVER (ORDER BY overall_gain_loss ASC) as asc_row_num,
      ROW_NUMBER() OVER (ORDER BY overall_gain_loss DESC) as desc_row_num
    FROM MonthEarnings
  )

SELECT
  year,
  month,
  house_gains,
  house_losses,
  overall_gain_loss,
  CASE 
    WHEN asc_row_num <= 3 THEN CONCAT('Loss bottom ', CAST(asc_row_num AS STRING))
    WHEN desc_row_num <= 3 THEN CONCAT('Gain bottom ', CAST(desc_row_num AS STRING))
    ELSE NULL
  END as indication
FROM RankedResults
WHERE asc_row_num <= 3 OR desc_row_num <= 3

Summary #

I highly recommend using BigQuery for personal projects and learning. Personally, I learned quite a bit from this project, and I hope I’ve managed to convey what makes this tool unique and what it can do.