Ez Dex Swaps

​

Description

​

Key Use Cases

• Analyze DEX trading activity with USD-denominated metrics
• Calculate real swap volume and compare swap activity across DEXes
• Compare activity across different curated swap programs
• Analyze user intent and distinguish routed vs. direct swaps

​

Important Relationships

• Source data from defi.fact_swaps
• Use with defi.fact_swaps_jupiter_inner and defi.fact_swaps_jupiter_summary for routed swap analysis
• Example queries: swap metrics by DEX protocol, compare activity across routed/direct swaps

​

Commonly-used Fields

• block_timestamp, swapper, swap_from_mint, swap_to_mint, swap_from_amount_usd, swap_to_amount_usd, swap_program

​

Sample Queries

​

Real swap volume calculation by DEX protocol

-- Calculate real swap volume by DEX protocol (includes both direct and Jupiter-routed swaps)
SELECT
    swap_program,
    COUNT(*) AS total_swaps,
    COUNT(DISTINCT swapper) AS unique_users,
    SUM(swap_from_amount_usd) AS total_volume_usd,
    AVG(swap_from_amount_usd) AS avg_swap_size_usd
FROM solana.defi.ez_dex_swaps
WHERE block_timestamp >= CURRENT_DATE - 7
    AND swap_from_amount_usd IS NOT NULL
GROUP BY 1
ORDER BY total_volume_usd DESC;

​

Buy/sell volume analysis for a specific token

-- Get buy/sell volume for JTO token across all DEXes
WITH sells AS (
    SELECT
        block_timestamp::date AS dt,
        SUM(swap_from_amount) AS sum_sells,
        COUNT(DISTINCT swapper) AS unique_sellers
    FROM solana.defi.ez_dex_swaps
    WHERE swap_from_mint = 'jtojtomepa8beP8AuQc6eXt5FriJwfFMwQx2v2f9mCL'
        AND block_timestamp::date BETWEEN '2025-01-01' AND '2025-01-07'
    GROUP BY 1
),
buys AS (
    SELECT
        block_timestamp::date AS dt,
        SUM(swap_to_amount) AS sum_buys,
        COUNT(DISTINCT swapper) AS unique_buyers
    FROM solana.defi.ez_dex_swaps
    WHERE swap_to_mint = 'jtojtomepa8beP8AuQc6eXt5FriJwfFMwQx2v2f9mCL'
        AND block_timestamp::date BETWEEN '2025-01-01' AND '2025-01-07'
    GROUP BY 1
)
SELECT
    COALESCE(s.dt, b.dt) AS dt,
    COALESCE(s.sum_sells, 0) AS sell_volume,
    COALESCE(s.unique_sellers, 0) AS unique_sellers,
    COALESCE(b.sum_buys, 0) AS buy_volume,
    COALESCE(b.unique_buyers, 0) AS unique_buyers
FROM sells s
FULL OUTER JOIN buys b ON s.dt = b.dt
ORDER BY dt;

​

Token pair trading analysis

-- Most traded token pairs by volume
SELECT
    swap_from_symbol || ' -> ' || swap_to_symbol AS trading_pair,
    COUNT(*) AS swap_count,
    SUM(swap_from_amount_usd) AS total_volume_usd,
    AVG(swap_from_amount_usd) AS avg_swap_size,
    COUNT(DISTINCT swapper) AS unique_traders,
    COUNT(DISTINCT swap_program) AS dex_count
FROM solana.defi.ez_dex_swaps
WHERE block_timestamp >= CURRENT_DATE - 7
    AND swap_from_amount_usd > 0
    AND swap_from_symbol IS NOT NULL
    AND swap_to_symbol IS NOT NULL
GROUP BY 1
HAVING swap_count >= 10
ORDER BY total_volume_usd DESC
LIMIT 50;

​

Compare Jupiter-routed vs direct DEX swaps

-- Compare volume of Jupiter-routed swaps vs direct DEX swaps for wSOL
with jupiter_routed_swaps as (
select
tx_id,
block_timestamp,
swap_from_amount
from solana.defi.fact_swaps_jupiter_inner
where swap_from_mint = 'So11111111111111111111111111111111111111112'
and block_timestamp::date between '2025-05-01' and '2025-05-07')
,

direct_swaps as (
select
tx_id,
block_timestamp,
swap_from_amount
from solana.defi.fact_swaps
where swap_from_mint = 'So11111111111111111111111111111111111111112'
and block_timestamp::date between '2025-05-01' and '2025-05-07'
and tx_id not in (select distinct(tx_id) from jupiter_routed_swaps) -- need to exclude the jupiter related swaps from fact_swaps
)
,

