🗃
Advanced Topics
Currently, the
dex_trader_stats_30d_by_hour_token table into consideration all trades an address have made in its life timeThere is another table,
dex_trader_stats_30d_by_hour_token, which only looks at balances opened in the last 30 days, on a rolling basis.For custom time windows, please contact us for additional instructions.
Each row has an
end_valid_balance field which is the remaining balance of that token after that trade (counting only tokens purchased on DEXs from the start of the table time range).1) For each token, take the
end_valid_balance from the row with most recent block_hour (the latest trade)2) multiply that by the current token price
Note as mentioned in a previous section, because that table only tracks DEX sales and purchases, if those tokens are transferred out/ used to buy an nft/staked/used as collateral/... that is not reflected in
end_valid_balance. So you might have someone with high unrealized PnL when their wallet is actually empty.An idea for a better estimate is to calculate their actual current token balance and pick the lowest between that and
end_valid_balance before multiplying by current price.You can use the following SQL to validate the correctness of the P&L calculation.
WITH
profit_including_cost_of_unsold_tokens AS
(
SELECT
trader,
token,
SUM(valid_revenue_usd - cost_usd - block_hour_fees_usd) AS total_profit_without_refund
FROM
nansen-query.aggregates_ethereum.dex_trader_stats_by_hour_token
GROUP BY
trader,
token
),
latest_refund_for_unsold_tokens AS (
SELECT DISTINCT
trader,
token,
FIRST_VALUE(total_refund) OVER(
PARTITION BY trader, token
ORDER BY block_hour DESC
) AS unsold_tokens_refund,
FIRST_VALUE(total_profit) OVER(
PARTITION BY trader, token
ORDER BY block_hour DESC
) AS total_profit_from_table
FROM
nansen-query.aggregates_ethereum.dex_trader_stats_by_hour_token
)
SELECT
trader,
token,
(total_profit_without_refund + unsold_tokens_refund) AS total_profit_we_calculated,
total_profit_from_table
FROM
profit_including_cost_of_unsold_tokens
INNER JOIN
latest_refund_for_unsold_tokens
USING(trader,token)
Last modified 1mo ago