allenday/pizza-bigquery.md

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)
library(igraph)
library(bigrquery)
library(AggregateR)
project = 'bitcoin-bigquery'

laszlo = '1XPTgDRhN8RFnzniWCddobD9iKZatrvH4'

base_query_up = "SELECT
timestamp/1000 AS tt,
inputs.input_pubkey_base58 AS input_key,
outputs.output_pubkey_base58 AS output_key,
outputs.output_satoshis * 0.00000001 AS btc
FROM
`bigquery-public-data.bitcoin_blockchain.transactions`
JOIN UNNEST (inputs) AS inputs
JOIN UNNEST (outputs) AS outputs
WHERE
outputs.output_pubkey_base58 = 'ADDRESS'
AND
output_satoshis  >= 10000000
AND
timestamp < TIMESTAMP * 1000
AND
inputs.input_pubkey_base58 IS NOT NULL
GROUP BY tt, input_key, output_key, btc"

frontier = data.frame()
dig = function(k,depth,tt,direction) {
  base_query = base_query_up
  query <- sub('ADDRESS',k,base_query)
  query <- sub('TIMESTAMP',tt,query)
  
  if (depth <= max_depth) {
    cat(depth,k,"\n")
    if (query_exec(
      paste("SELECT COUNT(*) FROM (",query,") AS x"), 
      project = project, 
      use_legacy_sql=F)$f0_ > 0
    ) {
      frontier <- query_exec(query, project = project, use_legacy_sql=F)
      tx0 <<- rbind(tx0, frontier)

      for (ikey in unique(frontier$input_key)) {
        dig(ikey, depth+1, max(frontier$tt[frontier$input_key==ikey]),direction)
      }
    }
  }
}
max_depth = 2
tx0 = data.frame()

dig(laszlo,0,999999999999999,-1)

btc = ddply(tx0, c("input_key","output_key"), summarize, value=btc)
btc.net <- graph.data.frame(btc, directed=T)

V(btc.net)$color <- "blue"
V(btc.net)$color[unlist(V(btc.net)$name) == laszlo] <- "red"

mytitle = paste("BTC inputs upstream of pizza purchase, depth=",(max_depth+2),sep="")
nodes <- unlist(V(btc.net)$name)
E(btc.net)$width <- ceiling(E(btc.net)$value/800)+.1
plot.igraph(btc.net, 
            main=mytitle,
            vertex.size=5, 
            edge.arrow.size=0.5, 
            vertex.label=NA,
            layout=layout_nicely,
            rescale=T
            )