Query editor
with cex as(select address from labels.all where blockchain = 'ethereum' and category = 'cex'),erc20 as (select date_trunc('minute', evt_block_time) as minute, 'ethereum' as blockchain,from,value , contract_addressfrom erc20_ethereum.evt_Transferwhere to = '0x4e5b2e1dc63f6b91cb6cd759936495434c7e972f'and evt_block_time >= now() - '6 month'::intervaland from not in (select address from cex)union allselect date_trunc('minute', evt_block_time) as minute, 'bnb' as blockchain,from,value , contract_addressfrom erc20_bnb.evt_Transferwhere to = '0x4727250679294802377dd6ca6541b8e459077c95'and evt_block_time >= now() - '6 month'::intervaland from not in (select address from cex)union allselect date_trunc('minute', evt_block_time) as minute, 'polygon' as blockchain,from,value , contract_addressfrom erc20_polygon.evt_Transferwhere to = lower('0x71d4249079684479F2651745fA2fcD79c9b45f53')and evt_block_time >= now() - '6 month'::intervaland from not in (select address from cex)), prices as (select *from prices.usdwhere blockchain in ('ethereum', 'bnb','polygon')), erc20_volume as (select date_trunc('day', e.minute) as dt, e.blockchain, p.symbol, sum(e.value / pow(10, p.decimals) ) as amount, round(sum( e.value / pow(10, p.decimals) * p.price )) as volume_in_usdfrom erc20 e left join prices p on e.blockchain = p.blockchain and e.contract_address = p.contract_address and e.minute = p.minutegroup by date_trunc('day', e.minute), e.blockchain, p.symbol), native_tokens as (select date_trunc('minute', block_time) as minute, 'ethereum' as blockchain,value,'0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' as contract_address -- use contract address of WETH to join with price tablefrom ethereum.transactionswhere to = '0x4e5b2e1dc63f6b91cb6cd759936495434c7e972f' and success = trueand block_time >= now() - '6 month'::intervaland value <> 0and from not in (select address from cex union all select from as address from erc20)union allselect date_trunc('minute', block_time) as minute, 'bnb' as blockchain,value,'0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c' as contract_address -- use contract address of WBNB to join with price tablefrom bnb.transactionswhere to = '0x4727250679294802377dd6ca6541b8e459077c95' and success = true -- hot wallet address of FixedFloatand block_time >= now() - '6 month'::intervaland value <> 0and from not in (select address from cex union all select from as address from erc20)union allselect date_trunc('minute', block_time) as minute, 'polygon' as blockchain,value,'0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270' as contract_address -- use contract address of WMATIC to join with price tablefrom polygon.transactions