SELECT

CASE WHEN e.origin_function_signature in ('0x6e553f65','0x06bf0dd3','0x6a761202') THEN 'Deposit' ELSE 'Withdraw' END as type,

COUNT(DISTINCT e.tx_hash) as actions,

COUNT(DISTINCT CASE WHEN e.origin_function_signature='0x6e553f65' THEN decoded_log:to ELSE decoded_log:from END) as active_users,

SUM(decoded_log:value/1e6) as amount,

CASE

WHEN e.contract_address = lower('0xBFdEf5e389bB403426337081eCD1D05bC5193203') THEN 'Flexible Term EURC Vault'

WHEN e.contract_address = lower('0xad6605F4987031fd2d6d6816bE53Eb7C5b764bf7') THEN 'Flexible Term USDC Vault'

WHEN e.contract_address = lower('0xBB9360d57F68075e98D022784C12F2Fda082316B') THEN 'Rates+ Vault'

END as vault_name

FROM avalanche.core.ez_decoded_event_logs e

WHERE e.contract_address IN (

lower('0xBFdEf5e389bB403426337081eCD1D05bC5193203'),

lower('0xad6605F4987031fd2d6d6816bE53Eb7C5b764bf7'),

lower('0xBB9360d57F68075e98D022784C12F2Fda082316B')

)

AND e.origin_function_signature IN ('0x6e553f65','0xde0c7fdf','0x06bf0dd3','0x6a761202','0x000b9050')

AND e.event_name = 'Transfer'

GROUP BY 1, 5

),

avg_price AS (

SELECT

AVG(price) as avg_price

FROM avalanche.price.ez_prices_hourly

WHERE token_address = lower('0xB97EF9Ef8734C71904D8002F8b6Bc66Dd9c48a6E')

SELECT

t.vault_name,

SUM(CASE WHEN type = 'Deposit' THEN actions ELSE 0 END) as total_deposits,

SUM(CASE WHEN type = 'Withdraw' THEN actions ELSE 0 END) as total_withdrawals,

SUM(CASE WHEN type = 'Deposit' THEN active_users ELSE 0 END) as total_depositors,

SUM(CASE WHEN type = 'Withdraw' THEN active_users ELSE 0 END) as total_withdrawers,

SUM(CASE WHEN type = 'Deposit' THEN amount ELSE 0 END) as total_deposited_tokens,

SUM(CASE WHEN type = 'Withdraw' THEN amount ELSE 0 END) as total_withdrawn_tokens,

SUM(CASE WHEN type = 'Deposit' THEN amount ELSE -amount END) as net_amount_tokens,