📂
Trader P&L
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
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
--------------------------------------------------------------
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
--------------------------------------------------------------
Last modified 3mo ago