sum_jupiter as (
select
block_timestamp::date dt,
sum(swap_from_amount) as jup_amt
from jupiter_routed_swaps
group by 1)

,
sum_direct as (
select
block_timestamp::date dt,
sum(swap_from_amount) as direct_amt
from direct_swaps
group by 1)

Select
a.dt,
a.jup_amt as swap_via_jupiter,
b.direct_amt as direct_swap,
a.jup_amt + b.direct_amt as total_amt
from sum_jupiter a
left join sum_direct b
on a.dt = b.dt;

​

Columns

• Data type: STRING (e.g., ‘Raydium’, ‘Orca’, ‘Meteora’, ‘Phoenix’)
• Business context: Used to group swaps by protocol, analyze DEX usage trends, and compare protocol performance.
• Analytics use cases: DEX market share analysis, protocol adoption tracking, and swap routing optimization.
• Example: ‘Raydium’ | | BLOCK_ID | NUMBER | A unique identifier for the block in which this transaction was included on the Solana blockchain. Typically a sequential integer or hash, depending on the data source. Used to group transactions by block and analyze block-level activity.

• 123456789

• Supports block-level analytics, such as block production rate and transaction throughput.
• Useful for tracing transaction inclusion and block explorer integrations.

• All transactions with the same ‘block_id’ share the same ‘block_timestamp’. | | BLOCK_TIMESTAMP | TIMESTAMP_NTZ | The timestamp (UTC) at which the block was produced on the Solana blockchain. This field is recorded as a TIMESTAMP data type and represents the precise moment the block was finalized and added to the chain. It is essential for time-series analysis, block production monitoring, and aligning transaction and event data to specific points in time. Used extensively for analytics involving block intervals, network activity trends, and historical lookups. Format: YYYY-MM-DD HH:MI:SS (UTC). | | TX_ID | TEXT | The unique transaction signature (hash) for each transaction on the Solana blockchain. This field is a base58-encoded string, typically 88 characters in length, and serves as the primary identifier for transactions across all Solana data models. Used to join transaction data with related tables (blocks, events, transfers, logs, decoded instructions) and to trace the full lifecycle and effects of a transaction. Essential for transaction-level analytics, debugging, and cross-referencing with block explorers or Solana APIs.

• 5Nf6Q2k6v1Qw2k3v4Qw5Nf6Q2k6v1Qw2k3v4Qw5Nf6Q2k6v1Qw2k3v4Qw5Nf6Q2k6v1Qw2k3v4Qw

• Enables precise tracking, auditing, and attribution of on-chain activity
• Used for linking transactions to events, logs, and protocol actions
• Critical for compliance, monitoring, and analytics workflows | | PROGRAM_ID | TEXT | The unique public key (base58-encoded address) of a Solana program. This field identifies the on-chain program (smart contract) responsible for processing instructions, emitting events, or managing accounts. Used throughout Solana analytics models—including events, transactions, IDLs, and program activity tables—to join, filter, and analyze program-level data.

• “4Nd1mY…”
• “TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA”

