SELECT

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

CASE WHEN e.origin_function_signature in ('0x6e553f65','0x06bf0dd3') 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

FROM avalanche.core.ez_decoded_event_logs e

WHERE e.contract_address = lower('0xBFdEf5e389bB403426337081eCD1D05bC5193203')

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

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,

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

FROM transfers t

LEFT JOIN daily_prices p ON t.date = p.date

GROUP BY 1

SELECT

t.date,

t.type,

t.actions,

SUM(t.actions) OVER (PARTITION BY t.type ORDER BY t.date) as total_actions,

t.active_users,