to_staking_contract_tx_cte as (

select

distinct

tx_hash

from ethereum.core.ez_token_transfers

where 1=1

and CONTRACT_ADDRESS = '0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0' -- MATIC

and TO_ADDRESS = '0x5e3ef299fddf15eaa0432e6e66473ace8c13d908' -- PoS Staking Contract

)

, from_null_adderss_cte as ( -- mint staking share

select

distinct

tx_hash

from ethereum.core.ez_token_transfers

where 1=1

and tx_hash in (select * from to_staking_contract_tx_cte)

and FROM_ADDRESS = '0x0000000000000000000000000000000000000000'

)

select

BLOCK_TIMESTAMP::date date

, 'Stake' grp

, count(distinct ORIGIN_FROM_ADDRESS) user

, sum(AMOUNT) AMOUNT

, sum(AMOUNT_USD) AMOUNT_USD

, count(distinct tx_hash) tx_count

from ethereum.core.ez_token_transfers

where 1=1

and CONTRACT_ADDRESS = '0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0' -- MATIC

and TO_ADDRESS = '0x5e3ef299fddf15eaa0432e6e66473ace8c13d908' -- PoS Staking Contract

and tx_hash in (select * from from_null_adderss_cte)

and date >= '2022-07-01'

group by 1,2

union