SELECT

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

SUM(CASE WHEN TO_ADDRESS = LOWER('0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4') THEN VALUE ELSE 0 END) AS daily_inflow,

SUM(CASE WHEN FROM_ADDRESS = LOWER('0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4') THEN VALUE ELSE 0 END) AS daily_outflow

FROM base.core.fact_traces

WHERE (TO_ADDRESS = LOWER('0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4') OR FROM_ADDRESS = LOWER('0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4')) AND TX_STATUS = 'SUCCESS'

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;