SELECT

trunc(block_timestamp,'month')as month,

ETH_FROM_ADDRESS AS wallet_address,

-SUM(AMOUNT_USD) AS outflow

FROM ethereum.core.ez_eth_transfers

GROUP BY 1,2

),

wallet_inflows as (

SELECT

trunc(block_timestamp,'month')as month,

ETH_TO_ADDRESS AS wallet_address,

SUM(AMOUNT_USD) AS inflow

FROM ethereum.core.ez_eth_transfers

GROUP BY 1,2

),

wallet_flows as (

ifnull(x.month,y.month) as months,

ifnull(x.wallet_address,y.wallet_address) as wallet,

ifnull(inflow,0) as inflow,

ifnull(outflow,0) as outflow

from wallet_inflows x

join wallet_outflows y on x.month=y.month and x.wallet_address=y.wallet_address

where inflow>0 and outflow>0

),

monthly_balances AS (

SELECT months, wallet, SUM(inflow + outflow) AS balance

FROM wallet_flows

GROUP BY 1, 2

),

monthly_totals AS (

SELECT months, SUM(balance) AS total_balance

FROM monthly_balances where balance is not null and balance >0

GROUP BY months

),