BigQuery Cookbook

create table `mltest-202903.bitcoin_playground.sample_transactions`  as (1)
select
    *
from
    `bigquery-public-data.crypto_bitcoin.transactions` (2)
where
    block_timestamp_month = '2020-05-01' (3)
    and rand() <= 0.01 (4)
;

create table `mltest-202903.bitcoin_playground.sample_blocks`  as
select
    *
from
    `bigquery-public-data.crypto_bitcoin.transactions`
where
    block_timestamp_month = '2020-05-01'
    and mod(block_number,100) = 1 (1)
;

create table `mltest-202903.bitcoin_playground.all_transactions_until_dec_04_2020`  as
select
    block_number,
    block_timestamp,
    input_count,
    output_count,
    input_value,
    output_value,
    is_coinbase,
    fee
from
    `bigquery-public-data.crypto_bitcoin.transactions`
where
    block_timestamp <= '2020-12-05'
;

drop table if exists `mltest-202903.bitcoin_playground.all_transactions_until_dec_04_2020`;

with hash_ct_by_block_num as ( (1)
  select
    block_number,
    count(*) as per_block_transaction_hash_ct
  from `mltest-202903.bitcoin_playground.all_transactions_until_dec_04_2020`
  group by
    block_number
)
select
  floor(per_block_transaction_hash_ct/100) as bkt_100, (2)
  count(distinct block_number) as block_ct
from hash_ct_by_block_num (3)
group by
  bkt_100
order by
  bkt_100
;

# function to create string label for the buckets
def create_label(val, bucket_width=100):
    return "[" + str(int(val)*bucket_width) + ',' + str((int(val)*bucket_width)+(bucket_width-1)) + "]"

df_hash_per_block['bkt_100_label'] = df_hash_per_block['bkt_100'].map(create_label) (1)

import plotly.express as px

fig = px.bar(df_hash_per_block,
  x='bkt_100_label',
  y='block_ct')

fig.show()

with hash_ct_by_block_num as (
  select
    extract(year from block_timestamp) as block_timestamp_year, (1)
    block_number,
    count(*) as per_block_transaction_hash_ct
  from `mltest-202903.bitcoin_playground.all_transactions_until_dec_04_2020`
  group by
    block_timestamp_year,
    block_number
)
select
  block_timestamp_year,
  avg(per_block_transaction_hash_ct) as avg_per_block_transaction_hash_ct (2)
from hash_ct_by_block_num
group by
  block_timestamp_year
order by
  block_timestamp_year
;

import plotly.express as px
fig = px.line(df_hash_per_block,
    x="block_timestamp_year",
    y="avg_per_block_transaction_hash_ct",
    title="Average transactions per block across the years")
fig.show()

with hash_ct_by_block_num as (
  select
    extract(year from block_timestamp) as block_timestamp_year, (1)
    block_number,
    count(*) as per_block_transaction_hash_ct
  from `mltest-202903.bitcoin_playground.all_transactions_until_dec_04_2020`
  group by
    block_timestamp_year,
    block_number
)
select
  block_timestamp_year,
  approx_quantiles(per_block_transaction_hash_ct, 100)[OFFSET (50)] as median_per_block_transaction_hash_ct (2)
from hash_ct_by_block_num
group by
  block_timestamp_year
order by
  block_timestamp_year
;

import plotly.express as px
fig = px.line(df_hash_per_block,
  x="block_timestamp_year",
  y="median_per_block_transaction_hash_ct",
  title="Median transactions per block across the years")
fig.show()

with hash_ct_by_block_num as (
  select
    extract(year from block_timestamp) as block_timestamp_year, (1)
    block_number,
    count(*) as per_block_transaction_hash_ct
  from `mltest-202903.bitcoin_playground.all_transactions_until_dec_04_2020`
  group by
    block_timestamp_year,
    block_number
)
select
  block_timestamp_year,
  avg(per_block_transaction_hash_ct) as avg_per_block_transaction_hash_ct,
-- Extract ordered statistics
  approx_quantiles(per_block_transaction_hash_ct, 100)[OFFSET (25)] as per_25_per_block_transaction_hash_ct,
  approx_quantiles(per_block_transaction_hash_ct, 100)[OFFSET (50)] as median_per_block_transaction_hash_ct,
  approx_quantiles(per_block_transaction_hash_ct, 100)[OFFSET (75)] as per_75_per_block_transaction_hash_ct,
  approx_quantiles(per_block_transaction_hash_ct, 100)[OFFSET (95)] as per_95_per_block_transaction_hash_ct,
  approx_quantiles(per_block_transaction_hash_ct, 100)[OFFSET (99)] as per_99_per_block_transaction_hash_ct
