Token holders of $X also hold $Y

This showcases relations between token holders. Given a token $X, which other tokens are the holders of $X holding?

We dive deeper into this through a Dune query below. The process is straight-forward, although compute heavy. We query all the token holders for a specified token, and then proceed to re-build their ERC-20 token balances based on all of their historical ERC-20 token Transfer events. And lastly, we sort by number of token holders.

WITH transfers AS (
  SELECT
    "from" AS address,
    "from",
    "to",
    contract_address,
    value
  FROM erc20_ethereum.evt_Transfer evt
  WHERE contract_address = {{token}}

  UNION ALL

  SELECT
    "to" AS address,
    "from",
    "to",
    contract_address,
    value
  FROM erc20_ethereum.evt_Transfer evt
  WHERE contract_address = {{token}}
),
token_holders AS (
  SELECT
    tf.address,
    tf.contract_address,
    SUM(
      CASE
        WHEN "to" = tf.address THEN CAST(tf.value AS DOUBLE) / POW(10, COALESCE(t.decimals, 18))
        WHEN "from" = tf.address THEN -CAST(tf.value AS DOUBLE) / POW(10, COALESCE(t.decimals, 18))
        ELSE CAST(0 AS DOUBLE)
      END
    ) AS balance
  FROM transfers tf
  LEFT JOIN tokens.erc20 t ON t.contract_address = tf.contract_address
  GROUP BY tf.address, tf.contract_address, t.symbol
),
holders AS (
  SELECT DISTINCT address
  FROM token_holders
  WHERE balance > CAST(1e-4 AS DOUBLE)
),
all_transfers AS (
  SELECT *
  FROM erc20_ethereum.evt_Transfer t
  INNER JOIN holders h ON t."from" = h.address

  UNION ALL

  SELECT *
  FROM erc20_ethereum.evt_Transfer t
  INNER JOIN holders h ON t."to" = h.address
),
final_filtered_holders AS (
  SELECT
    address,
    COUNT(*) AS count
  FROM all_transfers
  GROUP BY address
  HAVING COUNT(*) < 30000
),
filtered_transfers AS (
  SELECT *
  FROM erc20_ethereum.evt_Transfer t
  INNER JOIN final_filtered_holders h ON t."from" = h.address

  UNION ALL

  SELECT *
  FROM erc20_ethereum.evt_Transfer t
  INNER JOIN final_filtered_holders h ON t."to" = h.address
),
filtered_transfers_ordered AS (
  SELECT *
  FROM filtered_transfers
  ORDER BY evt_block_number ASC, evt_index ASC
),
balances AS (
  SELECT
    fto.address,
    fto.contract_address,
    t.symbol,
    SUM(
      CASE
        WHEN fto."to" = fto.address THEN CAST(fto.value AS DOUBLE) / POW(10, COALESCE(t.decimals, 18))
        WHEN fto."from" = fto.address THEN -CAST(fto.value AS DOUBLE) / POW(10, COALESCE(t.decimals, 18))
        ELSE CAST(0 AS DOUBLE)
      END
    ) AS balance
  FROM filtered_transfers_ordered fto
  LEFT JOIN tokens.erc20 t ON t.contract_address = fto.contract_address
  GROUP BY fto.address, fto.contract_address, t.symbol
),
balances_filtered AS (
  SELECT *
  FROM balances
  WHERE balance > CAST(0 AS DOUBLE)
),
token_correlations AS (
  SELECT
    contract_address,
    symbol,
    COUNT(*) AS count,
    SUM(balance) AS cumulative_balance
  FROM balances_filtered
  GROUP BY contract_address, symbol
),
main_correlation AS (
  SELECT count
  FROM token_correlations
  WHERE contract_address = {{token}}
  LIMIT 1
),
correlations AS (
  SELECT
    tc.*,
    CAST(tc.count AS DOUBLE) / CAST(mc.count AS DOUBLE) AS correlation
  FROM token_correlations tc, main_correlation mc
),
spam_tokens AS (
  SELECT contract_address
  FROM (
    VALUES
      (0x471c3a7f132bc94938516cb2bf6f02c7521d2797),
      (0x0d3716e3e411af431a6e87e715d4b05bbcd67000),
      (0x82dfdb2ec1aa6003ed4acba663403d7c2127ff67),
      (0x8053aff5111eaeabaa32f06242825c70c939e4f7),
      (0x20a21170d3bf388cbe2ac34643b5180a26e68331),
      (0x388bc6728b3cab9e93bd79105d6ecb3db8431918),
      (0x34278f6f40079eae344cbac61a764bcf85afc949),
      (0x1883a07c429e84aca23b041c357e1d21a2b645f3),
      (0xd88e87cd53d5b3c88c07bdb4715a39b75d6e7870),
      (0x1412eca9dc7daef60451e3155bb8dbf9da349933),
      (0x68ca006db91312cd60a2238ce775be5f9f738bba),
      (0xc1c8c49b0405f6cffba5351179befb2d8a2c776c),
      (0x67542502245eb5df64ef7ea776199ceb79401058),
      (0x54fd62228c6e1234fd5fded28555ca963dcf6d26),
      (0x2d262c7faa8e03a86d6634bdc6178b3e06cd690b),
      (0x643695d282f6ba237afe27ffe0acd89a86b50d3e),
      (0x956f824b5a37673c6fc4a6904186cb3ba499349b),
      (0xcf39b7793512f03f2893c16459fd72e65d2ed00c),
      (0x265befe2b1a0f4f646dea96ba09c1656b74bda91),
      (0xb688d06d858e092ebb145394a1ba08c7a10e1f56),
      (0xe89c2dccfa045f3538ae3ebbfff3daec6a40a73a),
      (0x2f87445c3c81a5b268adab1915235a94aa27bf54),
      (0x5d80a8d8cb80696073e82407968600a37e1dd780),
      (0x27ed436e67515c1dd33633537eebd59207c51806),
      (0x3ed525fa1f10889dbf55515d1a436d91680d6c54),
      (0x120aa018634f555484c088c8da80f75aa07e004f),
      (0x622651529bda465277cb890ef9176c442f42b338),
      (0x7a6b87d7a874fce4c2d923b09c0e09e4936bcf57),
      (0xad9f583f255ea7fba4321f84cba57c3bbd2f0687)
  ) AS temp_table(contract_address)
)
SELECT
  c.*
FROM correlations c
LEFT JOIN spam_tokens s ON c.contract_address = s.contract_address
WHERE s.contract_address IS NULL
ORDER BY c.count DESC
LIMIT {{_limit}};

An example below where we run the query on the $CULT token, and include the top 40 tokens that are held by the $CULT token holders.

In the bar chart below we see $USDC, $WETH, $USDT—but also tokens such as $MOG, $SPX and $PEPE. A correlation of 0.3 implies 30% of the $CULT holders hold the token, and the number on top shows the total holder count.