SELECT

CASE

WHEN origin_function_signature = '0x9dcaafb4' THEN '$APE Only Pool'

WHEN origin_function_signature = '0x8ecbffa7' THEN 'MAYC Pool'

WHEN origin_function_signature = '0x46583a05' THEN 'BAYC Pool'

WHEN origin_function_signature = '0xd346cbd9' THEN 'BAKC Pool'

ELSE NULL

END AS pool_type,

SUM(DECODED_LOG:value::decimal / 1e18) AS Total_Staked_APE

FROM ethereum.core.ez_decoded_event_logs logs

WHERE ORIGIN_TO_ADDRESS = LOWER('0x5954ab967bc958940b7eb73ee84797dc8a2afbb9')

AND event_name = 'Transfer'

AND tx_succeeded = TRUE

AND decoded_log:to::string = LOWER('0x5954ab967bc958940b7eb73ee84797dc8a2afbb9')

AND block_timestamp >= CURRENT_DATE - 365 -- Adding time filter

GROUP BY 1

),

unstake_data AS (

SELECT

CASE

WHEN origin_function_signature = '0xc63389c3' THEN 'MAYC Pool'

WHEN origin_function_signature = '0xfe31446c' THEN 'BAYC Pool'

WHEN origin_function_signature = '0x7f60d338' THEN '$APE Only Pool'

WHEN origin_function_signature = '0x26fb2249' THEN 'BAKC Pool'

ELSE NULL

END AS pool_type,

SUM(CASE

WHEN event_name = 'Withdraw' THEN DECODED_LOG:amount::decimal / 1e18

WHEN event_name IN ('WithdrawNft', 'WithdrawPairNft') THEN

CASE

WHEN DECODED_LOG:isPrincipal::boolean = TRUE THEN DECODED_LOG:amount::decimal / 1e18

ELSE 0

END

ELSE 0

END) AS Total_Unstaked_APE