{"p":"mrc-721","op":"deploy","tick":"gene","max":"881","meta":{"name":"GENE","description":"The first NFT collection in META layer","traits":"rank"},"code":{"engine":"trino","version":"400","body":"with inputs as( select block_height, tx_id, array_join(witness_data,',') payload from bitcoin.inputs t where t.block_height>=793000 and t.block_height<793009 and t.index=0), txns as ( select t.id txid, t.index, fee, virtual_size from bitcoin.transactions t where t.block_height>=793000 and t.block_height<793009 ), output as ( select address as miner, tx_id as txid from bitcoin.outputs t where t.block_height>=793000 and t.block_height<793009 and index=0 ), rawdata as ( SELECT '{' || from_utf8( from_hex( substr( payload, position('2270223a226272632d3230' in payload), position('7d68' in payload) - position('2270223a226272632d3230' in payload) ) ) ) || '}' as body, block_height, t.tx_id as txid FROM inputs t WHERE 1=1 AND t.payload LIKE '%0063036f726401%' AND t.payload LIKE '%2270223a226272632d3230%' AND t.payload LIKE '%227469636b223a226f69637022%' ), formatted as ( select *, COALESCE(TRY(JSON_PARSE(body)), NULL) as data from rawdata ), parsed as ( SELECT t.txid, block_height, json_extract_scalar(t.data, '$.p') as p, json_extract_scalar(t.data, '$.tick') as tick, json_extract_scalar(t.data, '$.op') as op, COALESCE( TRY( CAST (json_extract_scalar(t.data, '$.amt') AS integer) ), 0 ) as amt FROM formatted t where t.data is not null ), filtered as ( select * from parsed t where (t.p='brc-20' or t.p='brc-20c') and t.tick='oicp' and t.op='mint' ), integrated as ( select t.*, f.index,f.fee,f.virtual_size,o.miner from filtered t join txns f on t.txid=f.txid join output o on t.txid=o.txid ), sequenced as ( select row_number() OVER (ORDER BY t.block_height ASC,t.index ASC) as seq, t.* from integrated t ), cal_fr as ( select FLOOR(fee / virtual_size * 100000000) as fr, SUBSTR(CAST(txid as varchar), 3) || 'i0' as id, * from sequenced ), cal_fd as ( select fr, count(1) as fd from cal_fr group by fr order by fd asc ), cal_point as ( select (1000-seq)+t.fr/f.fd as point, * from cal_fr t join cal_fd f on t.fr=f.fr ), cal_rank_seed as ( select row_number() OVER (ORDER BY point DESC) as rs, * from cal_point ), cal_rank as ( select *, CASE WHEN rs>0 and rs <= 6 then 'gold' WHEN rs>6 and rs <= 31 then 'orange' WHEN rs>31 and rs <= 106 then 'purple' WHEN rs>106 and rs <= 231 then 'blue' WHEN rs>231 and rs <= 481 then 'green' WHEN rs>481 then 'white' END as level from cal_rank_seed ), pre_final as ( select rs as id, id as bind, miner, json_object('rank':level) as traits, json_object('contentType':'image/jpeg', 'uri':'ipfs://QmWKtb65YtxHuiq6GR9C5cd3TquKnjQrWDWotRitNnfZMo/' || level || '.jpg' ) as payload from cal_rank ), actived as ( select DISTINCT address from bitcoin.outputs t where t.block_height>=794500 and t.block_height<795000 and t.value*10000*10000>=2100 and address in (select miner from pre_final) ), gene as ( select t.* from pre_final t left join actived f on t.miner=f.address where f.address is not null order by id asc )"},"oops":{"bind":"select * from gene"}}