SELECT a.block_timestamp, a.raw_amount, b.label_type as from_address_label, b.label_subtype as from_address_sub_label, b.project_name as from_project_name,

c.label_type as to_address_label, c.label_subtype as to_address_sub_label, c.project_name as to_project_name

FROM arbitrum.core.fact_token_transfers a

LEFT JOIN arbitrum.core.dim_labels b

ON a.from_address = b.address

LEFT JOIN arbitrum.core.dim_labels c

ON a.to_address = c.address

WHERE contract_address = '0xff970a61a04b1ca14834a43f5de4533ebddb5cc8'

)

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

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

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

usdc_flow_in-usdc_flow_out as usdc_net_flow

FROM flow f

WHERE date >= '2023-01-01'-- CURRENT_DATE-1

GROUP BY DATE

ORDER BY DATE DESC