Abolfazl_771025user categorize by bridge volume (Arbitrum)
    Updated 2023-02-23
    with main as (select
    DISTINCT origin_from_address as user,
    count(DISTINCT tx_hash) as tx_count,
    sum(raw_amount/power(10,decimals)) as volume
    from arbitrum.core.fact_token_transfers left outer join arbitrum.core.dim_contracts on address like contract_address
    where to_address like lower('0x856cb5c3cBBe9e2E21293A644aA1f9363CEE11E8')
    and not tx_hash in (select tx_hash from arbitrum.core.fact_event_logs where contract_address like lower('0x126bE6a9ec71A1FeA19D2288Ba2Ce4cDC0faCB68'))
    group by 1
    union
    select
    DISTINCT origin_from_address as user,
    count(DISTINCT tx_hash) as tx_count,
    sum(AMOUNT_USD) as volume
    from arbitrum.core.ez_eth_transfers
    where eth_to_address like lower('0x856cb5c3cBBe9e2E21293A644aA1f9363CEE11E8')
    and not tx_hash in (select tx_hash from arbitrum.core.fact_event_logs where contract_address like lower('0x126bE6a9ec71A1FeA19D2288Ba2Ce4cDC0faCB68'))
    group by 1)
    select
    case
    when volume < 10 then 'Bridge less than 10 USD'
    when volume between 10 and 49.99 then 'Bridge 10 - 50 USD'
    when volume between 50 and 99.99 then 'Bridge 50 - 100 USD'
    when volume between 100 and 499.99 then 'Bridge 100 - 500 USD'
    when volume between 500 and 999.99 then 'Bridge 500 - 1000 USD'
    when volume between 1000 and 4999.99 then 'Bridge 1 K - 5 K USD'
    when volume between 5000 and 9999.99 then 'Bridge 5 K - 10 K USD'
    when volume between 10000 and 99999.99 then 'Bridge 10 K - 50 K USD'
    when volume between 50000 and 999999.99 then 'Bridge 50 K - 100 K USD'
    else 'Bridge over 100 K USD'
    end as bridge_volume,
    count(user) as user_count
    from main
    group by 1