Query editor
-------------------------DIMS---------------------------------with token_definitions as (select symbol, cast(contract_address as varbinary) as contract_address, cast(decimals as uint256) as decimals from (VALUES('imBTC', 0x3212b29E33587A00FB1C83346f5dBFA69A458923, 8) -- tracking to/from null not sufficient enough. total supply does not add up, ('wBTC', 0x2260fac5e5542a773aa44fbcfedf7c193bc2c599, 8), ('tBTC', 0x8daebade922df735c38c80c7ebd708af50815faa, 18), ('renBTC', 0xeb4c2781e4eba804ce9a9803c67d0893436bb27d, 8), ('pBTC', 0x5228a22e72ccC52d415EcFd199F99D0665E7733b, 18) --handeled by mint/burn evts in original query, ('sBTC', 0xfe18be6b3bd88a2d2a7f928d00292e7a9963cfc6, 18) -- synthetix btc is is oracle based. not backed by btc. to/from null not sufficient. Likely synth bridge involved, ('hBTC', 0x0316EB71485b0Ab14103307bf65a021042c6d380, 18)) as t(symbol, contract_address, decimals)), time_table as (-- generate cte with day, token symbol defined in `token_definitions` tableSELECT date_add('day', step, day) AS day, symbolFROM unnest(SEQUENCE(CAST('2019-01-01' AS TIMESTAMP), current_date, INTERVAL '1' day)) AS t(day)----- generate a sequence (this will create the number of rows necessary for each day)CROSS JOIN (SELECT * FROM unnest(SEQUENCE(1, 1, 1)) AS t(step))cross join (select distinct symbol from token_definitions)where day < date_trunc('day', now()))-------------------------------------------------------------------------------------------------FACTS--------------------------------, total_supply as (selectblock_date as day, td.symbol, td.contract_address, sum(sum(if("from" = 0x0000000000000000000000000000000000000000, amount_raw / pow(10,td.decimals), amount_raw / pow(10,td.decimals) * -1.0))) over (partition by td.symbol order by block_date) as amount, sum(if("from" = 0x0000000000000000000000000000000000000000, amount_raw / pow(10,td.decimals), amount_raw / pow(10,td.decimals) * -1.0)) as changefrom tokens_ethereum.transfers tfinner join token_definitions td on td.contract_address = tf.contract_addresswhere "from" = 0x0000000000000000000000000000000000000000 or to = 0x0000000000000000000000000000000000000000group by 1,2,3), token_prices as (select day, contract_address, price from (selectrow_number() over (partition by contract_address, date_trunc('day', minute) order by minute desc) as index, date_trunc('day', minute) as day, symbol, contract_address, pricefrom prices.usdwhere blockchain = 'ethereum' and contract_address in (select contract_address from token_definitions))where index = 1 -- end of day/most current price)----------------ABSTRACTION----------------, final_balances as (select x.day, x.symbol, x.amount, x.change, x.contract_address, price, price * x.amount as amount_usd from (selecttt.day, tt.symbol