binhachonCopy of Top TOKE Holders - #2
    Updated 2022-06-20
    with top100 as (
    select
    user_address,
    label_type,
    balance as jan_balance,
    amount_usd as jan_balance_usd
    from flipside_prod_db.ethereum.erc20_balances
    where contract_address = lower('0x2e9d63788249371f1DFC918a52f8d799F4a38C94')
    and balance_date = '2022-01-01'
    order by balance desc
    limit 100
    ),
    current_balance as (
    select
    *
    from flipside_prod_db.ethereum.erc20_balances
    where contract_address = lower('0x2e9d63788249371f1DFC918a52f8d799F4a38C94')
    and balance_date in (select max(balance_date) from flipside_prod_db.ethereum.erc20_balances)
    and user_address in (select user_address from top100)
    ),
    user_stats as (
    select
    top100.user_address,
    top100.label_type,
    jan_balance,
    coalesce(balance, 0) as current_balance,
    case when jan_balance > current_balance then current_balance / jan_balance * 100 else 100 end as perc_hold,
    case when jan_balance > current_balance then 'Not keep' else 'Keep' end as position_status,
    coalesce(amount_usd, 0) as current_balance_usd
    from top100
    left join current_balance on (top100.user_address = current_balance.user_address)
    )
    select
    position_status,
    count(*) as number_of_users,
    avg(perc_hold) as avg_perc_hold
    Run a query to Download Data