​
Blockchain Coverage

We support PNL data for the following blockchains:
BlockchainBalances Model
EVM-Compatible BlockchainsThe crosschain.assets.fungible_pnl_evm model offers a historical record of fungible asset pnls at block-level granularity. Chain-specific tables are available at <chain>.assets.fungible_pnl.
SolanaThe solana.assets.pnl model offers a historical record of SOL and spl tokens (no NFTs) pnls at transaction-level granularity. Refer to here for more details.

​
PNL calculation

The PNL (Profit and Loss) calculation uses the average-cost methodology to determine realized and unrealized gains/losses for fungible assets. This approach provides a fair and consistent way to calculate performance across multiple transactions and time periods.

​
Average-Cost Methodology

The average-cost method calculates PNL by maintaining a running average of the cost basis for each asset position. The methodology tracks cumulative costs and quantities to compute accurate profit and loss metrics.

​
Key Fields

The PNL model provides several important fields for analysis:
  • average_cost: The current weighted average cost per token unit, calculated as cumulative_costs / cumulative_quantities
  • cumulative_costs: The total USD cost basis remaining after accounting for sales (reduces proportionally when tokens are sold)
  • cumulative_quantities: The remaining token quantity after accounting for sales
  • realized_pnl: The cumulative realized profit/loss from all sales transactions for this token
  • unrealized_pnl: The unrealized profit/loss based on the token price at the time of the transaction
  • unrealized_pnl_latest: The current unrealized profit/loss using the latest available token price

​
Core Principles

  1. Cost Basis Averaging: When acquiring additional tokens, the new average cost becomes the weighted average of previous holdings and the new acquisition.
  2. Proportional Cost Reduction: When disposing of tokens, both cumulative_costs and quantities are reduced proportionally, keeping the average_cost unchanged.
  3. Transaction-Level Tracking: The average cost basis and PNL metrics are updated with every transaction that changes the token balance.

​
Calculation Steps

​
1. Purchase Transactions (transaction_type: ‘purchase’ or ‘first_purchase’)

When tokens are acquired:
New Cumulative Costs = Previous Cumulative Costs + (Tokens Purchased × Token Price USD)
New Quantities = Previous Quantities + Tokens Purchased
New Average Cost = New Cumulative Costs / New Quantities
Example:
  • Existing: 100 ETH, cumulative_costs = 200,000,averagecost=200,000, average_cost = 2,000
  • New purchase: 50 ETH at 2,400=2,400 = 120,000
  • New cumulative_costs: 200,000+200,000 + 120,000 = $320,000
  • New quantities: 100 + 50 = 150 ETH
  • New average_cost: 320,000/150=320,000 / 150 = 2,133.33 per ETH

​
2. Sale Transactions (transaction_type: ‘sale’)

