SELECT

AVG(PRICE) AS USD_PRICE

FROM avalanche.price.ez_prices_hourly

WHERE TOKEN_ADDRESS = LOWER('0xC891EB4cbdEFf6e073e859e987815Ed1505c2ACD')

AND HOUR >= CURRENT_DATE - INTERVAL '360 days'

),

DAILY_FLOWS AS (

SELECT

DATE_TRUNC('week', BLOCK_TIMESTAMP) as DATE,

CASE

WHEN CONTRACT_ADDRESS = '0xad6605f4987031fd2d6d6816be53eb7c5b764bf7' THEN 'XTBT'

WHEN CONTRACT_ADDRESS = '0xbb9360d57f68075e98d022784c12f2fda082316b' THEN 'XRV'

WHEN CONTRACT_ADDRESS = '0xbfdef5e389bb403426337081ecd1d05bc5193203' THEN 'XEVT'

END as TOKEN,

CASE WHEN EVENT_NAME IN ('PoolDeposit','CrossChainTransferMintDestination','Deposit')

THEN SUM(DECODED_LOG:shares / 1E6) * COALESCE(p.USD_PRICE, 1)

ELSE -1 * SUM(DECODED_LOG:shares / 1E6) * COALESCE(p.USD_PRICE, 1)

END as AMOUNT,

CASE WHEN EVENT_NAME IN ('PoolDeposit','CrossChainTransferMintDestination','Deposit')

THEN 'MINT' ELSE 'BURN'

END as FLOW_TYPE

FROM avalanche.core.ez_decoded_event_logs e

LEFT JOIN XEVT_PRICE p ON e.CONTRACT_ADDRESS = '0xbfdef5e389bb403426337081ecd1d05bc5193203'

WHERE CONTRACT_ADDRESS IN (

'0xad6605f4987031fd2d6d6816be53eb7c5b764bf7', -- XTBT

'0xbb9360d57f68075e98d022784c12f2fda082316b', -- XRV

'0xbfdef5e389bb403426337081ecd1d05bc5193203' -- XEVT

)

AND (

EVENT_NAME IN ('PoolDeposit','CrossChainTransferMintDestination','Deposit','Withdraw','WithdrawEarlyRepay'))

GROUP BY 1, 2, EVENT_NAME, p.USD_PRICE

SELECT