Query editor
WITHvotes_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_numberFROMgitcoin."GTC_evt_DelegateVotesChanged"WHEREevt_block_time >= TO_DATE('2021-05-12', 'YYYY-MM-DD')),-- take the last balance at the end of the daytemp_last_balances_of_the_day AS (SELECTdistinct on(delegate, day_evt_time) delegate as delegate,day_evt_time,balanceFROMvotes_changesORDER BYdelegate,day_evt_time DESC,evt_block_number DESC,-- take last block of the dayevt_index DESC -- take last tx in the block),last_balances_of_the_day AS (SELECT*FROMtemp_last_balances_of_the_dayUNION-- add that all delegates ever existed have zero delegation-- balance at the start, so that generate_series populate zeros balance-- at the start, otherwise dune visualisation skips the datesSELECTdelegate,TO_DATE('2021-05-12', 'YYYY-MM-DD') as day_evt_time,0 as balanceFROMtemp_last_balances_of_the_dayGROUP BYdelegate),-- populate missing dates where balance exists, but hasn't been changeddelegate_balances_end_of_day AS (SELECTgenerate_series(coalesce(day_evt_time:: date,TO_DATE('2021-05-12 00:00', 'YYYY-MM-DD mm-ss')):: date,lead(coalesce(day_evt_time:: date,TO_DATE('2021-05-12 00:00', 'YYYY-MM-DD mm-ss')