drone-mostafaCEX DEX copy
    Updated 2023-05-22
    SELECT 'From CEX TO DEX' as type,
    --date_trunc ('month',BLOCK_TIMESTAMP) as date,
    count (DISTINCT TX_HASH) as TXN,
    count (DISTINCT ORIGIN_FROM_ADDRESS) as Users,
    sum (AMOUNT_USD) as USD

    FROM ethereum.core.ez_token_transfers t1
    LEFT JOIN ethereum.core.dim_labels t2 on t2.ADDRESS = t1.TO_ADDRESS
    LEFT JOIN ethereum.core.dim_labels t3 on t3.ADDRESS = t1.FROM_ADDRESS

    WHERE t3.LABEL_TYPE = 'cex' and t2.LABEL_TYPE = 'dex' -- from cex to dex
    and t1.CONTRACT_ADDRESS = '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984' --UNI
    GROUP BY 1

    UNION

    SELECT 'From DEX TO CEX' as type,
    --date_trunc ('month',BLOCK_TIMESTAMP) as date,
    count (DISTINCT TX_HASH) as TXN,
    count (DISTINCT ORIGIN_FROM_ADDRESS) as Users,
    sum (AMOUNT_USD) as USD


    FROM ethereum.core.ez_token_transfers t1
    LEFT JOIN ethereum.core.dim_labels t2 on t2.ADDRESS = t1.TO_ADDRESS
    LEFT JOIN ethereum.core.dim_labels t3 on t3.ADDRESS = t1.FROM_ADDRESS

    WHERE t3.LABEL_TYPE = 'dex' and t2.LABEL_TYPE = 'cex' -- from cex to dex
    and t1.CONTRACT_ADDRESS = '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984' --UNI
    GROUP BY 1


    Run a query to Download Data