select

date_trunc(day,HOUR)::date as price_date

,avg(CLOSE) as avg_price

from near.price.fact_prices_ohlc_hourly

where ASSET_ID='wrapped-near'

and price_date is not null

and hour::date >= '2024-03-27'

group by 1

,lst_price_raw_last as (

select top 1

date_trunc(day,HOUR)::date as price_date

,avg(CLOSE) as avg_price

from near.price.fact_prices_ohlc_hourly

where ASSET_ID='wrapped-near'

and price_date is not null

and hour::date >= '2024-03-27'

group by 1

order by price_date desc

,lst_stake as (

with lst_stake_con as (

select

tx_hash

from near.core.fact_actions_events_function_call

where block_timestamp::date>='2024-03-27'

and method_name='on_stake_action'

and RECEIPT_SUCCEEDED=true

and PREDECESSOR_ID='shitzu.pool.near'

)

select

block_timestamp::date as date

,tx_hash

,signer_id as staker

,deposit/1e24 as amount