select

date(hour) as adates,

avg(price) as matic_price

from ethereum.core.fact_hourly_token_prices

where symbol = 'MATIC'

group by 1

),

stk_mat as (

select

date(block_timestamp) as dates,

sum(event_inputs:value/1e18) as Matic_staked,

sum (Matic_staked) over (order by dates) as cum_Matic_staked,

count(distinct(origin_from_address)) as n_stakers,

sum (n_stakers) over (order by dates) as cum_n_stakers,

avg(event_inputs:value/1e18) as avg_stake_size,

count(distinct(tx_hash)) as n_deposits,

sum(n_deposits) over ( order by dates) as cum_n_deposits,

max(event_inputs:value/1e18) as max_matic_deposit,

min(event_inputs:value/1e18) as min_matic_deposit,

median(event_inputs:value/1e18) as median_matic_deposit,

case when lower(origin_to_address) = lower('0x3A58a54C066FdC0f2D55FC9C89F0415C92eBf3C4') then 'Lido'

when lower(origin_to_address) = lower('0x7ed6390f38d554B8518eF30B925b46972E768AF8') then 'Claystack'

when lower(origin_to_address) = lower('0x3aD736904E9e65189c3000c7DD2c8AC8bB7cD4e3') then 'Stader'

end as platform

from polygon.core.fact_event_logs

where lower(origin_to_address) in (lower('0x3A58a54C066FdC0f2D55FC9C89F0415C92eBf3C4'), lower('0x7ed6390f38d554B8518eF30B925b46972E768AF8'),

lower('0x3aD736904E9e65189c3000c7DD2c8AC8bB7cD4e3'))

and dates >= '2022-01-01'

and event_name = 'Transfer'