prices as (

select date, price

from

(select date_trunc(month,HOUR) as date, BLOCKCHAIN, price, row_number() over (order by hour desc) as rank

from near.price.ez_prices_hourly

where TOKEN_ADDRESS='wrap.near')

where rank=1),

linear_staking as (

select BLOCK_TIMESTAMP, try_parse_json(CLEAN_LOG) as log,

log:data[0]:account_id::string as account,

case when try_parse_json(CLEAN_LOG):event='stake' then 'stake' else 'unstake' end as event,

coalesce(log:data[0]:staked_amount::double/1e24, log:data[0]:unstaked_amount::double/1e24) as amount,

log:data[0]:new_stake_shares::double/1e24 as new_stake_shares

from near.core.fact_logs

where RECEIVER_ID='linear-protocol.near'

and try_parse_json(CLEAN_LOG):standard='linear'

and try_parse_json(CLEAN_LOG):event in ('instant_unstake','stake','unstake')),

linear_staking_rewards as (

select BLOCK_TIMESTAMP, try_parse_json(CLEAN_LOG) as log,

log:data[0]:validator_id as validator_id,

'reward' as event,

log:data[0]:rewards::double/1e24 as amount,

log:data[0]:new_stake_shares::double/1e24 as new_stake_shares

from near.core.fact_logs

where RECEIVER_ID='linear-protocol.near'

and try_parse_json(CLEAN_LOG):standard='linear'

and try_parse_json(CLEAN_LOG):event in ('epoch_update_rewards')),

metapool_staking as (

select BLOCK_TIMESTAMP, try_parse_json(CLEAN_LOG) as log,

coalesce(log:account_id, log:account)::string as account,

case when try_parse_json(CLEAN_LOG):event='STAKE' then 'stake' else 'unstake' end as event,

coalesce(log:amount::double/1e24, log:near::double/1e24, log:near::double/1e24) as amount