WITH a1 AS ( SELECT DISTINCT tx_id FROM ethereum.events_emitted WHERE block_timestamp >= '2022-05-01' AND contract_address = LOWER('0xa5f2211B9b8170F694421f2046281775E8468044') and block_timestamp < CURRENT_DATE ), b1 AS ( SELECT DISTINCT e1.tx_id FROM ethereum.events_emitted e1 JOIN a1 ON a1.tx_id = e1.tx_id WHERE block_timestamp >= '2022-05-01' and block_timestamp < CURRENT_DATE AND tx_to_address = LOWER('0x815c23eca83261b6ec689b60cc4a58b54bc24d8d') AND event_name = 'Deposit' ), c1 AS ( SELECT DISTINCT e1.tx_id FROM ethereum.events_emitted e1 JOIN b1 ON b1.tx_id = e1.tx_id WHERE block_timestamp >= '2022-05-01' and block_timestamp < CURRENT_DATE AND tx_to_address = LOWER('0x815c23eca83261b6ec689b60cc4a58b54bc24d8d') AND event_name = 'DelegateVotesChanged' ), data1 as ( SELECT block_timestamp::date AS date , CASE WHEN e1.event_name = 'Transfer' AND contract_address = '0xa5f2211b9b8170f694421f2046281775e8468044' AND LENGTH(event_inputs:value::string) <= 25 THEN CAST(event_inputs:value::string AS INT) * POWER(10, -18) ELSE 0 END AS thor_amt , * FROM ETHEREUM.events_emitted e1 JOIN c1 ON c1.tx_id = e1.tx_id WHERE block_timestamp >= '2022-05-01' and block_timestamp < CURRENT_DATE ), thor_staked as ( Select date, sum(thor_amt) as thor_staked From data1 where TX_SUCCEEDED = 'TRUE' group by 1 order by 1 asc ), ----thor unstaked over time a2 AS ( SELECT DISTINCT tx_id FROM ethereum.events_emitted WHERE block_timestamp >= '2022-05-01' and block_timestamp < CURRENT_DATE AND contract_address = LOWER('0xa5f2211B9b8170F694421f2046281775E8468044') ), b2 AS ( SELECT DISTINCT e2.tx_id FROM ethereum.events_emitted e2 JOIN a2 ON a2.tx_id = e2.tx_id WHERE block_timestamp >= '2022-05-01' and block_timestamp < CURRENT_DATE AND tx_to_address = LOWER('0x815c23eca83261b6ec689b60cc4a58b54bc24d8d') AND event_name = 'Withdraw' ), c2 AS ( SELECT DISTINCT e2.tx_id FROM ethereum.events_emitted e2 JOIN b2 ON b2.tx_id = e2.tx_id WHERE block_timestamp >= '2022-05-01' and block_timestamp < CURRENT_DATE AND tx_to_address = LOWER('0x815c23eca83261b6ec689b60cc4a58b54bc24d8d') AND event_name = 'DelegateVotesChanged' ), data2 as ( SELECT block_timestamp::date AS date , CASE WHEN e2.event_name = 'Transfer' AND contract_address = '0xa5f2211b9b8170f694421f2046281775e8468044' AND LENGTH(event_inputs:value::string) <= 25 THEN CAST(event_inputs:value::string AS INT) * POWER(10, -18) ELSE 0 END AS thor_amt , * FROM ETHEREUM.events_emitted e2 JOIN c2 ON c2.tx_id = e2.tx_id WHERE block_timestamp >= '2022-05-01' and block_timestamp < CURRENT_DATE ), thor_unstaked as ( Select date, sum(thor_amt) as thor_unstaked From data2 where TX_SUCCEEDED = 'TRUE' group by 1 order by 1 asc ) SELECT thor_staked.date as date, thor_staked, -thor_unstaked, (thor_staked - thor_unstaked) as net_THOR_staked, SUM(net_THOR_staked) OVER(ORDER BY thor_staked.date ASC) AS cumul_THOR_staked from thor_staked left join thor_unstaked on thor_staked.date = thor_unstaked.date group by 1,2,3,4 order by 1

Net $THOR Staked