Skip to main content
The solana.assets.pnl table holds transaction-level PNL records.

​
PNL calculation and Methodology

See here for details.

​
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
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 address, token_account, mint, block_timestamp order by block_slot desc, txn_index desc) as rank_in_timestamp_secs,
    row_number() over (partition by address, token_account, mint order by block_timestamp, block_slot, txn_index) as global_rank,
    from solana.assets.pnl
    where address in (
        '...'
    )
    
), time_axis as (
    select 
    
    d.DATE_TIMESTAMP::varchar as description,
    d.DATE_TIMESTAMP as activity_date,
    address, token_account, mint
    from common.utility.dates d
    cross join (select distinct address, token_account, mint 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 (address, token_account, mint)
    
    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_account = time_axis.token_account
        and pnl.mint = time_axis.mint
),
unioned as (
    select
    'first' as description, block_timestamp as activity_date, address, token_account, mint,
    pnl.* exclude (address, token_account, mint)
    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, address, token_account, mint
This query shows how to get aggregated PNL across multiple address and/or tokens for portfolio-level analysis.
with latest_pnl as (
    select 
    *
    from solana.assets.pnl
    where address in (
        '...'
    )
    qualify row_number() over (partition by address, token_account, mint order by block_timestamp desc, block_slot desc, txn_index desc) = 1
), unioned as (
    -- Aggregated PNL across all tokens
    select 
    address,
    count(distinct mint) as total_tokens_held,
    count(distinct token_account) as total_token_accounts_used,
    
    null as mint,
    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 total_token_accounts_used,
    
    mint,
    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, mint

​
Table Columns

Unique Key: address, token_account, mint, unique_id
Column NameDescription
token_typeThe type of token (e.g., ‘native’, ‘spl-token’).
addressThe Solana wallet address holding the token.
token_accountThe token account address associated with this token holding.
mintThe mint address of the token. Native SOL is represented with a special mint 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_slotThe Solana block slot number containing this transaction.
block_heightThe Solana block height containing this transaction.
block_hashThe hash of the block containing this transaction.
txn_idThe unique transaction ID (signature) for this transaction.
txn_indexThe index of this transaction within the block.
unique_idA unique identifier for this PNL record.