to_cex as (select

date_trunc('week', BLOCK_TIMESTAMP) as date,

avg (CLOSE) as Price,

count (distinct TX_ID) as TXN,

count (distinct SENDER) as Users,

sum ( DISTINCT AMOUNT * CLOSE) as USD

FROM flow.core.ez_token_transfers

JOIN flow.core.fact_hourly_prices ON ID = TOKEN_CONTRACT and RECORDED_HOUR::DATE = BLOCK_TIMESTAMP::DATE

JOIN crosschain.core.address_labels L ON ADDRESS = RECIPIENT

WHERE AMOUNT > 0 and LABEL_TYPE = 'cex' AND TX_SUCCEEDED = 'true'

group by 1),

from_cex as (select

date_trunc('week', BLOCK_TIMESTAMP) as date,

avg (CLOSE) as Price,

count (distinct TX_ID) as TXN,

count (distinct RECIPIENT) as Users,

sum ( DISTINCT AMOUNT * CLOSE) as USD

FROM flow.core.ez_token_transfers

JOIN flow.core.fact_hourly_prices ON ID = TOKEN_CONTRACT and RECORDED_HOUR::DATE = BLOCK_TIMESTAMP::DATE

JOIN crosschain.core.address_labels L ON ADDRESS = SENDER

WHERE AMOUNT > 0 and LABEL_TYPE = 'cex' AND TX_SUCCEEDED = 'true'

group by 1)

SELECT t1.DATE AS TME,

sum (t1.TXN) AS Inflow_TXN,

sum (t2.TXN) * -1 AS Outflow_TXN,

sum (t1.Users) AS Inflow_Users,

sum (t2.Users) * -1 AS Outflow_Users,