hour::date as date,

avg(price) as price_usd

from near.price.ez_prices_hourly

where token_address = 'wrap.near'

group by 1

),

main as (

tx_hash,

block_timestamp,

signer_id as user,

amount,

action,

amount * price_usd as amount_usd

from near.gov.fact_staking_actions

left join pricet on date = block_timestamp::date

where block_timestamp::date between '{{ start_date }}' and '{{ end_date }}'

date_trunc('{{ period }}', block_timestamp) as date,

action,

count(distinct tx_hash) as transactions,

count(distinct user) as users,

sum(amount) as volume_near,

avg(amount) as average_amount_near,

sum(amount_usd) as volume_usd,

avg(amount_usd) as average_amount_usd,

median(amount_usd) as median_amount_usd,

sum(transactions) over (partition by action order by date) as cumulative_transactions,

sum(volume_usd) over (partition by action order by date) as cumulative_volume_usd

from main

where action in ('staking', 'unstaking')