allenday

SELECT number, gas_used AS gas FROM `bigquery-public-data.crypto_ethereum.blocks` WHERE number = 7000000;

allenday

CREATE TEMP FUNCTION
  hex64ToInt64(hex STRING)
  RETURNS INT64 AS (
    IF(hex < "8000000000000000", 
       cast(concat("0x", hex) AS INT64), 
       (SELECT (((ms32 & 0x7fffffff) << 32) | ls32) - 0x7fffffffffffffff - 1
        FROM (SELECT cast(concat("0x", substr(hex, 1, 8)) AS INT64) AS ms32,
                     cast(concat("0x", substr(hex, 9, 8)) AS INT64) AS ls32))));
CREATE TEMP FUNCTION
  KECCAK(data STRING)

allenday

CREATE TEMP FUNCTION
  KECCAK256(data STRING)
  RETURNS STRING
  LANGUAGE js AS """
    var utf8 = unescape(encodeURIComponent(data));
    var arr = [];
    for (var i = 0; i < utf8.length; i++) {
      arr.push(utf8.charCodeAt(i));
    }
    return ethers.utils.keccak256(arr)

allenday

with 
double_entry_book as (
    -- debits
    select to_address as address, value as value, block_timestamp
    from `bigquery-public-data.crypto_ethereum.traces`
    where to_address is not null
    and status = 1
    and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)
    union all
    -- credits

allenday

WITH 
output_ages AS (
  SELECT
    ARRAY_TO_STRING(outputs.addresses,',') AS output_ages_address,
    MIN(block_timestamp_month) AS output_month_min,
    MAX(block_timestamp_month) AS output_month_max
  FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(outputs) AS outputs
  GROUP BY output_ages_address
)
,input_ages AS (

allenday

WITH double_entry_book AS (
    -- debits
    SELECT
      ARRAY_TO_STRING(inputs.addresses, ",") AS address
    , inputs.type
    , -inputs.value AS value
    FROM `bigquery-public-data.crypto_bitcoin_cash.transactions` JOIN UNNEST(inputs) AS inputs
    WHERE block_timestamp_month = '2019-01-01'

    UNION ALL

allenday

Retrieve and visualize Bitcoin network transactions associated with the Pizza transaction sending address.

Data are from Google BigQuery public dataset (free).

Produces an image as shown below (scroll down).

R code:

library(plyr)

allenday

sudo apt-get -y update
sudo apt-get -y install build-essential ca-certificates curl ffmpeg git wget unzip python3-dev python3-pip software-properties-common python3-numpy youtube-dl

#install for GPU
sudo apt-get install mesa-common-dev libegl1-mesa-dev libgles2-mesa-dev
sudo apt purge nvidia-*
sudo add-apt-repository ppa:graphics-drivers/ppa
sudo apt update
sudo apt install nvidia-384

allenday

WITH wanted AS (
SELECT 
  session_id, 
  ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY block_timestamp) AS pos, 
  CASE WHEN tx.to_address = "0x698ff47b84837d3971118a369c570172ee7e54c2" THEN TRUE ELSE NULL END AS ok,
  tx.to_address
FROM `crypto-public-data.aux.materialized_sessions` AS sessions JOIN UNNEST(transactions) AS tx
WHERE TRUE
),
raw_referrers AS (

allenday

WITH all_edits AS (
SELECT 
CAST(osm_timestamp AS DATE) AS d, 0 AS code, COUNT(*) AS count
FROM `gcp-pdp-osm-dev.osm_to_bq_history.history_layers`
GROUP BY d
),

-- HEALTH
health_creates AS (
SELECT