Skip to content

Instantly share code, notes, and snippets.

@allenday

allenday/axie_query

Last active Nov 18, 2020
Embed
What would you like to do?
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()
AND block_timestamp > '2020-11-07 02:19:38 UTC'
)
,genomes AS (
SELECT MIN(block_timestamp) AS spawn_time, STRUCT<variant ARRAY<STRING>,locus ARRAY<INT64>>(SPLIT(genome, ""), GENERATE_ARRAY(1,254)) AS genes, genome
FROM axie
GROUP BY genome
)
SELECT locus, CAST(variant AS INT64) AS present
FROM genomes
JOIN UNNEST(genes.variant) AS variant
JOIN UNNEST(genes.locus) AS locus
--GROUP BY locus
LIMIT 1000
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment