Gist for http://www.andygranowitz.com/2018/04/12/empirical-explanation-craig-wright-selfish-mining-bitcoin-bet.html

block-times.r

library(tidyverse)
library(lubridate)
library(bigrquery)
library(tibble)

# use your project ID here
project <- "project-id" # put your project ID here

# query all Bitcoin data
sql <- "SELECT
  block_id,
  string(timestamp) as timestamp,
  previous_block,
  difficultyTarget
  FROM
  [bigquery-public-data:bitcoin_blockchain.blocks]"

# execute the query and store the result
all_block_times <- query_exec(sql, project = project, max_pages = Inf)

# save the data so you don't need to query BigQuery in future sessions 
# write.csv(all_block_times, "/path/to/bitcoindata.csv")
# all_block_times <- read.csv("/path/to/bitcoindata.csv")

all_block_times <- as_tibble(all_block_times) %>% 
  mutate(timestamp = as.numeric(timestamp))

all_block_times <- all_block_times %>% 
  mutate(timestamp = as_datetime(timestamp / 1000)) %>% 
  arrange(timestamp) # simplifying assumption! blocks can have timestamps before prior blocks found

# add time_since_last_block
block_times <- all_block_times %>% 
  transmute(block_number = row_number(), 
            timestamp,
            time_since_last_block = difftime(timestamp, lag(timestamp), units = "mins"),
            difficultyTarget,
            block_id
            ) %>% 
  filter(!is.na(time_since_last_block)) # removes the Genesis block

block_times <- block_times %>% filter(year(timestamp) == 2016) 

# plot all block times for 2016
par(oma=c(1,1,1,1))
hist(as.numeric(block_times$time_since_last_block), main = 'Histogram of 2016 Bitcoin block times', xlab = "minutes", ylab = "# of blocks")
abline(v = mean(block_times$time_since_last_block), col = "red")

# if time since last block is > 10, subtract 10, find average
offset <- 10
x <- block_times %>% 
  filter(time_since_last_block > offset) %>% 
  .$time_since_last_block

mean(x)
par(oma=c(1,1,1,1))
hist(as.numeric(x), main = 'Hist of time to next block, for blocks not found in first 10 min', xlab = "minutes", ylab = "# of blocks", xlim=c(0,110))
abline(v = mean(x), col = "red")

# random sample
st <- as.POSIXct(as.Date("2016/01/01"))
et <- as.POSIXct(as.Date("2016/12/31"))
dt <- as.numeric(difftime(et,st,unit="sec"))
ev <- sort(runif(10000, 0, dt))
rt <- st + ev

time_until_next_block <- function(time, timestamps) {
  difftime(timestamps[min(which(timestamps > time))], time, units = "mins")
}

ts <- block_times$timestamp
tunb <- map_dbl(rt, time_until_next_block, timestamps = ts)
mean(tunb)
par(oma=c(1,1,1,1))
hist(tunb, main = 'Hist of time to next block from 10k random times', xlab = "minutes", ylab = "# of blocks")
abline(v = mean(tunb), col = "red")