with flow as (

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

c.label_type as to_address_label, c.label_subtype as to_address_sub_label

FROM gnosis.core.ez_xdai_transfers a

LEFT JOIN gnosis.core.dim_labels b

ON a.eth_from_address = b.address

LEFT JOIN gnosis.core.dim_labels c

ON a.eth_to_address = c.address

SELECT

date_trunc('day',block_timestamp) as day,

sum(CASE WHEN f.to_address_label = 'layer2' THEN f.amount ELSE 0 END) as xdai_out,

sum(CASE WHEN f.from_address_label = 'layer2' THEN f.amount ELSE 0 END) as xdai_in,

(xdai_in-xdai_out) as xdai_net_flow

FROM flow as f

WHERE day >= CURRENT_DATE - 45

GROUP BY 1

ORDER BY 1 DESC