with price as (select

hour::date as dayz,

avg (price) as eth_price

from ethereum.core.fact_hourly_token_prices

where symbol ='WETH' group by 1),

platform_staking as (select

'StakeWise' as platform,

block_timestamp::date as dt, tx_hash, case when text_signature is null then 'Referral Stake' else text_signature end as signature, origin_function_signature,

origin_from_address,

event_inputs:value / POW(10,18) as amount,

amount * eth_price as usd_price

from ethereum.core.fact_event_logs l

left join ethereum.core.dim_function_signatures s ON l.origin_function_signature = s.BYTES_SIGNATURE

left join price ON block_timestamp::date = dayz

where origin_to_address = '0xc874b064f465bdd6411d45734b56fac750cda29a' AND tx_status = 'SUCCESS' and event_inputs is not null --same tx will have multiple column , null event_inputs is not needed (unwanted data)

and origin_function_signature in ('0x4a58db19','0x3a4b66f1','0x040dee8a') -- addDeposit , Stake() , Referral Stake

UNION ALL

select

'stkr' as platform,

block_timestamp::date as dt, tx_hash, text_signature as signature, origin_function_signature,

origin_from_address,

event_inputs:value / POW(10,18) as amount,

amount * eth_price as usd_price

from ethereum.core.fact_event_logs l

left join ethereum.core.dim_function_signatures s ON l.origin_function_signature = s.BYTES_SIGNATURE

left join price ON block_timestamp::date = dayz

where origin_to_address = '0x84db6ee82b7cf3b47e8f19270abde5718b936670' AND tx_status = 'SUCCESS' and event_inputs:from = '0x84db6ee82b7cf3b47e8f19270abde5718b936670'

and origin_function_signature in ('0x9fa65c56','0xeb834a2c') -- stakeAndClaimAethC(), stakeAndClaimAethB()

UNION ALL