Allen Day allenday

Notes

	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 (

	#!/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;


	%.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 :

	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

	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

	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,

	# 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')

	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