Querying LP distribution using Dune

For some time I look at V3 LP Range Distribution as an indicator of market sentiment about a specific asset. Here I made a tutorial teaching how to do dashboard following LP ranges.

This article is meant to help other users create LP distribution liquidity queries for any tokens. I’ve decided to create a Dune dashboard after finding out that V3 LP Range Distribution could be a good indicator of market sentiment about a specific asset. If you’re curious about the dashboard itself, you can check it using the URL in the image below and also look at any query. Everything is open source. Dune has made it very easy to design and plot any Ethereum data.

https://duneanalytics.com/yulesa/Liquidity-Distribution

Uniswap V3 concentrated liquidity

Uniswap v3 introduced the concept of concentrated liquidity. Instead of depositing liquidity for a pair across the entire price interval (0, ∞), liquidity providers (LP) can select a custom range of price in which his liquidity will be available for trades.

To achieve concentrated liquidity, the once continuous spectrum of price space has been partitioned with ticks. Ticks are discrete price intervals that each user can provide liquidity. Each tick represents a 0.0001% increase or decrease in price at any point in price space. To create a position, an LP chooses the lower and upper tick that will represent their position’s borders. More information on Uniswap V3 architecture can be in their documentation.

One import metric of the Uniswap market is how the combined liquidity of all LP is distributed across the price space. Uniswap.info is a starting point to get this data. The problem is that pairs with higher volume are paired to ETH, so it’s hard to see how LPs set their ranges related to USD. Also, it’s hard to quantify how much liquidity there is in one direction compared to the other.

LINK/ETH 0.3% Pair Liquidity Distribution - Source: https://info.uniswap.org/#/pools/0xa6cc3c2531fdaa6ae1a3ca84c2855806728693e8 21–06–23

Tutorial

To make your graph of liquidity distribution, we pick Dune Analytics because it already has all the data we need decoded into tables. The objective is, given a specific pool, we are going to create a table with all ticks and sum the liquidity that each LP deposited into. With this table, we can create additional indicators, like the sum of liquidity between two price intervals.

https://duneanalytics.com/queries/65056/129925

Depositing and withdrawing liquidity from a pool are functions of “UniswapV3Pool” contract. To deposit liquidity LP use the function “mint”; thus, we can use the table uniswap_v3.“Pair_call_mint” on Dune. To withdraw, the function called is “burn”, and the related table uniswap_v3.“Pair_call_burn.

A successful call of this function also triggers an Ethereum event. Dune also has tables to track all events in Ethereum for each contract. For Uniswap V3 the tables are uniswap_v3.“Pair_evt_Mint” and uniswap_v3.“Pair_evt_Burn”. We opted for the call tables in this tutorial because event tables weren’t handling negative numbers properly. Tip: Events are straightforward to track on Etherscan. You can check them on the log tab of a transaction.

Example of burn event log on Etherscan.

Let’s start with the code:

WITH mb AS(
 SELECT                       --get all mint calls
  "tickLower" AS lowerTick,   --range lower limit
  "tickUpper" AS UpperTick,   --range upper limit
  amount                     --Liquidity added to each tick
 FROM uniswap_v3."Pair_call_mint"
 WHERE call_success = true       --exclude fail calls
 AND contract_address =  '\xa6cc3c2531fdaa6ae1a3ca84c2855806728693e8'
          
 UNION ALL
          
 SELECT                        -- same to burn liquidity calls
  "tickLower" AS lowerTick,
  "tickUpper" AS UpperTick,
  -amount AS amount
 FROM uniswap_v3."Pair_call_burn"
 WHERE call_success = true
 AND contract_address = '\xa6cc3c2531fdaa6ae1a3ca84c2855806728693e8'
)

First, we’re querying the tables for the mint and burn calls and selecting the columns for “tickLower”, “tickUpper” and amount. We filter out unsuccessful transactions and filter only the pool address we are interested in. Notice that we are subtracting “amount” when using the “burn” table to remove this liquidity from the interval.

In order to find the pool address you want, a good approach is to search at Uniswap.info and look at the address on the URL.

WITH mint_burn AS(
 SELECT          -- sum liquidity for exact same range, added and removed liquidity will add to zero.
  lowerTick,
  UpperTick,
  SUM(amount) as amount
 FROM mb
 GROUP BY 1,2
)

Next, we sum liquidity for exact same range. All liquidity that was added and then removed will add to zero.

WITH ticks AS(
 SELECT    --for each range, create a series of ticks
  generate_series(lowerTick, UpperTick, 5) as tick,
  amount AS amount_tick
 FROM mint_burn
 WHERE amount > 0
)

Now, for each lowertick-uppertick interval, we generate a series and distribute the amount for each tick. We filter away any tick interval with a zero amount liquidity. If you need it, here is a good generate series documentation.

There is an additional detail here. Uniswap V3 doesn’t initialize every tick. The pool is instantiated with a parameter, tickSpacing; only ticks with indexes that are divisible by tickSpacing can be initialized. The tick spacings are dependable on the pool fee tier. 0.05% fee pools have a tick spacing of 10, 0.30% a tick spacing of 60, and 1% a tick spacing of 200. This means we could further increase the step/interval parameter of the generate_series function, but to make the graph sharper and also optimize this function we choose a 5 step.

distribution as (
 SELECT 
  1/(1.0001^tick) AS price,
  --tick,                     --this is here to help you later
  SUM(amount_tick) as total,  --add all liquidity for the same tick
  'liq' AS series              --auxiliary column to filter graph
 FROM ticks
 GROUP BY 1
)

With all the series in the same table, we add all liquidity for the same tick and convert tick and price. The formula to convert tick to price is:

This will give us the price in terms of token0/token1. Most of the time, token0 is ETH and what we want is to show the price in USD terms. To do this, we multiply the price above by the current price of ETH.

cp AS (   -- auxiliary table to get current price for the token
 SELECT
  --minute,
  Price,
  --decimal
  symbol
 FROM prices."usd"
 WHERE symbol = 'WETH' OR symbol = 'LINK' 
 ORDER BY minute DESC
 LIMIT 2
),

SELECT 
    cp.price*d.price AS price,
    total,
    series
FROM distribution d
LEFT JOIN cp ON cp.symbol = 'WETH'

prices.“usd” is a table with the historical prices of all tokens from an external source. We filter the most recent prices of both tokens we are interested in. We are using ETH and LINK to illustrate this tutorial.

Lastly, we want to add to our graph where the current token price is. To do this, we add to this last table one extra row with the current price and new series identification.

UNION ALL        --Add an extra line to the table with the current price
SELECT 
 price,          --current price
 1e10 AS total,  --arbitrary number (end up showing a white line in the graph)
 'current' AS series --auxiliary column to filter graph
FROM cp
WHERE symbol = 'LINK'--Get only LINK price

Plotting the result table, we get:

This plot included all ticks with liquidity but, to better present the data, we are more interested in the region with concentrated liquidity. We can make a better plot by filtering the distribution table with a “WHERE tick BETWEEN lower_limit AND upper_limit”. A trick to find this interval is to create a table with both price and tick. Uncomment the tick column at the distribution table (you will need to comment the current token price UNION at the end). Now, find the price-tick you want to filter and then return everything like before, adding the WHERE clause.

The final result is:

I would like to thank danner.eth for the feedback and review.