theericstoneBluzelle to Exchanges
    Updated 2021-11-11
    SELECT
    metric_date,
    CASE
    WHEN to_label like 'curve%' THEN 'Curve'
    WHEN to_label = 'uniswap' THEN 'Uniswap'
    WHEN to_label_subtype = 'project_token_contract' OR
    to_label_subtype = 'project_other' OR
    to_label_subtype = 'project_contract' OR
    to_label_subtype = 'distributor_dex' OR
    to_label_subtype = 'distributor_dex_balancer' THEN 'DeFi/DEX'
    WHEN to_label like 'coinbase%' THEN 'Coinbase'
    WHEN to_label like 'binance%' THEN 'Binance'
    WHEN to_label_type like 'distributor%' AND (to_label NOT IN('coinbase', 'binance', 'curve', 'uniswap')) THEN 'Other Exchanges'
    ELSE 'Other Project'
    END AS to_segment,
    SUM(amount) as volume,
    count(distinct tx_id) as tx_count,
    count(distinct from_address) as address_count
    FROM (
    SELECT date_trunc('day', block_timestamp) AS metric_date,
    tx_id,
    from_address,
    to_label_type,
    to_label_subtype,
    to_label,
    amount
    FROM gold.ethereum_events
    WHERE contract_address = '0x5732046a883704404f284ce41ffadd5b007fd668' -- BLZ
    AND to_label_type in ('distributor','project') -- exchanges and defi projects only
    AND from_label_subtype <> 'distributor_cex_satellite' -- eliminate sweeps inside CEXes
    AND block_timestamp >= getdate() - interval '1 month'
    AND from_address NOT IN('0x0000000000000000000000000000000000000000') -- to eliminate doublecounts
    AND amount_usd > 0
    ) sq
    GROUP BY 1,2
    ORDER BY 1;

    Run a query to Download Data