theericstoneUniswap V3 Moneymakers Bags
    Updated 2023-03-10
    with winners as (
    select liquidity_provider as address,
    sum(amount0_usd) as amount0_earned,
    sum(amount1_usd) as amount1_earned,
    coalesce(amount0_earned,0) + coalesce(amount1_earned,0) as amount_earned
    from
    uniswapv3.position_collected_fees
    where block_timestamp > current_date - 14
    group by 1
    order by 4 desc
    ),

    rankwins as (
    select winners.*,
    rank() over (order by amount_earned desc) as rank
    from winners
    )

    select
    contract_address,
    case when contract_address = '0x6b3595068778dd592e39a122f4f5a5cf09c90fe2' then 'SUSHI'
    when contract_address = '0xd533a949740bb3306d119cc777fa900ba034cd52' then 'CRV'
    when contract_address = '0x3845badade8e6dff049820680d1f14bd3903a5d0' then 'SAND'
    when contract_address = '0x57ab1ec28d129707052df4df418d58a2d46d5f51' then 'sUSD'
    when contract_address = '0xa0246c9032bc3a600820415ae600c6388619a14d' then 'FARM'
    when contract_address = '0x8798249c2e607446efb7ad49ec89dd1865ff4272' then 'xSUSHI'
    when contract_address = '0x15d4c048f83bd7e37d49ea4c83a07267ec4203da' then 'GALA'
    when contract_address = '0x429881672b9ae42b8eba0e26cd9c73711b891ca5' then 'PICKLE'
    when contract_address = '0x990f341946a3fdb507ae7e52d17851b87168017c' then 'STRONG'
    when contract_address = '0x95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce' then 'SHIB'
    else symbol end as symbol,
    count(distinct(user_address)) as n_hodlers,
    sum(coalesce(amount_usd,0)) as amount_held
    from ethereum.erc20_balances
    where balance_date = (select max(balance_date) from ethereum.erc20_balances where balance_date > current_date - 3)
    and user_address in (select address from rankwins where rank < 101)
    Run a query to Download Data