drone-mostafaplatforms
    Updated 2023-05-22
    with base as (SELECT
    date_trunc ('month',BLOCK_TIMESTAMP) as date,
    concat ('CEX: ',t3.LABEL, ' => ','DEX: ',t2.LABEL) as type,
    count (DISTINCT TX_HASH) as TXN,
    count (DISTINCT ORIGIN_FROM_ADDRESS) as Users,
    sum (AMOUNT_USD) as USD,

    sum (TXN) over (partition by type order by date) as Cum_TXN,
    sum (Users) over (partition by type order by date) as Cum_Users,
    sum (USD) over (partition by type order by date) as Cum_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
    and AMOUNT_USD > 0
    GROUP BY 1,2

    UNION

    SELECT
    date_trunc ('month',BLOCK_TIMESTAMP) as date,
    concat ('DEX: ',t3.LABEL, ' => ','CEX: ',t2.LABEL) as type,
    count (DISTINCT TX_HASH) as TXN,
    count (DISTINCT ORIGIN_FROM_ADDRESS) as Users,
    sum (AMOUNT_USD) as USD,

    sum (TXN) over (partition by type order by date) as Cum_TXN,
    sum (Users) over (partition by type order by date) as Cum_Users,
    sum (USD) over (partition by type order by date) as Cum_USD

    FROM ethereum.core.ez_token_transfers t1
    LEFT JOIN ethereum.core.dim_labels t2 on t2.ADDRESS = t1.TO_ADDRESS
    Run a query to Download Data