תרגול SQL ב-BigQuery 🙋

-- 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
);

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')

-- 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)

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 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 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 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 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 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 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 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 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 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 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 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