SELECT

DATE_TRUNC('day', block_timestamp) AS day,

from_address AS address,

SUM(value) AS eth_out,

0 AS eth_in

FROM

kaia.core.fact_traces

WHERE

tx_succeeded = true AND from_address IS NOT NULL

GROUP BY

1, 2

UNION ALL

SELECT

DATE_TRUNC('day', block_timestamp) AS day,

to_address AS address,

0 AS eth_out,

SUM(value) AS eth_in

FROM

kaia.core.fact_traces

WHERE

tx_succeeded = true AND to_address IS NOT NULL

GROUP BY

1, 2

),

-- 주소별 입출금 계산

address_daily_balances AS (

SELECT

dt.day,

dt.address,

SUM(COALESCE(dt.eth_in, 0)) - SUM(COALESCE(dt.eth_out, 0)) AS net_change

FROM

daily_traces dt

GROUP BY

dt.day, dt.address