• Used as a join key for program activity, deployments, events, and interface changes.
• Supports segmentation of activity by protocol, DEX, NFT marketplace, or other on-chain application. | | SWAPPER | TEXT | The Solana address (base58-encoded string) that initiated the swap transaction. This field identifies the user or contract responsible for the swap, enabling wallet-level analytics, behavioral segmentation, and user journey analysis.
• Data type: STRING (base58 Solana address, e.g., 4Nd1mYw4r...)
• Business context: Used to group swaps by user, analyze trading patterns, and track protocol adoption.
• Analytics use cases: User segmentation, whale tracking, protocol adoption analysis, and cross-table joins with other user-activity models.
• Example: 4Nd1mYw4r... | | SWAP_FROM_MINT | TEXT | The mint address of the token being sent or swapped from in the transaction. This is a unique identifier for the SPL token or SOL.
• Data type: STRING (base58 Solana mint address)
• Business context: Used to identify the source asset in the swap, filter by token, and analyze token-specific activity.
• Analytics use cases: Token flow analysis, asset popularity studies, and cross-token comparisons.
• Example: SOL: So11111111111111111111111111111111111111112, USDC: Es9vMFrzaCER... | | SWAP_FROM_SYMBOL | TEXT | The symbol of the token being sent or swapped from in the transaction. This field provides a human-readable identifier for the source asset, enabling token-specific analytics and filtering.
• Data type: STRING (e.g., ‘SOL’, ‘USDC’, ‘RAY’)
• Business context: Used to identify the source asset in the swap, filter by token, and analyze token-specific activity.
• Analytics use cases: Token flow analysis, asset popularity studies, and cross-token comparisons.
• Example: ‘SOL’ | | SWAP_FROM_AMOUNT | FLOAT | The total amount of the token sent in to initiate the swap, as recorded on-chain. This value is already decimal adjusted according to the token’s standard decimals, and represents the human-readable amount.
• Data type: NUMBER (float or integer, depending on token)
• Business context: Used to calculate swap volume, analyze liquidity flows, and measure user activity.
• Analytics use cases: Volume analysis, liquidity tracking, slippage calculations, and DEX protocol comparisons.
• Example: For SOL, a value of 2.5 means 2.5 SOL. For USDC, a value of 100.5 means 100.5 USDC. | | SWAP_FROM_AMOUNT_USD | FLOAT | The USD value of the tokens being sent into the swap, calculated using the token’s price at the time of the swap. This field enables USD-denominated volume analysis and value flow tracking.
• Data type: NUMBER (float, USD value)
• Business context: Used to calculate USD swap volume, analyze value flows, and measure economic impact of swaps.
• Analytics use cases: USD volume analysis, value flow tracking, and economic impact measurement.
• Example: 150.25 | | SWAP_TO_MINT | TEXT | The mint address of the token being received or swapped to in the transaction. This is a unique identifier for the SPL token or SOL.
• Data type: STRING (base58 Solana mint address)
• Business context: Used to identify the destination asset in the swap, filter by token, and analyze token-specific inflows.
• Analytics use cases: Token inflow analysis, asset demand studies, and DEX routing analytics.
• Example: SOL: So11111111111111111111111111111111111111112, USDT: BQvQ8... | | SWAP_TO_SYMBOL | TEXT | The symbol of the token being received or swapped to in the transaction. This field provides a human-readable identifier for the destination asset, enabling token-specific analytics and filtering.
• Data type: STRING (e.g., ‘USDC’, ‘SOL’, ‘RAY’)
• Business context: Used to identify the destination asset in the swap, filter by token, and analyze token-specific inflows.
• Analytics use cases: Token inflow analysis, asset demand studies, and DEX routing analytics.
• Example: ‘USDC’ | | SWAP_TO_AMOUNT | FLOAT | The total amount of the token received from the swap, as recorded on-chain. This value is already decimal adjusted according to the token’s standard decimals, and represents the human-readable amount.
• Data type: NUMBER (float or integer, depending on token)
• Business context: Used to measure swap output, analyze price impact, and track liquidity received.
• Analytics use cases: Output volume analysis, slippage and price impact studies, and DEX performance comparisons.
• Example: For USDC, a value of 1.0 means 1 USDC. For SOL, a value of 0.25 means 0.25 SOL. | | SWAP_TO_AMOUNT_USD | FLOAT | The USD value of the tokens being received from the swap, calculated using the token’s price at the time of the swap. This field enables USD-denominated output analysis and value flow tracking.
• Data type: NUMBER (float, USD value)
• Business context: Used to calculate USD output volume, analyze value flows, and measure economic impact of swaps.
• Analytics use cases: USD output analysis, value flow tracking, and economic impact measurement.
• Example: 148.75 | | _LOG_ID | TEXT | A unique identifier for the swap event, typically a combination of the transaction ID (TX_ID) and the event index within the transaction. This field enables precise event-level analytics and deduplication.
• Data type: STRING (composite key, e.g., ‘5G7…:3’)
• Business context: Used to uniquely identify swap events, prevent double-counting, and join with event-level logs.
• Analytics use cases: Event-level deduplication, log/event joins, and transaction traceability.
• Example: ‘5G7…:3’ | | INSERTED_TIMESTAMP | TIMESTAMP_NTZ | The timestamp when this transaction record was first inserted into the analytics database. Used for data freshness tracking and incremental model logic. Format: YYYY-MM-DD HH:MI:SS. Not derived from the blockchain, but from the ETL process. | | MODIFIED_TIMESTAMP | TIMESTAMP_NTZ | The timestamp when this transaction record was last updated in the analytics database. Used for tracking updates and supporting incremental model logic. Format: YYYY-MM-DD HH:MI:SS. Not derived from the blockchain, but from the ETL process. | | EZ_SWAPS_ID | TEXT | A unique, stable identifier for each record in this table. The primary key (PK) ensures that every row is uniquely identifiable and supports efficient joins, lookups, and data integrity across models. The PK may be a natural key (such as a blockchain transaction hash) or a surrogate key generated from one or more fields, depending on the table’s structure and requirements. | | SWAP_FROM_IS_VERIFIED | BOOLEAN | A flag indicating if the asset has been verified by the Flipside team. | | SWAP_TO_IS_VERIFIED | BOOLEAN | A flag indicating if the asset has been verified by the Flipside team. |