binhachonUntitled Query
    Updated 2022-02-15
    with liquidity_pools as (
    select
    pool_address,
    pool_name,
    token0,
    token1
    from ethereum.dex_liquidity_pools
    where platform ilike '%uni%'
    ),
    liquidity_pool_token as (
    select
    user_address,
    contract_address,
    amount_usd
    from ethereum.erc20_balances
    left join liquidity_pools on (user_address = pool_address and (contract_address = token0 or contract_address = token1))
    where balance_date in (select distinct balance_date from ethereum.erc20_balances order by balance_date desc limit 1)
    ),
    account_balance as (
    select
    user_address,
    count(amount_usd) as number_of_line,
    case
    when number_of_line = 1 then sum(amount_usd) * 2
    else sum(amount_usd) end
    as balance
    from liquidity_pool_token
    group by user_address
    ),
    top_5_liquidity_pools as (
    select
    pool_address,
    pool_name,
    coalesce(balance,0) as liquidity_usd,
    row_number() over (order by liquidity_usd desc) as rank
    from liquidity_pools
    Run a query to Download Data