select * from near.core.dim_address_labels

where LABEL_TYPE ='dapp'

, cexs as (

select *

from near.core.dim_address_labels

where label_type = 'cex' and label_subtype ='deposit_wallet'

),

count_cex as (

select count(DISTINCT address) as num_users , PROJECT_NAME

from cexs

group by PROJECT_NAME

)

, other_wallet as (

select DISTINCT TX_SIGNER from near.core.fact_transactions

where tx_signer not in (select address from cexs )

)

, outflow as (

select a.* , b.PROJECT_NAME as cex, a.tx_signer as user, a.TX_RECEIVER as other_side from near.core.fact_transfers a INNER JOIN cexs b on a.tx_signer = b.address

where a.STATUS = true

, inflow as (

select *, b.PROJECT_NAME as cex ,a.tx_signer as other_side, a.TX_RECEIVER as user from near.core.fact_transfers a INNER JOIN cexs b on a.TX_RECEIVER = b.address

where TX_RECEIVER in (select address from cexs )

and STATUS = true

select count(DISTINCT tx_hash) as num_tx , count(DISTINCT tx_signer ) as num_user ,

sum(deposit)/1e24 as volume,

from outflow

where TX_RECEIVER in (select DISTINCT address from dapp)

group by cex