date_trunc('{{Interval}}', HOUR) as date,

avg(price) as avg_price

from ethereum.core.fact_hourly_token_prices

where symbol in ('WETH')

group by 1

)

, perp_tx as (

select

a.block_timestamp,

case

when origin_function_signature in ('0xf3daeacc','0xef8c5994','0x364e2311','0x53a8aa03') then 'Stake'

when origin_function_signature in ('0x078580d2','0x64f64467','0xabb5e5e2','0x0f3aa554') then 'UnStake'

end as action_type,

a.origin_from_address,

a.tx_hash,

tx_fee * avg_price as fee_usd

from arbitrum.core.fact_event_logs a left join arbitrum.core.fact_transactions b USING(tx_hash)

left join price c on a.block_timestamp::date = c.date

where origin_to_address = '0xa906f338cb21815cbc4bc87ace9e68c87ef8d8f1'

and origin_function_signature in ('0xf3daeacc','0xef8c5994','0x364e2311','0x53a8aa03','0x078580d2','0x64f64467','0xabb5e5e2','0x0f3aa554')

)

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

action_type,

count(distinct tx_hash) as TX_Count,

count(distinct origin_from_address) as Users_Count,

sum(fee_usd) as fee_amt,

sum(TX_Count) over (partition by action_type order by date) as cum_tx_cnt,

sum(fee_amt) over (partition by action_type order by date) as cum_fee_USD

FROM perp_tx

WHERE action_type is not null

GROUP by 1,2

ORDER by 1