Skip to content

Instantly share code, notes, and snippets.

Allen Day allenday

Block or report user

Report or block allenday

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@allenday
allenday / tx.sql
Last active Jun 7, 2019
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 / gas-price-by-date.sql
Created May 26, 2019
Ethereum gas price by date from BigQuery
View gas-price-by-date.sql
SELECT CAST('2019-04-01' AS DATE) AS d, AVG(gas_used) AS gas
FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE TRUE
AND timestamp >= TIMESTAMP('2019-04-01')
AND timestamp <= TIMESTAMP(DATE_ADD(CAST('2019-04-01' AS DATE), INTERVAL 1 DAY))
GROUP BY d
@allenday
allenday / gas-price-by-block.sql
Created May 26, 2019
Ethereum gas price by block number from BigQuery
View gas-price-by-block.sql
SELECT number, gas_used AS gas FROM `bigquery-public-data.crypto_ethereum.blocks` WHERE number = 7000000;
@allenday
allenday / rice3k-hw-equlibrium.sql
Created Mar 6, 2019
Rice3K analysis 2: some specific regions are under selective pressure
View rice3k-hw-equlibrium.sql
#standardSQL
--
-- The following query computes the Hardy-Weinberg equilibrium for variants.
--
WITH variants AS (
SELECT reference_name, start_position, end_position, reference_bases, alt,
SUM(HOM_REF) AS HOM_REF,
SUM(HOM_ALT) AS HOM_ALT,
SUM(HET) AS HET
FROM (
@allenday
allenday / rice3k-hw-equilibrium-qplot.R
Created Mar 6, 2019
Rice3K analysis 2: dataviz some specific regions are under selective pressure
View rice3k-hw-equilibrium-qplot.R
data = read.csv("~/data.csv")
colnames(data) <- c("contig", "pos", "t", "f", "value", "value2")
qplot(data=data, x=pos,y=1,color=value, alpha=I(1),geom="jitter",facets=contig ~ ., main="Position vs. % of Alleles out of Hardy-Weinberg Equilibrium (p<=0.05)", xlab="Chromosome Position (Bin Size=10Kb)",ylab="") + theme_bw() + theme(axis.title = element_text(size = 15), axis.ticks.y=element_blank(),axis.text.y=element_blank()) + scale_x_continuous() + guides(colour = guide_legend(override.aes = list(alpha = 1))) + scale_colour_gradient(low="white",high="red")
@allenday
allenday / rice3k-selective-pressure-heatmap.R
Created Mar 6, 2019
Rice 3K analysis 1: dataviz of genetic variant distribution
View rice3k-selective-pressure-heatmap.R
rice3k = read.csv("~/Downloads/rice3k_z.csv",header=F)
#chromosomes 1-12, excludes Sy and Un
rice3k.z = as.matrix(rice3k[,c(2:45,129:165,166:202,203:239,240:270,271:302,303:333,334:362,363:386,46:69,70:99,100:128)])
rownames(rice3k.z) = rice3k[,1]
colnames(rice3k.z) = c(rep(1,44),rep(2,37),rep(3,37),rep(4,37),rep(5,31),rep(6,32),rep(7,31),rep(8,29),rep(9,24),rep(10,24),rep(11,30),rep(12,29))
heatmap(rice3k.z,Colv=NA,ColSideColors=colnames(rice3k.z),labCol=F,scale="none",cexRow=0.1)
#2:45 #1
#46:69 #10
#70:99 #11
#100:128 #12
@allenday
allenday / rice3k-selective-pressure-anomalies.sql
Created Mar 6, 2019
Rice 3K analysis 1: genetic variants are not uniformly distributed
View rice3k-selective-pressure-anomalies.sql
WITH
ind AS (
-- count variants for each sample/ref/bin
SELECT
call.name AS sample, reference_name AS ref, CAST(start_position/1000000 AS INT64) AS bin, COUNT(call.name) AS n
FROM `bigquery-public-data.genomics_rice.Rice3K_DeepVariant_Os_Nipponbare_Reference_IRGSP_1_0`
JOIN UNNEST(call) AS call
JOIN UNNEST(alternate_bases) AS alt
WHERE alt.alt != '<*>'
GROUP BY sample, ref, bin
@allenday
allenday / example-rice-fastq.fq
Created Feb 13, 2019
small snippet of rice chr1
View example-rice-fastq.fq
@example-rice-fastq
CTAAACCCTAAACCCTAAACCCTAAACCCTAAACCCTAAACCCTAAACCCTAAACCCTAACCCTAAACCCTAACCCTAAACCCTAAACCCTAAACCCTAAACCCTAAACCCTAAACAGCTGACAGTACGATAGATCCACGCGAGAGGAAC
+
"##"""""#1'##.&&'#&(&'($&'+)""#"##"""$#&*&+),&$##$*-(,-1850*(&###&(*'&&),//($'*(%'#%##(('"$"&#"$')&$"$&+($#"$(%%"""""#$$"%##"%#%""'$#+*,$,*%%$%"&$++$"
@allenday
allenday / bitcoin-cash.sql
Last active Jun 13, 2019
What are the current balances of a random set of 1000 addresses on blockchain X?
View bitcoin-cash.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_cash.transactions` JOIN UNNEST(inputs) AS inputs
WHERE block_timestamp_month = '2019-01-01'
UNION ALL
@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
You can’t perform that action at this time.