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()
  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