When tokens are sold:
Realized PNL This TX = Tokens Sold × (Sale Price - Average Cost Before Sale)
New Cumulative Costs = Previous Cumulative Costs - (Tokens Sold × Average Cost Before Sale)
New Quantities = Previous Quantities - Tokens Sold
Average Cost = New Cumulative Costs / New Quantities (unchanged from before sale)
Example:
  • Before sale: 150 ETH, cumulative_costs = 320,000,averagecost=320,000, average_cost = 2,133.33
  • Sell: 30 ETH at $2,500 per ETH
  • Realized PNL: 30 × (2,500−2,500 - 2,133.33) = $11,000.10 gain
  • New cumulative_costs: 320,000−(30×320,000 - (30 × 2,133.33) = $256,000.10
  • New quantities: 150 - 30 = 120 ETH
  • New average_cost: 256,000.10/120=256,000.10 / 120 = 2,133.33 per ETH (unchanged)

​
3. Unrealized PNL Calculations

At Transaction Time:
Unrealized PNL = Current Balance × (Token Price USD - Average Cost)
At Latest Price:
Unrealized PNL Latest = Current Balance × (Latest Token Price - Average Cost)
Example:
  • Current holdings: 120 ETH at average_cost of $2,133.33
  • Token price at transaction: $2,600
  • Latest token price: $2,800
  • Unrealized PNL (transaction time): 120 × (2,600−2,600 - 2,133.33) = $56,000.40
  • Unrealized PNL Latest: 120 × (2,800−2,800 - 2,133.33) = $80,000.40
This query shows how to get daily PNL snapshots for a wallet
with pnl as (
    select 
    *,
    -- take last record per second granularity (asof join can only compare by time)
    row_number() over (partition by chain, address, token_address, block_timestamp order by block_number desc) as rank_in_timestamp_secs,
    row_number() over (partition by chain, address, token_address order by block_timestamp, block_number) as global_rank,
    from crosschain.assets.fungible_pnl_evm
    where address in (
        lower('...')
    )
    
), time_axis as (
    select 
    
    d.DATE_TIMESTAMP::varchar as description,
    d.DATE_TIMESTAMP as activity_date,
    chain, address, token_address
    from common.utility.dates d
    cross join (select distinct chain, address, token_address from pnl)
    where d.DATE_TIMESTAMP >= (Select date(min(block_timestamp)) from pnl)
    and d.DATE_TIMESTAMP <= current_date + 1
),
anchors as (
    select
    time_axis.*,
    pnl.* exclude (chain, address, token_address)
    
    from time_axis
    asof join (select * from pnl where rank_in_timestamp_secs=1) pnl
        match_condition (time_axis.activity_date > pnl.block_timestamp)
        on pnl.address = time_axis.address
        and pnl.token_address = time_axis.token_address
        and pnl.chain = time_axis.chain
),
unioned as (
    select
    'first' as description, block_timestamp as activity_date, chain, address, token_address,
    pnl.* exclude (chain, address, token_address)
    from pnl where global_rank = 1

    union all

    select * from anchors
    
)
select * from unioned
where token_type is not null and balance is not null and usd_exchange_rate is not null
order by activity_date, chain, address, token_address
This query shows how to get aggregated PNL across multiple address and/or tokens for portfolio-level analysis.
with latest_pnl as (
    select 
    *
    from crosschain.assets.fungible_pnl_evm
    where address in (
        lower('...')
    )
    qualify row_number() over (partition by chain, address, token_address order by block_timestamp desc, block_number desc) = 1
), unioned as (
    -- Aggregated PNL across all tokens
    select 
    address,
    count(distinct token_address) as total_tokens_held,
    count(distinct chain) as chains_used,
    
    null as token_address,
    null as token_symbol,
    
    sum(realized_pnl) as total_realized_pnl,
    sum(unrealized_pnl_latest) as total_unrealized_pnl,
    sum(realized_pnl + unrealized_pnl_latest) as total_pnl,
    sum(usd_balance_latest) as total_portfolio_value,
    
    max(block_timestamp) as last_activity_timestamp
    
    from latest_pnl
    group by all
    
    union all
    
    -- Per-token PNL
    select 
    address || ' - ' || coalesce(token_symbol, 'Unknown') as address,
    1 as total_tokens_held,
    1 as chains_used,
    
    token_address,
    token_symbol,
    
    sum(realized_pnl) as total_realized_pnl,
    sum(unrealized_pnl_latest) as total_unrealized_pnl,
    sum(realized_pnl + unrealized_pnl_latest) as total_pnl,
    sum(usd_balance_latest) as total_portfolio_value,
    
    max(block_timestamp) as last_activity_timestamp
    
    from latest_pnl
    group by all
)
select * from unioned
order by address, token_address

​
Table Columns

Unique Key: chain, address, token_address, unique_id
Column NameDescription
chainThe EVM-compatible blockchain where the assets are held.
token_typeThe type of token (e.g., ‘native’, ‘erc20’).
addressThe wallet address holding the token.
token_addressThe contract address of the token. Native tokens are represented with zero address.
token_nameThe name of the token.
token_symbolThe symbol of the token.
balanceThe current token balance after this transaction.
prev_balanceThe token balance before this transaction.
balance_changeThe change in token balance from this transaction (balance - prev_balance). Can be negative.
usd_exchange_rateThe USD exchange rate for the token at the time of the transaction.
usd_balanceThe current token balance converted to USD at transaction time.
usd_balance_changeThe change in USD balance from this transaction. Can be negative.
usd_exchange_rate_latestThe latest available USD exchange rate for the token.
usd_balance_latestThe current token balance converted to USD using the latest exchange rate.
transaction_typeThe type of transaction: ‘first_purchase’, ‘purchase’, ‘sale’, or ‘no_change’.
tokens_purchasedThe number of tokens purchased in this transaction (0 for sales).
tokens_soldThe number of tokens sold in this transaction (0 for purchases).
average_costThe current weighted average cost per token unit after this transaction.
cumulative_costsThe total USD cost basis remaining after accounting for proportional reductions from sales.
cumulative_quantitiesThe total token quantity remaining after accounting for sales.
realized_pnlThe cumulative realized profit/loss from all sales transactions for this token up to this point.
realized_pnl_this_txThe realized profit/loss from this specific transaction (null for purchases).
unrealized_pnlThe unrealized profit/loss based on the token price at the time of this transaction.
unrealized_pnl_latestThe unrealized profit/loss based on the latest available token price.
block_timestampThe timestamp of the block containing this transaction.
block_numberThe block number containing this transaction.
block_hashThe hash of the block containing this transaction.
unique_idA unique identifier for this PNL record.