CryptoLionTop Providers
Updated 2022-11-27
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
›
⌄
WITH x as (SELECT
lpa.liquidity_provider,
SUM(CASE WHEN action LIKE 'INC%' THEN lpa.amount0_usd+lpa.amount1_usd ELSE 0 END) as mint,
SUM(CASE WHEN action LIKE 'DEC%' THEN lpa.amount0_usd+lpa.amount1_usd ELSE 0 END) as burn,
SUM(pcf.amount0_usd+pcf.amount1_usd) as collected_fees,
mint-burn as net_liquidity,
count(distinct lpa.pool_name) as pools
FROM uniswapv3.lp_actions lpa
INNER JOIN uniswapv3.position_collected_fees pcf on pcf.liquidity_provider = lpa.liquidity_provider
GROUP BY lpa.liquidity_provider
HAVING net_liquidity > 0
ORDER BY net_liquidity DESC),
y as (
SELECT
liquidity_provider,
sum(fee_usd) as gas_fees
FROM uniswapv3.lp_actions l
INNER JOIN ethereum.transactions t on t.tx_id = l.tx_id
GROUP BY liquidity_provider
ORDER BY gas_fees
)
SELECT
x.liquidity_provider,
ROUND(net_liquidity,2) as liquidity_provided_usd,
ROUND(collected_fees,2) as collected_fees,
ROUND(gas_fees,2) as gas_fees,
pools
FROM x
INNER JOIN y on x.liquidity_provider = y.liquidity_provider
HAVING liquidity_provided_usd > 0
ORDER BY liquidity_provided_usd DESC