Skip to content

Instantly share code, notes, and snippets.

View axie_query
CREATE TEMP FUNCTION hex_to_binary(x STRING) AS
(
REPLACE(REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(x, '0','0000'), '1','0001'),'2','0010'),'3','0011'),'4','0100'),'5','0101'),'6','0110'),'7','0111'),'8','1000'),'9','1001'),'A','1010'),'B','1011'),'C','1100'),'D','1101'),'E','1110'),'F','1111')
);
WITH axie AS (
SELECT block_timestamp, input, to_address, hex_to_binary(UPPER(SUBSTR(output,3,63))) AS genome
FROM `bigquery-public-data.crypto_ethereum.traces`
WHERE TRUE
AND input LIKE '0xa6472906%' --getAxie()
@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 (
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;
@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 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 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 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,
View cannabis-3k_similarity-plot.R
# dependencies
library("moduleColor")
library("dynamicTreeCut")
library("RColorBrewer")
# import data
canna.z = read.csv("~/Downloads/canna-z.csv",header=T,row.names=1)
#https://github.com/mattsarrasin/canna-gen/blob/master/sample_description_list-2.txt
id2name = read.csv('~/Downloads/results-20200327-155748.csv')
@allenday
allenday / convert-to-geography
Last active Feb 4, 2020
Load GFF v3 data to BigQuery
View convert-to-geography
SELECT
seq_id,source,type,seq_start,seq_end,
ST_MAKELINE(ST_GEOGPOINT(LOG(seq_start)/LOG(10),-0.01),ST_GEOGPOINT(LOG(seq_end)/LOG(10),0.01)) AS range_log10,
score,strand,phase,attributes
FROM `project.dataset.tablename`
LIMIT 1000
You can’t perform that action at this time.