outs as (

select

trunc(block_timestamp,'day') as date,

count(distinct tx_hash) as swap_sold,

sum(amount_in) as volume_sold

from ethereum.core.ez_dex_swaps

where symbol_in in ('USDC')

and amount_in > 0

and amount_in < 1e9

and date between CURRENT_DATE - INTERVAL '40 DAYS' and CURRENT_DATE-1

group by 1

),

ins as (

select

trunc(block_timestamp,'day') as date,

count(distinct tx_hash ) as swap_bought,

sum(amount_out) as volume_bought

from ethereum.core.ez_dex_swaps

where symbol_out in ('USDC')

and amount_out > 0

and amount_out < 1e9

and date between CURRENT_DATE - INTERVAL '40 DAYS' and CURRENT_DATE-1

group by 1

)

select

ifnull(ins.date,outs.date) as dates,

case when dates < '2022-09-30' then 'Before event'

else 'After event' end as period,

swap_sold*(-1) as swap_solds,

swap_bought,

volume_sold*(-1) as volume_solds,

volume_bought,

swap_bought - swap_sold as netflow_swaps,

volume_bought - volume_sold as netflow_volume

from ins join outs on ins.date=outs.date