Updated 2025-01-26
    WITH staking_data AS (
    SELECT
    delegator,
    COUNT(DISTINCT CASE WHEN action IN ('TokensCommitted', 'DelegatorTokensCommitted') THEN tx_id ELSE NULL END) AS total_stake_actions,
    SUM(CASE WHEN action IN ('TokensCommitted', 'DelegatorTokensCommitted') THEN amount ELSE 0 END) AS total_stake_volume,
    DATE_TRUNC('month', block_timestamp) AS month
    FROM
    flow.gov.ez_staking_actions
    WHERE
    tx_succeeded = TRUE
    AND block_timestamp >= '2024-08-01'
    GROUP BY
    delegator, DATE_TRUNC('month', block_timestamp)
    ),
    new_stakers AS (
    SELECT
    delegator
    FROM
    flow.gov.ez_staking_actions
    WHERE
    tx_succeeded = TRUE
    AND block_timestamp >= '2024-08-01'
    GROUP BY
    delegator
    HAVING
    NOT EXISTS (
    SELECT 1
    FROM flow.gov.ez_staking_actions prev_stakes
    WHERE prev_stakes.delegator = flow.gov.ez_staking_actions.delegator
    AND prev_stakes.block_timestamp < '2024-08-01'
    AND prev_stakes.tx_succeeded = TRUE
    )
    ),
    staker_activity AS (
    SELECT
    sd.delegator,
    QueryRunArchived: QueryRun has been archived