MLDZMNsolcex3
    Updated 2023-06-19
    with tb1 as(SELECT
    *
    from solana.core.dim_labels
    where label_type='cex'
    )

    select
    BLOCK_TIMESTAMP::date as day,
    Concat('Network','->',Label) as pathway,
    count(distinct tx_id) as no_transfer,
    count(distinct tx_from) as no_sender,
    sum(AMOUNT) as total_volume,
    avg(AMOUNT) as avg_volume,
    sum(total_volume) over (order by day) as cum_volume
    from solana.core.fact_transfers s
    left join tb1 a on s.tx_to=a.ADDRESS
    where tx_to in (select ADDRESS from tb1)
    and tx_from not in (select ADDRESS from tb1)
    and mint = 'So11111111111111111111111111111111111111112'
    and BLOCK_TIMESTAMP>= '2023-06-05'
    group by 1,2

    union ALL

    select
    BLOCK_TIMESTAMP::date as day,
    Concat(Label,'->','Network') as pathway,
    count(distinct tx_id) as no_transfer,
    count(distinct tx_to) as no_sender,
    sum(AMOUNT) as total_volume,
    avg(AMOUNT) as avg_volume,
    sum(total_volume) over (order by day) as cum_volume
    from solana.core.fact_transfers s
    left join tb1 a on s.tx_from=a.ADDRESS
    where tx_from in (select ADDRESS from tb1)
    and tx_to not in (select ADDRESS from tb1)
    Run a query to Download Data