PP as(

select date(hour) as date, token_address, avg(price) as price

from ethereum.price.ez_prices_hourly

where token_address = '0xc71b5f631354be6853efe9c3ab6b9590f8302e81'

group by 1,2 ),

AA as(

select 'ethereum' as chain, origin_from_address as user, from_address as contract, to_address as pool, amount as amount, contract_address, block_timestamp from ethereum.core.ez_token_transfers union all

select 'ethereum' as chain, origin_to_address as user, to_address as contract, from_address as pool, 0 - amount as amount, contract_address, block_timestamp from ethereum.core.ez_token_transfers ),

BB as(

select date_trunc('day',block_timestamp) as time, sum(amount) as stake

from AA

where contract_address = '0xc71b5f631354be6853efe9c3ab6b9590f8302e81'

and pool in ('0x76538f77ce2cc5e2408392e0c20d6ee991c8fa60')

group by 1 ),

CC as(

select a.time, case when stake is null then 0 else stake end as stake

from (select distinct time from BB) as a

left join BB as b on a.time = b.time ),

DD as(

select time, stake as stake1, sum(stake) over(order by time) as stake2

from CC )

select

time, 'Staking TVL' as pool, 'ZKJ' as token, price,

stake2 as staked_native, stake2*price as staked_usd

from DD as a

left join PP as b on date(a.time) = b.date