with flow as (

SELECT a.*, b.label_type as from_address_label, b.label_subtype as from_address_sub_label, b.address_name as from_address_name,

c.label_type as to_address_label, c.label_subtype as to_address_sub_label, c.address_name as to_address_name

FROM avalanche.core.fact_token_transfers a

LEFT JOIN avalanche.core.dim_labels b

ON a.from_address = b.address

LEFT JOIN avalanche.core.dim_labels c

ON a.to_address = c.address

WHERE contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

)

SELECT date_trunc('day',block_timestamp) as date,

sum(CASE WHEN f.to_address_label = 'dex' THEN f.raw_amount ELSE 0 END)/pow(10,6) as USDC_swap_out,

sum(CASE WHEN f.from_address_label = 'dex' THEN f.raw_amount ELSE 0 END)/pow(10,6) as USDC_swap_in,

USDC_swap_in-USDC_swap_out as USDC_net_swapped,

case when USDC_net_swapped >0 then 'in' else 'out' end as colour

FROM flow f

WHERE block_timestamp >= dateadd('month', -1*{{last_n_month}}, current_date) and block_timestamp <= CURRENT_DATE-1

GROUP BY DATE

ORDER BY DATE DESC