Blockchain Coverage

We support pnl data for the following blockchains:

Blockchain	Balances Model
EVM-Compatible Blockchains	The `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`.
Solana	The `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

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

Detailed PNL Calculation Methodology and Examples

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, average_cost =$ 2,000
New purchase: 50 ETH at $2,400 =$ 120,000
New cumulative_costs: $200,000 +$ 120,000 = $320,000
New quantities: 100 + 50 = 150 ETH
New average_cost: $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, average_cost =$ 2,133.33
Sell: 30 ETH at $2,500 per ETH
Realized PNL: 30 × ( $2,500 -$ 2,133.33) = $11,000.10 gain
New cumulative_costs: $320,000 - (30 ×$ 2,133.33) = $256,000.10
New quantities: 150 - 30 = 120 ETH
New average_cost: $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,133.33) = $56,000.40
Unrealized PNL Latest: 120 × ( $2,800 -$ 2,133.33) = $80,000.40

Getting Daily PNL

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

Getting Aggregated PNL

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 Name	Description
`chain`	The EVM-compatible blockchain where the assets are held.
`token_type`	The type of token (e.g., ‘native’, ‘erc20’).
`address`	The wallet address holding the token.
`token_address`	The contract address of the token. Native tokens are represented with zero address.
`token_name`	The name of the token.
`token_symbol`	The symbol of the token.
`balance`	The current token balance after this transaction.
`prev_balance`	The token balance before this transaction.
`balance_change`	The change in token balance from this transaction (balance - prev_balance). Can be negative.
`usd_exchange_rate`	The USD exchange rate for the token at the time of the transaction.
`usd_balance`	The current token balance converted to USD at transaction time.
`usd_balance_change`	The change in USD balance from this transaction. Can be negative.
`usd_exchange_rate_latest`	The latest available USD exchange rate for the token.
`usd_balance_latest`	The current token balance converted to USD using the latest exchange rate.
`transaction_type`	The type of transaction: ‘first_purchase’, ‘purchase’, ‘sale’, or ‘no_change’.
`tokens_purchased`	The number of tokens purchased in this transaction (0 for sales).
`tokens_sold`	The number of tokens sold in this transaction (0 for purchases).
`average_cost`	The current weighted average cost per token unit after this transaction.
`cumulative_costs`	The total USD cost basis remaining after accounting for proportional reductions from sales.
`cumulative_quantities`	The total token quantity remaining after accounting for sales.
`realized_pnl`	The cumulative realized profit/loss from all sales transactions for this token up to this point.
`realized_pnl_this_tx`	The realized profit/loss from this specific transaction (null for purchases).
`unrealized_pnl`	The unrealized profit/loss based on the token price at the time of this transaction.
`unrealized_pnl_latest`	The unrealized profit/loss based on the latest available token price.
`block_timestamp`	The timestamp of the block containing this transaction.
`block_number`	The block number containing this transaction.
`block_hash`	The hash of the block containing this transaction.
`unique_id`	A unique identifier for this PNL record.

Overview

Data Verticals

Solana

Hyperliquid

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

PNL

Blockchain Coverage

PNL calculation

Average-Cost Methodology

Key Fields

Core Principles

Calculation Steps

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

2. Sale Transactions (transaction_type: ‘sale’)

3. Unrealized PNL Calculations

Table Columns

Overview

Data Verticals

Solana

Hyperliquid

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

​Blockchain Coverage

​PNL calculation

​Average-Cost Methodology

​Key Fields

​Core Principles

​Table Columns

Blockchain Coverage

PNL calculation

Average-Cost Methodology

Key Fields

Core Principles

Table Columns