from hash_ct_by_block_num
group by
  block_timestamp_year
order by
  block_timestamp_year
;

import plotly.graph_objects as go

fig = go.Figure()

fig.add_trace(go.Scatter(
  x=df_hash_per_block['block_timestamp_year'],
  y=df_hash_per_block['avg_per_block_transaction_hash_ct'],
  mode='lines',
  name='Avg')
  )
fig.add_trace(go.Scatter(
  x=df_hash_per_block['block_timestamp_year'],
  y=df_hash_per_block['per25_per_block_transaction_hash_ct'],
  mode='lines',
  name='25th_percentile')
  )
fig.add_trace(go.Scatter(
  x=df_hash_per_block['block_timestamp_year'],
  y=df_hash_per_block['median_per_block_transaction_hash_ct'],
  mode='lines',
  name='Median')
  )
fig.add_trace(go.Scatter(
  x=df_hash_per_block['block_timestamp_year'],
  y=df_hash_per_block['per75_per_block_transaction_hash_ct'],
  mode='lines',
  name='75th_percentile')
  )
fig.add_trace(go.Scatter(
  x=df_hash_per_block['block_timestamp_year'],
  y=df_hash_per_block['per95_per_block_transaction_hash_ct'],
  mode='lines',
  name='95th_percentile')
  )
fig.add_trace(go.Scatter(
  x=df_hash_per_block['block_timestamp_year'],
  y=df_hash_per_block['per99_per_block_transaction_hash_ct'],
  mode='lines',
  name='99th_percentile')
  )

fig.show()

import plotly.graph_objects as go

y_vars = ['avg_per_block_transaction_hash_ct',
'per25_per_block_transaction_hash_ct', 'median_per_block_transaction_hash_ct',
'per75_per_block_transaction_hash_ct', 'per95_per_block_transaction_hash_ct',
'per99_per_block_transaction_hash_ct']

fig = go.Figure()

for y_var in y_vars:
  fig.add_trace(go.Scatter(
    x=df_hash_per_block['block_timestamp_year'],
    y=df_hash_per_block[y_var],
    mode='lines',
    name=y_var.split('_')[0]
  ))

# Figure titles
fig.update_layout(
    title="Statistics related of transactions per block",
    xaxis_title="Year",
    yaxis_title="Transactions per block"
)

fig.show()

select
    date(block_timestamp) as block_timestamp_date,
    count(*) as ct_transactions
  from `mltest-202903.bitcoin_playground.all_transactions_until_dec_04_2020`
  group by
    block_timestamp_date
order by
  block_timestamp_date
;

import plotly.express as px

fig = px.line(df_hash_per_block,
    x="block_timestamp_date",
    y="ct_transactions",
    title="Daily Transactions")

fig.show()

with daily_transactions as (
    select
      date(block_timestamp) as block_timestamp_date,
      count(*) as ct_transactions
    from `mltest-202903.bitcoin_playground.all_transactions_until_dec_04_2020`
    group by
      block_timestamp_date
)
select
  block_timestamp_date,
  ct_transactions,
  AVG(ct_transactions) (1)
    OVER ( (2)
      ORDER BY block_timestamp_date (3)
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW (4)
    ) as ma_7d_transactions
from
  daily_transactions
order by
  block_timestamp_date
;

import plotly.graph_objects as go

fig = go.Figure()

fig.add_trace(go.Scatter(
  x=df_transactions_daily['block_timestamp_date'],
  y=df_transactions_daily['ct_transactions'],
  mode='lines',
  name='Daily Transactions Count')
  )
fig.add_trace(go.Scatter(
  x=df_transactions_daily['block_timestamp_date'],
  y=df_transactions_daily['ma_7d_transactions'],
  mode='lines',
  name='7-day Moving Average of Daily Transactions Count')
  )

fig.update_layout(
    title="Daily Transactions Count",
    xaxis_title="Date",
    yaxis_title="Transactions/day"
)

fig.show()

