copytraders.sql

script.sql

WITH 
last_week_trades AS (
    SELECT * from SANTIMENT__HYPERLIQUID.DEX.TRADES where timestamp > current_timestamp() - INTERVAL '10 DAY'
),
parsed_trades AS (
  SELECT
    TIMESTAMP,
    COIN,
    MARKET_TYPE,
    BUYER_ADDRESS AS address,
    PARSE_JSON(_EXTRA_FIELDS):buyer:dir::STRING AS dir,
    'buyer' AS role
  FROM last_week_trades
  WHERE PARSE_JSON(_EXTRA_FIELDS):buyer:dir IS NOT NULL

  UNION ALL

  SELECT
    TIMESTAMP,
    COIN,
    MARKET_TYPE,
    SELLER_ADDRESS AS address,
    PARSE_JSON(_EXTRA_FIELDS):seller:dir::STRING AS dir,
    'seller' AS role
  FROM last_week_trades
  WHERE PARSE_JSON(_EXTRA_FIELDS):seller:dir IS NOT NULL
),

-- Add week identifier for grouping
trades_with_week AS (
  SELECT
    *,
    DATE_TRUNC('week', TIMESTAMP) AS trade_week
  FROM parsed_trades
),

-- Self-join to find potential copy events
copy_candidates AS (
  SELECT
    t1.address AS leader,
    t2.address AS follower,
    t1.trade_week,
    t1.COIN,
    t1.MARKET_TYPE,
    t1.dir,
    t1.TIMESTAMP AS leader_time,
    t2.TIMESTAMP AS follower_time,
    DATEDIFF('minute', t1.TIMESTAMP, t2.TIMESTAMP) AS time_diff_minutes
  FROM trades_with_week t1
  JOIN trades_with_week t2
    ON t1.COIN = t2.COIN
    AND t1.MARKET_TYPE = t2.MARKET_TYPE
    AND t1.dir = t2.dir
    AND t1.trade_week = t2.trade_week
    AND t1.address != t2.address
    AND t2.TIMESTAMP > t1.TIMESTAMP
    AND DATEDIFF('minute', t1.TIMESTAMP, t2.TIMESTAMP) BETWEEN 0 AND 5
),

-- Count copy events per (leader, follower, week)
follower_summary AS (
  SELECT
    leader,
    follower,
    trade_week,
    COUNT(*) AS copy_count
  FROM copy_candidates
  GROUP BY leader, follower, trade_week
  HAVING COUNT(*) >= 6
)

-- Final output: distinct followers and their leaders
SELECT DISTINCT
  follower AS copytrader_address,
  leader AS leader_address,
  trade_week,
  copy_count
FROM follower_summary
ORDER BY trade_week DESC, copy_count DESC;