Data Science Example: Uniswap Liquidity Pool Trade-Offs

Dex Swaps

Each dex swap table records the amounts of tokens being swapped in and out of a liquidity pool, the amount in dollars ($), as well the effective price (amount_out/amount_in) normalized by the decimals of the different tokens.

Liquidity Pool Analysis

In this example we’ll look at a data-sciency example, in which we look at the number one poll address in terms of volume, WBTC/ETH (as of 11th January), and analyze different aspects of the trading that has taken place in the last 30 days across both fee bands available on Uniswap v3 (0.05% and 0.3%).

📘

Find the dashboard version of the above query here.

What we can see, in the overall stats of the last 30 days is that even though the count of trades is higher for the 0.05% pool, the fees accrued and the average trade size is bigger for the 0.3% pool. This also matches with the fact that the 0.3% pool has much higher TVL (3.33x more TVL) than the 0.05% pool. Indicating first, that it’s more profitable for Liquidity providers to provide liquidity for the 0.3% pool, regardless of the fact that the 0.05% pool has higher trade count, but also it indicates that when swapping large amounts of ETH for WBTC or vice versa the trades are routed through the 0.3% because of the higher TVL and better coverage across ticks, but that also users are fine to pay the 0.3% fee to get better slippage on the trade.

To rerun the same analysis for an up-to-date result or use the similar code to check a different pair address combination please use the SQL code below:

WITH prices_usd as (
SELECT
*
FROM prices.usd as p
WHERE p.data_creation_date >= CURRENT_DATE - interval '30' day 
AND p.symbol='ETH'
)
SELECT
  fee,
  round(SUM(amount_usd),2) as volume_usd,
  round(SUM(amount_usd) * (fee*0.01),2) as fees_accrued_usd,
  round(SUM(amount_usd)/COUNT(*),2) as avg_volume_usd_per_trade,
  round(SUM(amount_usd) * (fee*0.01)/COUNT(*),2) as avg_fees_accrued_usd_per_trade,
  COUNT(*) as ct
from
(
SELECT
  CASE WHEN token_in_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN coalesce(pp.price/effective_price,0)
  ELSE coalesce(pp.price/(1/effective_price),0) END as price
  ,pp.price as  price_usd
  ,p.fee
  ,s.*
FROM dex.ethereum_dex_swaps as s
  LEFT JOIN dex.ethereum_dex_pools as p
  ON s.pair_address=p.pair_address
  LEFT JOIN prices_usd as pp
  ON date_trunc('minute', s.block_time)=pp.minute
where s.data_creation_date >= CURRENT_DATE - interval '30' day
AND s.pair_address in ('0xcbcdf9626bc03e24f779434178a73a0b4bad62ed','0x4585fe77225b41b697c938b018e2ac67ac5a20c0')
AND p.pair_address in ('0xcbcdf9626bc03e24f779434178a73a0b4bad62ed','0x4585fe77225b41b697c938b018e2ac67ac5a20c0')
)
GROUP BY 1