with daily_transactions as (
    select
      date(block_timestamp) as block_timestamp_date,
      count(*) as ct_transactions
    from `mltest-202903.bitcoin_playground.all_transactions_until_dec_04_2020`
    group by
      block_timestamp_date
),
moving_avg_d as (
    select
      block_timestamp_date,
      ct_transactions,
      AVG(ct_transactions)
        OVER (
          ORDER BY block_timestamp_date
          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as ma_7d_transactions
    from
      daily_transactions
    order by
      block_timestamp_date
),
daily_usd_per_btc as (
    select
      date(`date`) as date_val, (1)
      `open` as usd_open
    from `mltest-202903.bitcoin_playground.btc_to_usd`
)
select
  a.date_val,
  a.usd_open,
  b.ct_transactions,
  b.ma_7d_transactions
from daily_usd_per_btc a
join moving_avg_d b on (a.date_val = b.block_timestamp_date)
;

import plotly.graph_objects as go
from plotly.subplots import make_subplots

# Create figure with secondary y-axis
fig = make_subplots(specs=[[{"secondary_y": True}]])

fig.add_trace(go.Scatter(
  x=df_transactions_daily['date_val'],
  y=df_transactions_daily['ct_transactions'],
  mode='lines',
  name='Daily Transactions Count')
  )
fig.add_trace(go.Scatter(
  x=df_transactions_daily['date_val'],
  y=df_transactions_daily['ma_7d_transactions'],
  mode='lines',
  name='7-day Moving Average')
  )

fig.add_trace(go.Scatter(
  x=df_transactions_daily['date_val'],
  y=df_transactions_daily['usd_open'],
  mode='lines',
  name='USD Open Price of 1 BTC'
  ),
  secondary_y = True
  )


# Figure title
fig.update_layout(
    title_text="Daily Transactions and Open Price of 1 BTC in USD"
)

# Set x-axis title
fig.update_xaxes(title_text="Date")

# Set y-axes titles
fig.update_yaxes(title_text="Transactions/day", secondary_y=False)
fig.update_yaxes(title_text="Open Price in USD of 1 BTC", secondary_y=True)

# Horizontal legend layout and move below the plotting area
fig.update_layout(legend=dict(
    orientation="h",
    yanchor="top",
    y=-0.15,
    xanchor="left",
    x=0.3
))

fig.show()

with base_data as (  (1)
    select
        date(block_timestamp) as block_timestamp_date,
        input_data.addresses as input_data_addresses
    from
        `bigquery-public-data.crypto_bitcoin.transactions`
    CROSS JOIN UNNEST(inputs) as input_data
    where
        block_timestamp_month >= '2015-01-01'
),
individual_input_address as ( (2)
    select
        block_timestamp_date,
        input_address
    from base_data
    CROSS JOIN UNNEST(input_data_addresses) as input_address
),
dist_input_addresses_per_day as ( (3)
    select
        block_timestamp_date,
        count(distinct input_address) as distinct_input_addresses
    from individual_input_address
    group by
        block_timestamp_date
    order by
        block_timestamp_date
),
moving_avg_d as ( (4)
    select
        block_timestamp_date,
        distinct_input_addresses,
        AVG(distinct_input_addresses)
        OVER (
            ORDER BY block_timestamp_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as ma_7d_input_addresses,
        AVG(distinct_input_addresses)
        OVER (
            ORDER BY block_timestamp_date
            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
      ) as ma_30d_input_addresses
    from
        dist_input_addresses_per_day
),
daily_usd_per_btc as ( (5)
    select
        date(`date`) as date_val,
        `open` as usd_open
    from `mltest-202903.bitcoin_playground.btc_to_usd`
)
select
    a.block_timestamp_date,
    a.distinct_input_addresses,
    a.ma_7d_input_addresses,
    a.ma_30d_input_addresses,
    b.usd_open
from moving_avg_d a
join daily_usd_per_btc b on (a.block_timestamp_date = b.date_val)
order by
   block_timestamp_date
;

import plotly.graph_objects as go
from plotly.subplots import make_subplots

fig = make_subplots(specs=[[{"secondary_y": True}]])
fig.add_trace(go.Scatter( x=df['block_timestamp_date'], y=df['distinct_input_addresses'], mode='lines',name='Daily'))
fig.add_trace(go.Scatter( x=df['block_timestamp_date'], y=df['ma_7d_input_addresses'], mode='lines',name='7d MA'))
fig.add_trace(go.Scatter( x=df['block_timestamp_date'], y=df['ma_30d_input_addresses'], mode='lines',name='30d MA'))
fig.add_trace(go.Scatter( x=df['block_timestamp_date'], y=df['usd_open'], mode='lines',name='USD Open Price of 1 BTC'), secondary_y = True)

# Figure title
fig.update_layout(
    title_text="Daily Distinct Input Addresses and Open Price of 1 BTC in USD"
)

# Set x-axis title
fig.update_xaxes(title_text="Date")

# Set y-axes titles
fig.update_yaxes(title_text="Distinct Input Addresses/day", secondary_y=False)
fig.update_yaxes(title_text="Open Price in USD of 1 BTC", secondary_y=True)

# Horizontal legend layout and move below the plotting area
fig.update_layout(legend=dict(
    orientation="h",
    yanchor="top",
    y=-0.15,
    xanchor="left",
    x=0.3
))

fig.show()