sarathop portfolio1
    Updated 2022-11-08
    WITH outflows as (SELECT block_timestamp::date as day_time,
    eth_from_address,
    sum(amount) as eth_outflows,
    sum(eth_outflows) over (partition by eth_from_address order by day_time) as cum_eth_outflows
    FROM optimism.core.ez_eth_transfers
    GROUP BY 1,2),

    inflows as (SELECT block_timestamp::date as day_time,
    eth_to_address,
    sum(amount) as eth_inflows,
    sum(eth_inflows) over (partition by eth_to_address order by day_time) as cum_eth_inflows
    FROM optimism.core.ez_eth_transfers
    GROUP BY 1,2)

    SELECT outflows.day_time,
    avg(cum_eth_inflows - cum_eth_outflows) as net_flow
    FROM outflows
    JOIN inflows
    ON outflows.day_time=inflows.day_time AND outflows.eth_outflows=inflows.eth_inflows
    GROUP BY 1
    Run a query to Download Data