from near.core.dim_staking_actions)

,

price as ( select timestamp::date as date, avg(price_usd) as avg_price

from near.core.fact_prices

where symbol ilike '%near%'

group by 1)

,

final as ( select a.date, action , pool_address, tx_hash, tx_signer, raw_amount, raw_amount*avg_price as stake_usd

from stake a left outer join price b on a.date = b.date )

select pool_address , sum(raw_amount) as near_amoumt, count(DISTINCT(tx_hash)) as total_tx,

avg(raw_amount) as avg_stake_amount, median(raw_amount) as median_amount,

sum(stake_usd) as volume,

avg(stake_usd) as avg_volume,

median(stake_usd) as median_stake,

max(stake_usd) as max_usd

from final

where action = 'Stake' and stake_usd is not null

group by 1

order by 6 desc

limit 10