Fact Stake Pool Actions

​

Description

​

Key Use Cases

• Analyze liquid staking adoption and user behavior
• Track stake pool performance and yield generation
• Study staking protocol usage patterns and preferences
• Monitor staking flows and capital allocation
• Support analytics on DeFi staking ecosystem growth

​

Important Relationships

• Closely related to defi.ez_liquidity_pool_actions (for liquidity provision), defi.ez_dex_swaps (for DEX activity), and core.ez_transfers (for token movements)
• Use defi.ez_liquidity_pool_actions to analyze liquidity provision in staking-related pools
• Use defi.ez_dex_swaps to track trading of liquid staking tokens
• Use core.ez_transfers to analyze token movements related to staking
• Joins with core.fact_blocks for block context and core.fact_transactions for transaction context

​

Commonly-used Fields

• block_timestamp: For time-series and staking activity analysis
• stake_pool_name, action: For pool and action identification
• amount: For staking amount analytics
• address: For user and pool address analysis
• succeeded: For transaction success analysis

​

Sample Queries

​

Daily liquid staking activity by protocol

-- Daily liquid staking activity by protocol
SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    stake_pool_name AS platform,
    action,
    COUNT(DISTINCT tx_id) AS action_txns,
    COUNT(DISTINCT address) AS unique_users,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount_per_action
FROM solana.defi.fact_stake_pool_actions
WHERE block_timestamp >= CURRENT_DATE - 30
    AND succeeded = true
    AND amount IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 6 DESC;

​

Stake pool market share analysis

-- Stake pool market share analysis
WITH pool_totals AS (
    SELECT
        stake_pool_name,
        SUM(CASE WHEN action = 'deposit' THEN amount ELSE 0 END) AS total_deposits,
        SUM(CASE WHEN action = 'withdraw' THEN amount ELSE 0 END) AS total_withdrawals,
        COUNT(DISTINCT address) AS unique_users,
        COUNT(*) AS total_actions
    FROM solana.defi.fact_stake_pool_actions
    WHERE block_timestamp >= CURRENT_DATE - 90
        AND succeeded = true
        AND amount IS NOT NULL
    GROUP BY stake_pool_name
)
SELECT
    stake_pool_name,
    total_deposits,
    total_deposits * 100.0 / SUM(total_deposits) OVER () AS deposit_market_share_pct,
    total_withdrawals,
    total_deposits - total_withdrawals AS net_deposits,
    unique_users,
    total_actions,
    total_deposits / NULLIF(total_actions, 0) AS avg_action_size
FROM pool_totals
ORDER BY total_deposits DESC;

​

User staking behavior patterns

-- User staking behavior patterns
WITH user_activity AS (
    SELECT
        address AS user_address,
        COUNT(DISTINCT stake_pool_name) AS pools_used,
        COUNT(DISTINCT action) AS action_types_used,
        COUNT(*) AS total_actions,
        SUM(CASE WHEN action = 'deposit' THEN amount ELSE 0 END) AS total_deposits,
        SUM(CASE WHEN action = 'withdraw' THEN amount ELSE 0 END) AS total_withdrawals,
        MIN(block_timestamp) AS first_action,
        MAX(block_timestamp) AS last_action
    FROM solana.defi.fact_stake_pool_actions
    WHERE succeeded = true
        AND amount IS NOT NULL
    GROUP BY address
)
SELECT
    CASE
        WHEN total_deposits < 1 THEN '< 1 SOL'
        WHEN total_deposits < 10 THEN '1-10 SOL'
        WHEN total_deposits < 100 THEN '10-100 SOL'
        WHEN total_deposits < 1000 THEN '100-1K SOL'
        ELSE '1K+ SOL'
    END AS user_tier,
    COUNT(*) AS user_count,
    AVG(total_actions) AS avg_actions_per_user,
    AVG(pools_used) AS avg_pools_used,
    SUM(total_deposits) AS tier_total_deposits,
    SUM(total_withdrawals) AS tier_total_withdrawals
FROM user_activity
GROUP BY user_tier
ORDER BY MIN(total_deposits);

​

Stake pool performance comparison

