Query editor
with price as (select date_trunc('day',minute) as day, symbol, avg(price) as pricefrom prices.usdwhere(contract_address is null or contract_address = 0x7083609fce4d1d8dc0c979aab8c869ea2c873402)and symbol in (select replace(symbol,'21','') as symbol from query_2986715)and minute>=date('2023-08-28')group by 1,2),holders as (select day, symbol, address, sum(amount) as amountfrom (selectdate_trunc('day', evt_block_time) as day,symbol,"from" as address,sum(value/power(10,decimals)*-1) as amountfrom erc20_ethereum.evt_Transfer aleft join query_2986715 bon a.contract_address=b.contract_addresswhere evt_block_time>=date('2023-08-28')and a.contract_address in (select contract_address from query_2986715)group by 1,2,3union allselectdate_trunc('day', evt_block_time) as day,symbol,"to" as address,sum(value/power(10,decimals)) as amountfrom erc20_ethereum.evt_Transfer aleft join query_2986715 bon a.contract_address=b.contract_addresswhere evt_block_time>=date('2023-08-28')and a.contract_address in (select contract_address from query_2986715)group by 1,2,3)agroup by 1,2,3),timeseries as (select day, symbol, addressfrom query_3225625cross join (select distinct symbol, address from holders)where day>=date('2023-08-28')and day<=date(now())),final as (select a.day, a.symbol, address, holdings * price as usd_holdingsfrom (select a.day, a.symbol, a.address, sum(amount) over (partition by a.symbol, a.address order by a.day) as holdingsfrom timeseries aleft join holders bon a.day=b.dayand a.symbol=b.symboland a.address=b.address)aleft join price bon a.day=b.dayand replace(a.symbol,'21','') = b.symbol)