ins as (

select

trunc(block_timestamp,'day') as date,

count(distinct trader) as traders_in,

count(distinct tx_id) as swaps_in,

sum(token_in_amount) as volume_in

from flow.defi.ez_swaps where token_in_contract='A.5207737ccdc76d11.StudentsofTheGame'

and token_in_destination=trader

group by 1

),

outs as (

select

trunc(block_timestamp,'day') as date,

count(distinct trader) as traders_out,

count(distinct tx_id) as swaps_out,

sum(token_out_amount) as volume_out

from flow.defi.ez_swaps where token_out_contract='A.5207737ccdc76d11.StudentsofTheGame'

and token_out_source=trader

group by 1

),

final as (

ifnull(x.date,y.date) as dates,

ifnull(x.traders_in,0) as traders_in,

ifnull(y.traders_out,0) as traders_out,

ifnull(x.swaps_in,0) as swaps_in,

ifnull(y.swaps_out,0) as swaps_out,

ifnull(volume_in,0) as volume_deposited,

ifnull(volume_out,0) as volume_withdrawn

from ins x left join outs y on x.date=y.date

order by 1 asc

select *, traders_in-traders_out as netflow_traders,

swaps_in-swaps_out as netflow_swaps,