-- Stake pool performance comparison
WITH pool_metrics AS (
    SELECT
        stake_pool_name,
        COUNT(DISTINCT address) AS unique_users,
        COUNT(*) AS total_actions,
        SUM(CASE WHEN action = 'deposit' THEN amount ELSE 0 END) AS total_deposits,
        SUM(CASE WHEN action = 'withdraw' THEN amount ELSE 0 END) AS total_withdrawals,
        AVG(CASE WHEN action = 'deposit' THEN amount END) AS avg_deposit_size,
        COUNT(CASE WHEN succeeded = false THEN 1 END) * 100.0 / COUNT(*) AS failure_rate_pct
    FROM solana.defi.fact_stake_pool_actions
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND amount IS NOT NULL
    GROUP BY 1
)
SELECT
    stake_pool_name,
    unique_users,
    total_actions,
    total_deposits,
    total_withdrawals,
    total_deposits - total_withdrawals AS net_flow,
    avg_deposit_size,
    failure_rate_pct,
    total_actions / NULLIF(unique_users, 0) AS actions_per_user
FROM pool_metrics
ORDER BY total_deposits DESC;

​

Large staking actions monitoring

-- Large staking actions monitoring (whale activity)
SELECT
    block_timestamp,
    tx_id,
    stake_pool_name AS platform,
    action,
    address AS user_address,
    amount,
    succeeded
FROM solana.defi.fact_stake_pool_actions
WHERE amount >= 1000  -- Large stakes (1000+ SOL)
    AND block_timestamp >= CURRENT_DATE - 7
ORDER BY amount DESC;

​

Columns

• Data type: STRING (e.g., ‘Lido’, ‘Marinade’, ‘Jito’)
• Business context: Used to identify the staking protocol, analyze protocol adoption, and compare staking performance.
• Analytics use cases: Staking protocol market share analysis, protocol adoption tracking, and staking performance comparison.
• Example: ‘Lido’ | | 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 | | 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). | | INDEX | NUMBER | The position of the event (instruction) within the list of instructions for a given Solana transaction. Used to order and reference events within a transaction. Indexing starts at 0 for the first event.

• 0
• 3

• Enables precise identification and ordering of events within a transaction, which is critical for reconstructing transaction flows and analyzing protocol behavior.
• Used to join or filter event-level data, especially when multiple events occur in a single transaction. | | SUCCEEDED | BOOLEAN | Boolean flag indicating whether the transaction was successfully executed and confirmed on the Solana blockchain. A value of TRUE means the transaction was processed without errors; FALSE indicates failure due to program errors, insufficient funds, or other issues.

• true
• false

• Used to filter for successful transactions in analytics and reporting.
• Important for error analysis, user experience, and program debugging. | | ACTION | TEXT | The type of action performed on the stake pool, such as deposit, withdraw, or other staking-related operations.
• Data type: STRING (e.g., ‘deposit’, ‘withdraw’, ‘stake’, ‘unstake’)
• Business context: Used to categorize staking actions, analyze user behavior, and track staking patterns.
• Analytics use cases: Staking action analysis, user behavior studies, and staking pattern tracking.
• Example: ‘deposit’ | | ADDRESS | TEXT | The address of the stake pool or the user address involved in the staking action. This field identifies the specific address related to the staking operation.
• Data type: STRING (base58 Solana address)
• Business context: Used to track stake pool addresses, analyze staking activity, and identify staking participants.
• Analytics use cases: Staking address analysis, participant tracking, and staking activity studies.
• Example: ‘4Nd1mYw4r…’ | | STAKE_POOL | TEXT | The stake pool address or identifier that represents the specific staking pool within a protocol. This field enables pool-level analytics and tracking.
• Data type: STRING (base58 Solana address or pool identifier)
• Business context: Used to identify specific staking pools, analyze pool performance, and track pool-specific activity.
• Analytics use cases: Pool-level analysis, performance comparison, and pool-specific activity tracking.
• Example: ‘4Nd1mYw4r…’ | | AMOUNT | VARIANT | The amount involved in the stake pool action, denominated in lamports (the smallest unit of SOL). This field enables staking amount analysis and tracking.
• Data type: NUMBER (integer, lamports)
• Business context: Used to track staking amounts, analyze staking patterns, and measure staking activity.
• Analytics use cases: Staking amount analysis, staking pattern studies, and staking activity measurement.
• Example: 1000000000 (1 SOL in lamports) | | TOKEN | TEXT | The token utilized in the stake pool action, typically representing the staking token or reward token involved in the operation.
• Data type: STRING (token symbol or mint address)
• Business context: Used to identify the token involved in staking, analyze token-specific staking activity, and track staking token usage.
• Analytics use cases: Token-specific staking analysis, staking token tracking, and cross-token staking studies.
• Example: ‘SOL’ or ‘mSOL’ | | FACT_STAKE_POOL_ACTIONS_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. | | 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. |