flows as (

select

date_trunc('day',block_timestamp) as date

, coalesce(labels_from.label, labels_to.label) as cex

, sum(case when labels_to.label is not null then amount * close else 0 end) as inflow

, sum(case when labels_from.label is not null then amount * close else 0 end) * (-1) as outflow

from solana.core.fact_transfers transfers

left join solana.core.dim_labels labels_from

on transfers.tx_from = labels_from.address

and labels_from.label_type = 'cex'

left join solana.core.dim_labels labels_to

on transfers.tx_to = labels_to.address

and labels_to.label_type = 'cex'

inner join solana.price.ez_token_prices_hourly prices

on date_trunc('hour', transfers.block_timestamp) = prices.recorded_hour

and transfers.mint = prices.token_address

where (labels_from.label_type is null or labels_to.label_type is null)

and (labels_from.label_type is not null or labels_to.label_type is not null)

and transfers.block_timestamp ::date >= current_date - 30

and transfers.mint not in ( -- bugged prices

'B584xmGMbrJz2FewT6fxNx64vQ74zrbQijHHijszSa7K'

)

group by 1,2