kakamoraETH Transfers to CEX
    Updated 2022-09-29
    WITH cex_addys as (
    SELECT
    INITCAP(replace(TAG_NAME, 'user', '')) as cex_name,
    address
    FROM crosschain.core.address_tags
    where tag_type ='cex'
    ),
    trf_into_cex as (
    SELECT
    'Moved to CEX' as type,
    date_trunc(day, block_timestamp) as date,
    sum(amount) as eth_volume,
    count(tx_hash) as transfers,
    case when date >= '2022-09-15' then 'Post Merge' else 'Pre Merge' end as period
    FROM ethereum.core.ez_eth_transfers trf
    INNER JOIN cex_addys ON origin_to_address = address
    WHERE block_timestamp >= '2022-08-01'
    GROUP BY date
    ORDER BY date desc, eth_volume desc
    ),
    trf_out_of_cex as (
    SELECT
    'Removed From CEX' as type,
    date_trunc(day, block_timestamp) as date,
    -1 * sum(amount) as eth_volume,
    count(tx_hash) as transfers
    FROM ethereum.core.ez_eth_transfers trf
    INNER JOIN cex_addys ON origin_from_address = address
    WHERE block_timestamp >= '2022-08-01'
    GROUP BY date
    ORDER BY date desc, eth_volume desc
    )
    select * from trf_into_cex