SELECT

TRUNC(e.block_timestamp,'day') as date,

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, 2

),

daily_prices AS (

SELECT

TRUNC(hour, 'day') as date,

AVG(price) as avg_daily_price

FROM avalanche.price.ez_prices_hourly

WHERE token_address = lower('0xB97EF9Ef8734C71904D8002F8b6Bc66Dd9c48a6E')

GROUP BY 1

),

daily_net AS (

SELECT

t.date,

--t.vault_name,

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

SUM(CASE WHEN type = 'Deposit' THEN amount * p.avg_daily_price ELSE -amount * p.avg_daily_price END) as net_amount_usd