Query editor
WITH addresses AS ( SELECT CONCAT('\xde30da39c46104798bb5aa3fe8b9e0e1f348163f')::bytea AS address), -- 0xDe30da39c46104798bB5aA3fe8B9e0e1F348163F Gitcoin GTC contractthresh AS ( SELECT {{GTC Balance}}::numeric as threshhold ),decimals AS ( SELECT 18::numeric as decimals ),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, addressesWHERE contract_address = addressUNION ALLSELECT 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, addressesWHERE contract_address = address) as tGROUP BY 1, 2, 3),balances_with_gap_days AS ( SELECT t.day,address,SUM(amount) OVER (PARTITION BY address ORDER BY t.day asc) AS balance, -- balance per day with a transferlead(DAY, 1, now() ) OVER (PARTITION BY address ORDER BY t.day asc) AS next_day -- the day after a day with a transferFROM transfers t),days AS ( SELECT generate_series('2021-05-12'::TIMESTAMP,date_trunc('day', NOW()), '1 day') AS DAY -- Smart contract creation May 12, 2021 // Tx Hash: 0x7857273a16c1dbefe40842f6fb98824fb09ad3c5e8e001880c5fe656784570a4),balance_all_days AS ( SELECT d.day,address,SUM(balance/(10^decimals)) AS balanceFROM balances_with_gap_days bINNER JOIN days d ON b.day <= d.day AND d.day < b.next_day -- Observation for every day after the first transfer until the next day with transferLEFT JOIN thresh on 1=1LEFT JOIN decimals on 1=1WHERE balance/(10^decimals) >= threshholdGROUP BY 1, 2ORDER BY 1, 2),holders AS (SELECTaddress AS token_holders,balanceFROM balance_all_days bWHERE b.day = date_trunc('day', NOW())GROUP BY 1, 2),votes_changes AS (SELECTdelegate,ROUND("newBalance" / 1e18) as balance,ROUND("previousBalance" / 1e18) as prev_balance,date_trunc('hour', evt_block_time) as day_evt_time,evt_tx_hash,evt_index,evt_block_numberFROM