📂Trader P&L

Catalyst Profiteers

This SQL attempts to see who is profiting from any given events.

In this example, the token is CHZ, which pumped as the World Cup approaches.

CHZ Token Price Performance

Question:

Which addresses “profited” the most between 27 June - Nov 01?

SQL Code:

--------------------------------------------------------------
-- top profit (or roi) on CHZ at end of given time range
-- roi calculation based on USD spent within time range
    -- but realised profit could originate from purchases before time range
--------------------------------------------------------------
SELECT
    trader,
    SUM(bought_usd) AS invested,
    SUM(block_hour_profit - block_hour_fees_usd) AS abs_profit,
    SAFE_DIVIDE(SUM(block_hour_profit - block_hour_fees_usd),SUM(bought_usd))*100 AS pct_roi
FROM
    `nansen-query.aggregates_ethereum.dex_trader_stats_by_hour_token`
WHERE
    DATE(block_hour) BETWEEN DATE("2022-06-27") AND DATE("2022-11-01")
    AND token = "0x3506424f91fd33084466f402d5d97f05f8e3b4af"--chz
GROUP BY trader
ORDER BY abs_profit DESC --  ORDER BY pct_roi DESC
--------------------------------------------------------------

--------------------------------------------------------------
-- Top ROI adjusted by amount spent
-- roi calculation based on USD spent within time range
    -- but realised profit could originate from purchases before time range
--------------------------------------------------------------
SELECT
    trader,
    SUM(bought_usd) AS invested,
    SUM(block_hour_profit - block_hour_fees_usd) AS abs_profit,
    SAFE_DIVIDE(
        SUM(block_hour_profit - block_hour_fees_usd),
        SUM(bought_usd) * SAFE_DIVIDE(SUM(valid_sold_token),SUM(bought_token))
    )*100 AS sale_adjusted_pct_roi
FROM
    `nansen-query.aggregates_ethereum.dex_trader_stats_by_hour_token`
WHERE
    DATE(block_hour) BETWEEN DATE("2022-06-27") AND DATE("2022-11-01")
    AND token = "0x3506424f91fd33084466f402d5d97f05f8e3b4af"--chz
GROUP BY trader
ORDER BY sale_adjusted_pct_roi DESC
--------------------------------------------------------------

--------------------------------------------------------------
-- max achieved profit on CHZ in given time range (but by nov 1 might be lower)
--------------------------------------------------------------
WITH profits AS (
    SELECT
        block_hour,
        trader,
        SUM(block_hour_profit - block_hour_fees_usd) OVER (
            PARTITION BY trader
            ORDER BY block_hour ASC
        ) AS cumulative_profit_at_block_hour
    FROM
        `nansen-query.aggregates_ethereum.dex_trader_stats_by_hour_token`
    WHERE
        DATE(block_hour) BETWEEN DATE("2022-06-27") AND DATE("2022-11-01")
        AND token = "0x3506424f91fd33084466f402d5d97f05f8e3b4af"--chz
)

SELECT *
FROM profits
WHERE cumulative_profit_at_block_hour = (
    SELECT MAX(cumulative_profit_at_block_hour)
    FROM profits
  )
--------------------------------------------------------------

--------------------------------------------------------------
-- top volume on CHZ in given time range
--------------------------------------------------------------
SELECT
    trader,
    SUM(bought_usd) + SUM(sold_usd) AS volume
FROM
    `nansen-query.aggregates_ethereum.dex_trader_stats_by_hour_token`
WHERE
    DATE(block_hour) BETWEEN DATE("2022-06-27") AND DATE("2022-11-01")
    AND token = "0x3506424f91fd33084466f402d5d97f05f8e3b4af"--chz
GROUP BY trader
ORDER BY volume DESC
LIMIT 1
--------------------------------------------------------------

Token Leaders

Question

For all traders of $LDO - which addresses have profited the most?

SQL Code:

--------------------------------------------------------------
-- highest LDO pnl at current time
--------------------------------------------------------------
WITH latest_trade AS (
  SELECT
    trader,
    MAX(block_hour) AS block_hour
  FROM
      `nansen-query.aggregates_ethereum.dex_trader_stats_by_hour_token`
  WHERE
      token = "0x5a98fcbea516cf06857215779fd812ca3bef1b32"--LDO
  GROUP BY trader
)

SELECT
    trader,
    total_profit
FROM
    `nansen-query.aggregates_ethereum.dex_trader_stats_by_hour_token`
    INNER JOIN latest_trade USING(trader, block_hour)
WHERE
    token = "0x5a98fcbea516cf06857215779fd812ca3bef1b32"--LDO
ORDER BY total_profit DESC
LIMIT 1
--------------------------------------------------------------

--------------------------------------------------------------
-- max achieved LDO pnl (current could be lower if there's more recent trades)
--------------------------------------------------------------
SELECT
    trader,
    total_profit
FROM
    `nansen-query.aggregates_ethereum.dex_trader_stats_by_hour_token`
WHERE
    token = "0x5a98fcbea516cf06857215779fd812ca3bef1b32"--LDO
ORDER BY total_profit DESC
LIMIT 1
--------------------------------------------------------------