Skip to content

Instantly share code, notes, and snippets.

@allenday
allenday / pizza-bigquery.md
Last active Sep 18, 2021
Retrieve and visualize Bitcoin network transactions associated with the Pizza transaction sending address.
View 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)
@allenday
allenday / gini-balance-bitcoin.sql
Created Jan 31, 2019
Calculate Gini coefficient for Bitcoin balances.
View gini-balance-bitcoin.sql
WITH double_entry_book AS (
-- debits
SELECT
array_to_string(inputs.addresses, ",") as address
, inputs.type
, -inputs.value as value
, block_timestamp
FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs
UNION ALL
@allenday
allenday / worldpop_density.sql
Created Sep 17, 2021
worldpop population density per country 2020
View worldpop_density.sql
SELECT
country_name,
COUNT(1) AS hectares,
SUM(population) AS total_pop,
SUM(population)/COUNT(1) AS pop_per_hectare
FROM
`bigquery-public-data.geo_worldpop.WorldPop_GP_100m` AS pop
,`bigquery-public-data.country_codes.country_codes` AS codes
WHERE TRUE
AND pop.country_code = codes.alpha_3_code
@allenday
allenday / mining-pool-address-features.sql
Last active Sep 13, 2021
Bitcoin mining pool address signatures and statistics of their behavior over time.
View mining-pool-address-features.sql
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
allenday / gini-balance-erc20.sql
Created Jan 31, 2019
Calculate Gini coefficient for ERC-20 balances.
View gini-balance-erc20.sql
with
double_entry_book as (
-- debits
select to_address as address, CAST(value AS NUMERIC) as value, block_timestamp
from `bigquery-public-data.crypto_ethereum.token_transfers`
where from_address is not null and to_address is not null
and token_address = LOWER('0x408e41876cccdc0f92210600ef50372656052a38') --OMG
union all
-- credits
select from_address as address, -CAST(value AS NUMERIC) as value, block_timestamp
@allenday
allenday / bigquery-bitcoin-balance.sql
Last active Aug 11, 2021
Query Bitcoin balance by timestamp in BigQuery
View bigquery-bitcoin-balance.sql
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.inputs` as inputs
UNION ALL
@allenday
allenday / gini-balance-ethereum.sql
Created Jan 31, 2019
Calculate Gini coefficient for Ethereum balances.
View gini-balance-ethereum.sql
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
allenday / profanity.sh
Created Jul 13, 2021
run profanity for x sec to generate a vanity key, then exit
View profanity.sh
( cmdpid=$BASHPID; (sleep 3600; kill $cmdpid) & exec ./profanity.x64 --matching 888888XXXXXXXXXXXXXXXXXXXXXXXXXXXX888888 | grep --line-buffered Score >> keys.txt )
@allenday
allenday / geohash-precipitation-noaa_gsod.sql
Created Jul 3, 2021
get total precipitation within a date range for a geohash polygon
View geohash-precipitation-noaa_gsod.sql
#standardSQL
CREATE TEMPORARY FUNCTION geohashDecode(geohash STRING)
RETURNS STRUCT<bbox ARRAY<FLOAT64>, lat FLOAT64, lng FLOAT64>
LANGUAGE js AS """
if (!geohash) return null;
var base32 = '0123456789bcdefghjkmnpqrstuvwxyz';
geohash = geohash.toLowerCase();
var evenBit = true;
var latMin = -90, latMax = 90;
var lonMin = -180, lonMax = 180;
@allenday
allenday / bq-geohash-udf.sql
Created Jul 2, 2021 — forked from vladaman/bq-geohash-udf.sql
Google BigQuery standardSQL UDF function to decode geohash string into bounding box and coordinates
View bq-geohash-udf.sql
#standardSQL
CREATE TEMPORARY FUNCTION geohashDecode(geohash STRING)
RETURNS STRUCT<bbox ARRAY<FLOAT64>, lat FLOAT64, lng FLOAT64>
LANGUAGE js AS """
if (!geohash) return null;
var base32 = '0123456789bcdefghjkmnpqrstuvwxyz';
geohash = geohash.toLowerCase();
var evenBit = true;
var latMin = -90, latMax = 90;