Calculating slippage on Uniswap v3, with SQL & Tableau! part 1

Slippage data on Uniswap pools can be accessed directly through Uniswap Subgraphs if you know how to work with Graphql and you know basics of programming. The advantage is that a lot of heavy lifting has already been done upstream and you just get the time series for your desired data point. The disadvantage, though, is that you are limited to what the subgraph has to offer.

In this post, I’ll show you how to do that heavy lifting yourself, using SQL, so that you can do many different analyses which I talk about in part two. The heavy lifting is basically nothing but building the liquidity distribution data set.

As a basis for comparing your results, for most of pools, the liquidity distribution is available on uniswap analytics website. But fret not if your pool doesn’t show up on the site! we’re going to re-construct this chart anyway.

The pool we’re reconstructing in this post is the GNS/MATIC pool on polygon with the following address: 0xEFa98Fdf168f372E5e9e9b910FcDfd65856f3986

And we’re lucky and we can see it on the website:

Steps to follow:

1- Unfortunately Dune analytics, and non of the other data providers for that matter, doesn’t have pool liquidity distribution data sets ready, probably due to the cost of building them for each pool. But the closest thing we can lay hands on is the current active positions of all liquidity providers. This is available in the following two event tables on Dune. (Reminder: Each liquidity position is ascribed to an NFT, thus its unique and traceable)

WITH mb as (
    select
      tickLower as lowerTick --
,
      tickUpper as upperTick --
,
      cast(amount as double) / sqrt(power(10, 18 + 18)) as amount
    from
      uniswap_v3_polygon.UniswapV3Pool_evt_Mint
          where
      contract_address = 0xEFa98Fdf168f372E5e9e9b910FcDfd65856f3986
    union all
    select
      tickLower as lowerTick --
,
      tickUpper as upperTick --
, 
      -1 * cast(amount as double) / sqrt(power(10, 18 + 18)) as amount
    from
    uniswap_v3_polygon.UniswapV3Pool_evt_Burn
        where
      contract_address = 0xEFa98Fdf168f372E5e9e9b910FcDfd65856f3986
  ) --
,
  mint_burn as (
    select
      lowerTick 
,
      upperTick
,
      sum(amount) as amount
    from
      mb
    group by
      1,
      2
  )

2- Construct pool’s liquidity at each tick from position’s liquidity

So far we have liquidity per position. As you know, liquidity providers often spread their money on a range of ticks, especially on pools with volatile assets. For example, if we had only 3 positions in a pool with a tick spacing of 10 as below,

The constructed pool’s liquidity distribution would look like this:

The above example was a simple example that involved only a position on one asset. In reality, a position can have 2 assets in it. That’s why you see amount0 and amount1 in that table. We’re using the column “amount” that is a mix of both quantities in a format that is very hard to understand. But we will worry about making liquidity human readable in step 6. The following lines will essentially do the job of turning position liquidity data to pool liquidity for our polygon pool.

 sequence_parts AS (
    SELECT
      CEIL((upperTick - lowerTick + 1) / (99 * 60)) AS num_parts,-- 99 is completely arbitrary. we're using it due to limitations that trino has in generating series. So we have to reduce the size of our sequence
      lowerTick,
      upperTick,
      amount AS amount_tick
    FROM
      mint_burn
    WHERE
      amount > 0
  ),
  generated_sequences AS (
    SELECT
      seq_part,
      lowerTick + seq_part * 99 * 60 AS start_tick, -- Here we're adding back the 99 factor that we used above tactically. we're using it due to limitations that trino has in generating series. So we have to reduce the size of our sequence
      least(upperTick, lowerTick + (seq_part + 1) * 99 * 60 - 60) AS end_tick, -- Here we're adding back the 99 factor that we used above tactically. we're using it due to limitations that trino have in generating series. So we have to reduce the size of our sequence
      amount_tick
    FROM
      sequence_parts
      CROSS JOIN UNNEST(SEQUENCE(0, cast(num_parts as int), 1)) AS t(seq_part)
  ),
  nest_ticks AS (
    SELECT
      sequence(start_tick, end_tick, 60) AS tick,
      amount_tick
    FROM
      generated_sequences
  ),
  ticks AS (
    SELECT
      ticks.tick,
      amount_tick
    FROM
      nest_ticks
      CROSS JOIN UNNEST(tick) AS ticks (tick)
  )

3- Get the current tick

Now that we have the distribution of pool liquidity per tick, its important to visualize which tick represents the current price. This information is usually available in every swap event. We just need to get the most recent one.

    select
      tick as currentTick
    from
      uniswap_v3_polygon.UniswapV3Pool_evt_Swap
              where
      contract_address = 0xEFa98Fdf168f372E5e9e9b910FcDfd65856f3986
    order by
      evt_block_time desc
    limit
      1

4- So far we have liquidity distribution per price tick. But price ticks are not really understandable to us as humans. We are more used to seeing the price in terms of GNS, MATIC, etc., rather than ticks. The following part will take care of it.

  ld as (
    SELECT
      -- The most difficult part is doing tick to price correlation.
      -- Formula is 1.0001^(tick)* 1e(number of decimals in token0)/1e(numberof_decimals in token1)
      -- Invert for token1/token0 price
      power(1.0001, t.tick) * power(10, 18 - 18) AS price, -- both tokens have 18 decimals
      SUM(amount_tick) AS total,
      'liq' AS series
    FROM
      ticks t,
      ct c
    WHERE
      (
        CASE
          WHEN ({{rangeMinToken0PriceInToken1}}) != 0 THEN power(1.0001, tick) * power(10, 18 - 18) > {{rangeMinToken0PriceInToken1}}
          ELSE tick > c.currentTick - 100 * 60
        END
      )
      AND (
        CASE
          WHEN ({{rangeMaxToken0PriceInToken1}}) != 0 THEN power(1.0001, tick) * power(10, 18 - 18) < {{rangeMaxToken0PriceInToken1}}
          ELSE tick < c.currentTick + 100 * 60
        END
      )
    GROUP BY
      1
  ) -- don't worry about those parameters in {{}}. Those are just for visualization improvement on Dune. Default value could be 0
,
  current_price as (
    SELECT
      power(1.0001, ct.currentTick) * power(10, 18 - 18) AS price,  -- both tokens have 18 decimals
      ld.total,
      'cp' AS series --auxiliary colunm to filter graph
    FROM
      ld,
      ct
    WHERE
      ld.price < power(1.0001, ct.currentTick + 60) * power(10, 18 - 18)
      AND power(1.0001, ct.currentTick) * power(10, 18 - 18) <= ld.price
    LIMIT
      1
  ),

5- We’re pretty much done. Now its all about putting it all together in a final select statement:

final as (
SELECT
  price,
  1 / price as inv_price,
  total,
  series
from
  ld
UNION ALL
SELECT
  price,
  1 / price as inv_price,
  total,
  series
from
  current_price )
  
 select 
 a.price, --This is the price of MATIC per GNS
 a.inv_price, -- This is the price of GNS per MATIC
 a.total,  -- This is the liquidity 
 a.series, -- This one is just for visualizing the current price
 sum(a.total)over(order by a.inv_price) as cumul_total, -- this is the running sum of liquidity
 sum(a.total)over(order by a.inv_price)/sum(a.total)over() as cumul_total_pcg, -- This is the % of cumulative liquidity over total liquidity
 from 
 final a

The final select statement gives you what you need for visualizing the result that looks like this:

6- We made the price ticks human readable, but we have yet to do this for liquidity, which is the Y axis. In part two, we will take care of that while doing the analysis on price impact.

The complete SQL query is available here. You are free to re-use it.

Part 2