hessUsers Breakdown Based on Total Transferred Volume per token
    Updated 2023-02-25
    with new_users as ( select min(block_timestamp) as date, receiver
    from osmosis.core.fact_transfers
    group by 2)
    ,
    new as ( select DISTINCT receiver
    from new_users
    where date >= CURRENT_DATE - 30)
    ,
    transfer as ( select date(block_timestamp) as date, project_name, amount/pow(10,a.decimal) as amounts, tx_id, receiver
    FROM osmosis.core.fact_transfers a LEFT outer JOIN osmosis.core.dim_tokens b on a.currency = b.address
    where receiver ilike 'osmo%'
    and (project_name ilike '%bnb%' or project_name ilike '%avax%' or project_name ilike '%mars%' or project_name ilike '%avax%' or
    project_name ilike '%ACRE%' or project_name ilike '%ARUSD%' or project_name ilike '%ngm%' or project_name ilike '%luna%' or
    project_name ilike '%matic%' or project_name ilike '%WETH%' or project_name ilike '%btc%')
    and block_timestamp >= '2023-01-01')
    ,
    price as ( select date(RECORDED_HOUR) as date, symbol, avg(price) as avg_price
    from osmosis.core.ez_prices
    where recorded_hour >= '2023-01-01'
    and symbol in (select project_name from transfer)
    group by 1,2)
    ,
    final as ( select receiver, symbol, count(DISTINCT(tx_id)) as total_transfer, count(DISTINCT(receiver)) as total_receiver, sum(amounts*avg_price) as volume,
    sum(amounts) as total_amount, avg(amounts*avg_price) as avg_volume
    from price a left outer join transfer b on a.date = b.date and a.symbol = b.project_name
    where receiver not in (select receiver from new)
    group by 1,2)

    select count(DISTINCT(receiver)) as total_user, symbol,
    case when volume <= 1 then 'Below 1$'
    when volume <= 5 then '1-5$'
    when volume <= 10 then '5-10$'
    when volume <= 25 then '10-25$'
    when volume <= 50 then '25-50$'
    when volume <= 100 then '50-100$'
    when volume <= 250 then '100-250$'
    Run a query to Download Data