with price as (

select recorded_hour::date as datee,

symbol,

avg(close) as usdprice

from solana.price.fact_token_prices_hourly

where symbol = 'SOL'

group by 1,2

select date_trunc('{{Granularity}}',block_timestamp) as date,

case when action ='deposit' then 'Stake'

when action = 'withdraw' then 'Instant UnStake'

when action = 'withdraw_stake' then 'Delayed UnStake'

end as type,

count(DISTINCT tx_id) as txs,

count(DISTINCT address) as users,

sum(amount/pow(10,9)) as sol_amount,

sum(amount/pow(10,9)*usdprice) as usd_amount,

avg(amount/pow(10,9)*usdprice) as average_volume

from solana.defi.fact_stake_pool_actions

join price on block_timestamp::date=datee and symbol=upper(token)

where stake_pool_name = 'blazestake'

and succeeded

and action in ('deposit','withdraw','withdraw_stake')

and date >= '{{Date}}'

group by 1,2