maybeyonastop_10_lp-top_10_pools
Updated 2021-09-29
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
WITH top5_pools as(
SELECT
st.block_timestamp as date,
st.pool_name,
(st.token0_balance_usd + st.token1_balance_usd) as total_bal,
st.pool_address
FROM uniswapv3.pool_stats st
INNER JOIN (
SELECT MAX(p1.block_timestamp) as maxDate, p1.pool_address
FROM uniswapv3.pool_stats p1
GROUP BY p1.pool_address
) p2 ON st.pool_address = p2.pool_address AND st.block_timestamp= p2.maxDate
WHERE total_bal IS NOT NULL
and st.pool_address != '0xcdb4e35b235845ae8d0c27f77cd8176ddfc26965'
ORDER BY total_bal DESC
LIMIT 10
),
all_lp AS (
SELECT
liquidity_provider,
pool_address,
pool_name,
COUNT(block_id) as n_tx
FROM
uniswapv3.positions
WHERE
pool_address in (select pool_address from top5_pools)--= '0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8' -- USDC-WETH v3
GROUP BY
liquidity_provider,pool_address,pool_name
ORDER BY
n_tx DESC
),
Run a query to Download Data