Home
Documentation

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 9 months ago