SELECT

DATE_TRUNC('week', m.block_timestamp) AS date,

lb.label_type AS label,

SUM(m.amount_usd) AS inflow

FROM blast.core.ez_token_transfers m

LEFT JOIN blast.core.dim_labels lb

ON m.from_address = lb.address

WHERE m.contract_address = '0x4300000000000000000000000000000000000003'

AND label = 'dex'

GROUP BY date, label

),

burn AS (

SELECT

DATE_TRUNC('week', b.block_timestamp) AS date,

lb.label_type AS label,

SUM(b.amount_usd) AS outflow

FROM blast.core.ez_token_transfers b

LEFT JOIN blast.core.dim_labels lb

ON b.to_address = lb.address

WHERE b.contract_address = '0x4300000000000000000000000000000000000003'

AND label = 'dex'

GROUP BY date, label

SELECT

COALESCE(m.date, b.date) AS date,

COALESCE(m.label, b.label) AS label,

COALESCE(m.inflow, 0) AS inflow,

-COALESCE(b.outflow, 0) AS outflow,

COALESCE(m.inflow, 0) - COALESCE(b.outflow, 0) AS net_flow,

COALESCE(m.inflow, 0) + COALESCE(b.outflow, 0) AS total_flow,

CASE

WHEN COALESCE(m.inflow, 0) + COALESCE(b.outflow, 0) = 0 THEN 0

ELSE (COALESCE(m.inflow, 0) / (COALESCE(m.inflow, 0) + COALESCE(b.outflow, 0))) * 100

END AS inflow_percentage,