SELECT

DATE_TRUNC('hour', TO_TIMESTAMP(BLOCK_TIMESTAMP)) AS date,

SUM(CASE WHEN

TO_ADDRESS = LOWER(CASE WHEN TO_TIMESTAMP(BLOCK_TIMESTAMP) > '2023-10-08' THEN '0x563395A2a04a7aE0421d34d62ae67623cAF67D03' ELSE '0xA481B139a1A654cA19d2074F174f17D7534e8CeC' END)

THEN AVAX_VALUE ELSE 0 END) AS daily_inflow,

SUM(CASE WHEN

FROM_ADDRESS = LOWER(CASE WHEN TO_TIMESTAMP(BLOCK_TIMESTAMP) > '2023-10-08' THEN '0x563395A2a04a7aE0421d34d62ae67623cAF67D03' ELSE '0xA481B139a1A654cA19d2074F174f17D7534e8CeC' END)

THEN AVAX_VALUE ELSE 0 END) AS daily_outflow

FROM avalanche.core.fact_traces

WHERE ((TO_ADDRESS = LOWER('0xA481B139a1A654cA19d2074F174f17D7534e8CeC') OR FROM_ADDRESS = LOWER('0xA481B139a1A654cA19d2074F174f17D7534e8CeC'))

OR (TO_ADDRESS = LOWER('0x563395A2a04a7aE0421d34d62ae67623cAF67D03') OR FROM_ADDRESS = LOWER('0x563395A2a04a7aE0421d34d62ae67623cAF67D03')))

AND TX_STATUS = 'SUCCESS'

AND trace_status = 'SUCCESS' AND type <> 'DELEGATECALL'

GROUP BY date

),

cumulative_traces AS (

SELECT

date,

SUM(daily_inflow) OVER (ORDER BY date) AS cumulative_inflow,

SUM(daily_outflow) OVER (ORDER BY date) AS cumulative_outflow

FROM daily_traces

dt.date,

dt.daily_inflow AS inflow,

dt.daily_outflow AS outflow,

(dt.daily_inflow - dt.daily_outflow) AS net_inflow,

ct.cumulative_inflow - ct.cumulative_outflow AS cumulative_balance

FROM daily_traces dt

JOIN cumulative_traces ct ON dt.date = ct.date

ORDER BY dt.date;