binhachon17. [Hard] Pool Depth - Rank table - Swim with the popular
    Updated 2021-11-08
    with pool_depth as(
    select day, pool_name, rune_liquidity*rune_price_usd*2 as pool_depth, row_number() over (partition by pool_name order by day desc) as row_number from thorchain.daily_pool_stats
    qualify row_number = 1
    ),
    address_list as(
    select address, pool_name, sum(amount_provided) - sum(amount_withdrawed) as net_provided from(
    select from_address as address, sum(asset_amount_usd + rune_amount_usd) as amount_provided, 0 as amount_withdrawed, pool_name from thorchain.liquidity_actions
    where lp_action = 'add_liquidity'
    group by address, pool_name
    union all
    select from_address as address, 0 as amount_provided, sum(asset_amount_usd + rune_amount_usd) as amount_withdrawed, pool_name from thorchain.liquidity_actions
    where lp_action = 'remove_liquidity'
    group by address, pool_name
    )
    group by address, pool_name
    ),
    number_of_addresses as(
    select pool_name, count(address) as number_of_addresses from address_list
    where net_provided > 0
    group by pool_name
    ),
    final_table as(
    select pool_depth.pool_name, pool_depth, number_of_addresses from pool_depth left join number_of_addresses on pool_depth.pool_name = number_of_addresses.pool_name
    )
    select case when position('-', pool_name, 1) = 0 then pool_name else
    substr(pool_name, 1, position('-', pool_name, 1) - 1) end as pool_name, pool_depth, row_number() over (order by pool_depth desc) as pool_depth_rank,
    number_of_addresses, row_number() over (order by number_of_addresses desc) as number_of_addresses_rank,
    pool_depth_rank + number_of_addresses_rank as total_rank from final_table
    order by total_rank asc

    Run a query to Download Data