SocioCryptohistogram
    Updated 2022-06-20
    with bins as (
    select
    floor(diff_pcnt/10.00)*10 as bin_floor,
    count(user_address) as count
    from

    (
    SELECT user_address,
    100*(avg_past_31_days-avg_jan)/avg_jan as diff_pcnt,
    ntile(100/{{percent}}) over (order by avg_jan DESC) as ntile_percent
    FROM
    (
    SELECT user_address,
    avg(CASE WHEN balance_date between '2022-01-01' AND '2022-01-31' THEN balance end) as avg_jan,
    avg(CASE WHEN balance_date >=CURRENT_DATE-31 THEN balance end) as avg_past_31_days
    FROM flipside_prod_db.ethereum.erc20_balances
    WHERE symbol = 'TOKE' --address_name = 'tokemak: toke token'
    AND (balance_date between '2022-01-01' AND '2022-01-31' or balance_date >=CURRENT_DATE-31)
    GROUP BY user_address
    HAVING avg_jan is not null
    ) a
    LEFT JOIN ethereum.core.dim_labels b
    ON a.user_address = b.address
    qualify ntile_percent = 1

    )
    group by 1
    order by 1
    -- same query as above, just in a CTE
    )

    select
    bin_floor,
    bin_floor || ' - ' || (bin_floor + 10) as bin_range,
    Run a query to Download Data