misaghlbQualified Pools - uniswap
Updated 2022-07-04
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
›
⌄
with qualified as (
SELECT COUNT(DISTINCT POOL_ADDRESS) as qualified_pools_count from (
SELECT POOL_ADDRESS, POOL_NAME, sum(zeroifnull(b.amount_usd)) as tvl
from ethereum.core.dim_dex_liquidity_pools a
join flipside_prod_db.ethereum.erc20_balances b on a.POOL_ADDRESS = b.USER_ADDRESS and b.balance_date = CURRENT_DATE - 5 and b.LABEL in ('uniswap')
where pool_address not in ('0xf4bfe9b4ef01f27920e490cea87fe2642a8da18d', '0x9cbfb60a09a9a33a10312da0f39977cbdb7fde23') -- clean junk
and PLATFORM in ('uniswap-v3', 'uniswap-v2')
GROUP by POOL_ADDRESS, POOL_NAME having tvl >= 100000 -- above $100K
order by tvl DESC)
),
all_pools as (
SELECT count(DISTINCT POOL_ADDRESS) as total_pools_count
from ethereum.core.dim_dex_liquidity_pools a
join flipside_prod_db.ethereum.erc20_balances b on a.POOL_ADDRESS = b.USER_ADDRESS and b.balance_date = CURRENT_DATE - 5 and b.LABEL in ('uniswap')
where pool_address not in ('0xf4bfe9b4ef01f27920e490cea87fe2642a8da18d', '0x9cbfb60a09a9a33a10312da0f39977cbdb7fde23') -- clean junk
and PLATFORM in ('uniswap-v3', 'uniswap-v2')
)
SELECT qualified_pools_count, total_pools_count,
(qualified_pools_count/total_pools_count) * 100 as qualified_percent
from all_pools, qualified
Run a query to Download Data