P as(

select hour, price

from ethereum.price.ez_prices_hourly

where token_address = '0x40d16fc0246ad3160ccc09b8d0d3a2cd28ae6c2f'

order by 1 desc limit 1 ),

X as(

select block_timestamp, 0+amount as balance, origin_from_address as user, from_address as contract, contract_address from ethereum.core.ez_token_transfers union all

select block_timestamp, 0-amount as balance, origin_from_address as user, to_address as contract, contract_address from ethereum.core.ez_token_transfers ),

S as(

select sum(balance) as supply

from X

where lower(contract) = '0x0000000000000000000000000000000000000000'

and contract_address = lower('0x40D16FC0246aD3160Ccc09B8D0D3A2cD28aE6C2f') ),

L as(

select sum(0-balance) as stake, sum(0-balance*price) as value

from X,P,S

where lower(contract) = '0x1a88df1cfe15af22b3c4c783d4e6f7f9e0c1885d'

and contract_address = lower('0x40D16FC0246aD3160Ccc09B8D0D3A2cD28aE6C2f') ),

H as(

select user

from X

where lower(contract) = '0x0000000000000000000000000000000000000000'

and contract_address = lower('0x40D16FC0246aD3160Ccc09B8D0D3A2cD28aE6C2f')

group by 1

having sum(balance) > 0 )

select *, stake/supply*100 as stake_ratio, (select count(user) from H) as users

from L,S