🗃️Advanced Topics

Profit Loss Over Custom Time Periods

Currently, the dex_trader_stats_30d_by_hour_token table into consideration all trades an address have made in its life time

There 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.

How to Estimate Unrealized P&L

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.

How to Calculate the P&L Manually?

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)