Google has imported the Bitcoin blockchain into its BigQuery dataset service, so it is now possible to write SQL queries on every Bitcoin transaction.

My free trial of Google Cloud Platform has 82 days and $218.38 left, so I am looking for something to do with it. I put together a query to match transactions going to the Hubski donation address.

  #standardSQL
  SELECT 
    timestamp, 
    transaction_id, 
    i.input_pubkey_base58, 
    o.output_script_bytes, 
    o.output_pubkey_base58
  FROM
    `bigquery-public-data.bitcoin_blockchain.transactions`,
    UNNEST(outputs) as o,
    UNNEST(inputs) as i
  WHERE o.output_pubkey_base58 = '19vVrqzbbLFKJL7ta9dvR7zX7uk6hdTWwU'
  ORDER BY timestamp DESC
  LIMIT 100

It returned 70 rows, the most recent having a timestamp of 1495074685000, corresponding to May 2017. This matched the most recent incoming transaction listed on a blockchain explorer.

BigQuery provided a colorful query profile.

Many rows had the same timestamp, so another query returned 12 distinct timestamps for incoming transactions, starting on Halloween 2015, with six more in 2016 and one in 2017.

I did a few more queries to match outgoing funds, but it was a lot easier (and about as informative) to just click around in the blockchain explorer.

BigQuery does not include price data, which makes things more interesting. I downloaded a Cryptocurrency Market Data dataset and noodled around in Excel. I was curious to try and visualize how reasonable it is for a speculator to panic on the roller coaster.

A spreadsheet calculated "Pollyanna patience days" for each date: the number of days since the most recent time a buy would now be profitable.

It also calculated "No-Loss patience days," a pessimistic measure showing how many days earlier you must have bought in order to avoid ever being down meanwhile.

These data are not very useful but they are fun to chart (log scale).

Between April 6, 2017 and March 7, 2018, it was impossible to lose value as long as you held at least 100 days.

Google debited my account $0.15 for BigQuery usage of 1,053.79 GB.