Query editor
WITH addresses AS( SELECT "to" AS adrFROM erc20."ERC20_evt_Transfer" trWHERE contract_address = '/x0000000000' ) ,transfers AS( SELECT DAY,address,token_address,sum(amount) AS amount -- Net inflow or outflow per dayFROM( SELECT date_trunc('day', evt_block_time) AS DAY,"to" AS address,tr.contract_address AS token_address,value AS amountFROM erc20."ERC20_evt_Transfer" tr --INNER JOIN addresses ad ON tr."to" = ad.adrWHERE contract_address = '\x514910771AF9Ca656af840dff83E8264EcF986CA'UNION ALL SELECT date_trunc('day', evt_block_time) AS DAY,"from" AS address,tr.contract_address AS token_address, -value AS amountFROM erc20."ERC20_evt_Transfer" tr --INNER JOIN addresses ad ON tr."from" = ad.adrWHERE contract_address = '\x514910771AF9Ca656af840dff83E8264EcF986CA') tGROUP BY 1,2,3 ) ,balances_with_gap_days AS( SELECT t.day,address,SUM(amount) OVER (PARTITION BY addressORDER BY t.day) AS balance, -- balance per day with a transferlead(DAY, 1, now()) OVER (PARTITION BY addressORDER BY t.day) AS next_day -- the day after a day with a transferFROM transfers t ) ,days AS( SELECT generate_series('2021-01-01', date_trunc('day', NOW()), '1 day') AS DAY -- Generate all days since the first contract) ,balance_all_days AS( SELECT d.day,address,SUM(balance) AS balanceFROM balances_with_gap_days bINNER JOIN days d ON b.day <= d.dayAND d.day < b.next_day -- Yields an observation for every day after the first transfer until the next day with transfer--INNER JOIN erc20.tokens erc ON b.token_address = erc.contract_addressGROUP BY 1,2ORDER BY 1,2 )SELECT b.day AS "Date",SUM(CASE WHEN address='\xa3A7B6F88361F48403514059F1F16C8E78d60EeC' then balance/1e18 END) as "Arbitrum: L1 ERC20 Gateway",SUM(CASE WHEN address='\xe78388b4ce79068e89bf8aa7f218ef6b9ab0e9d0' then balance/1e18 END) as "Avalanche: Bridge",SUM(CASE WHEN address='\xe010fcda8894c16a8acfef7b37741a760faeddc4' then balance/1e18 END) as "Balancer: ETH/LINK 50/50 #3",