Skip to content

Instantly share code, notes, and snippets.

@allenday
allenday / contract referrers
Created Aug 12, 2020
which address did user interact with in session right before target address 0x698ff47b84837d3971118a369c570172ee7e54c2 (or null)?
View contract referrers
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
allenday / Makefile
Last active Aug 6, 2020
coingecko-etl
View Makefile
%.ohlc.csv :
perl ./coingecko-csv.pl ${@:.ohlc.csv=} > $@
%.meta.jsonl :
perl ./coingecko-metadata.pl ${@:.meta.jsonl=} > $@
all.meta.jsonl :
cat `ls *.meta.jsonl | grep -v $@` | perl -ne 'print lc' | sort > $@
all.ohlc.csv :
cat `ls *.ohlc.csv | grep -v $@` > $@
all :
View setup-mediapipe-yt8m-ubuntu-20.sh
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
View sessionize-ethereum.pl
#!/usr/bin/perl
=pod
--root level tx
SELECT from_address AS u
, ARRAY_AGG(DISTINCT(UNIX_SECONDS(block_timestamp))) AS ss_array
FROM `bigquery-public-data.crypto_ethereum.traces`
WHERE trace_address IS NULL
GROUP BY u
=cut
use strict;
View tokens.sql
SELECT transfers.block_timestamp,transfers.from_address, transfers.to_address, tokens.symbol, tokens.address, SAFE_CAST(transfers.value AS INT64) / POW(10,SAFE_CAST(tokens.decimals AS INT64)) AS token_value
FROM
`bigquery-public-data.crypto_ethereum.token_transfers` AS transfers,
`bigquery-public-data.crypto_ethereum.tokens` AS tokens
WHERE
transfers.token_address = tokens.address
AND tokens.address IN('0x514910771af9ca656af840dff83e8264ecf986ca','0x2260fac5e5542a773aa44fbcfedf7c193bc2c599')
ORDER BY transfers.block_timestamp DESC
LIMIT 1000
View ethereum-sessions.sql
WITH sessions AS (
SELECT
block_timestamp,
block_number,
transaction_index,
value,
from_address,
CASE WHEN trace_type IS NULL THEN 0 ELSE 1 END AS is_from_contract,
trace_type,
SUM(is_new_session) OVER (ORDER BY from_address, block_timestamp) AS global_session_id,
@allenday
allenday / transacting-partner-count.sql
Created Jan 25, 2019
Number of transacting partners per Bitcoin address
View transacting-partner-count.sql
SELECT
txn_count,
COUNT(txn_count) AS num_addresses
FROM
(
SELECT
ARRAY_TO_STRING(inputs.addresses, '') AS addresses,
COUNT(DISTINCT `hash`) AS txn_count
FROM `crypto-etl-ethereum-dev.bitcoin_blockchain.transactions` AS txns
CROSS JOIN UNNEST(txns.inputs) AS inputs
@allenday
allenday / tx.sql
Last active May 6, 2020
recent anomalous token transfers
View tx.sql
WITH
z AS -- TODO better to use percentiles
(
SELECT
tok.address,
COUNT(tok.address) AS n,
AVG(SAFE_CAST(tx.value AS NUMERIC)/POWER(10,CAST(tok.decimals AS NUMERIC))) AS mu,
STDDEV(SAFE_CAST(tx.value AS NUMERIC)/POWER(10,CAST(tok.decimals AS NUMERIC))) AS sigma
FROM `crypto-etl-ethereum-dev.crypto_ethereum.tokens` AS tok,
`crypto-etl-ethereum-dev.crypto_ethereum.token_transfers` AS tx
@allenday
allenday / bigquery-bitcoin-balance.sql
Last active Apr 26, 2020
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
You can’t perform that action at this time.