with staked as (

select

date_trunc('day', timestamp) as stringdate,

count(*) as staked_count, -- Count the number of stakes

sum(cast(input_2_value_uint256 as double)) / 1e6 as total_staked_amount, -- Total staked amount

avg(cast(input_2_value_uint256 as double)) / 1e6 as avg_stake_size -- Average stake size

from evm_events_arbitrum_mainnet_v1

where address = '0xaf88d065e77c8cc2239327c5edb3a432268e5831'

and input_1_value_address = '0x2df1c51e09aecf9cacb7bc98cb1742757f163df7' -- to address

and signature = 'Transfer(address,address,uint256)'

group by date_trunc('day', timestamp)

redeemed as (

select

date_trunc('day', timestamp) as stringdate,

count(*) as redeemed_count, -- Count the number of redemptions

sum(cast(input_2_value_uint256 as double)) / 1e6 as total_redeemed_amount, -- Total redeemed amount

avg(cast(input_2_value_uint256 as double)) / 1e6 as avg_redemption_size -- Average redemption size

from evm_events_arbitrum_mainnet_v1

where address = '0xaf88d065e77c8cc2239327c5edb3a432268e5831'

and input_0_value_address = '0x2df1c51e09aecf9cacb7bc98cb1742757f163df7' -- from address

and signature = 'Transfer(address,address,uint256)'

group by date_trunc('day', timestamp)

combined as (

select

coalesce(s.stringdate, r.stringdate) as stringdate,

s.staked_count,

r.redeemed_count,

s.total_staked_amount,

r.total_redeemed_amount,

s.avg_stake_size,

r.avg_redemption_size

from staked s

full outer join redeemed r on s.stringdate = r.stringdate -- full outer join to ensure we capture all dates

stringdate,

coalesce(staked_count, 0) as staked_count,

coalesce(redeemed_count, 0) as redeemed_count,

coalesce(total_staked_amount, 0) as total_staked_amount,

coalesce(total_redeemed_amount, 0) as total_redeemed_amount,

coalesce(avg_stake_size, 0) as avg_stake_size,

coalesce(avg_redemption_size, 0) as avg_redemption_size

from combined

order by stringdate desc;