Home

Finding Trending New Pairs

Find new trending liquidity pool contracts.

Suggest Edits

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.

Finding Trending Pairs

In this example we’ll look at a more advanced example of looking at trending liquidity pool contracts. The advanced component comes in when using a heuristic to filter out for high volume to total volume ratio by wallet, which usually indicates that a specific pair is dominated by a specific market maker.

📘

Find the query here.

What we see is that, in the last 3 days, pairs such sa MAG-WETH and PORK-WETH have been actively traded with significant volume, also without a major market maker for PORK-WETH, with MAX(volume(by wallet)/total_volume) ratio of 0.0542. Meaning that PORK-WETH might be something degen traders are genuinely excited about and something one could look into.

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
  dex_swaps_metrics AS (
    SELECT
      SWAPS.pair_address,
      SWAPS.from_address_tx AS wallet,
      pools.pair_symbol,
      pools.token_1 AS token_1,
      pools.token_2 AS token_2,
      pools.symbol_1 AS symbol_1,
      pools.symbol_2 AS symbol_2,
      SUM(SWAPS.amount_usd) AS volume_usd
    FROM
      dex.ethereum_dex_swaps AS SWAPS
      LEFT JOIN dex.ethereum_dex_pools AS pools ON SWAPS.pair_address = pools.pair_address
    WHERE
      SWAPS.data_creation_date >= CURRENT_DATE - INTERVAL '3' DAY
    AND swaps.amount_usd is not null
    GROUP BY
      1,2,3,4,5,6,7
  ),
  top_contributors AS (
    SELECT
      *,
      SUM(volume_usd) OVER (PARTITION BY pair_address) AS total_volume,
      CAST(volume_usd AS DOUBLE) / CAST(
        SUM(volume_usd) OVER (PARTITION BY pair_address) AS DOUBLE
      ) AS volume_contribution_ratio
    FROM
      dex_swaps_metrics
  ), possible_market_makers AS (
    SELECT
      DISTINCT pair_address
    FROM
      top_contributors
    WHERE
      volume_contribution_ratio >= 0.65 -- set your own threshold here
  )
SELECT
  pair_address,
  pair_symbol,
  token_1,
  token_2,
  symbol_1,
  symbol_2,
  ROUND(total_volume,2) as total_volume_usd,
  MAX(ROUND(volume_contribution_ratio,4)) as max_volume_contribution_ratio
FROM
  top_contributors
WHERE
  pair_address NOT IN (
    SELECT
      pair_address
    FROM
      possible_market_makers
  )
AND total_volume < 100000000 -- volume less than (set your threshold here)
GROUP BY 1,2,3,4,5,6,7
ORDER BY
  total_volume_usd DESC

Updated about 2 months ago