Query editor
with tokens(address,label,divisor) as (select address, label, 10^decimals as divisorfrom (select "contract_address" as address, symbol as label, CAST (decimals AS numeric) as decimalsfrom erc20."tokens") z),prices as (select date_trunc('day', minute) as day, avg(price) as price, symbol, contract_addressfrom prices."usd"where minute > '2021-01-01'group by 1, 3, 4),ava as (select *from (select date_trunc('day', a.timestamp) as day, 'Avalanche Bridge' as bridge, token_address, label, max(amount_raw) as token_amount, divisorfrom erc20."token_balances" aleft join tokens t on t.address = a.token_addresswhere wallet_address = '\xe78388b4ce79068e89bf8aa7f218ef6b9ab0e9d0' --Avalanchegroup by 1, 2, 3, 4, 6order by 1) z),weth_ava as (select day, bridge, token_address, label, avg(token_amount) as token_amount, divisorfrom (SELECT day, 'Avalanche Bridge' as bridge, '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'::bytea as token_address, 'WETH' as label, SUM(transfer) over (order by day) as token_amount, 1 as divisorFROM (SELECT date_trunc('day', evt_block_time) as day, sum(-value/1e18) as transferFROM erc20."ERC20_evt_Transfer"WHERE contract_address = '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' --WETHAND "from" = '\xe78388b4ce79068e89bf8aa7f218ef6b9ab0e9d0' --AvalancheGROUP BY 1UNION allSELECTdate_trunc('day', evt_block_time) as day, sum(value/1e18) as transferFROM erc20."ERC20_evt_Transfer"WHERE contract_address = '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' --WETHAND "to" = '\xe78388b4ce79068e89bf8aa7f218ef6b9ab0e9d0' --AvalancheGROUP BY 1) as net) wgroup by 1, 2 ,3, 4, 6),ava_full as(select * from ava),ava_tvl as (select n.day, bridge, token_address, label, token_amount*price/divisor as token_amount_usdfrom ava_full nleft join prices p on n.day = p.day and n.label = p.symbol),