bq etl stakes apy select

apy filtered.sql

WITH selected_validators AS (
  SELECT vote_account FROM UNNEST([
    'EdGevanAjM8a6Gg9KxBVrmVdZAUGAZ9xaVd7t9R4H2x',
    'Haz7b47sZBpxh9SwggGndN3fAyNQ1S949BPdxWXS3ab6',
    'juicQdAnksqZ5Yb8NQwCLjLWhykvXGktxnQCDvMe6Nx',
    '5iJDEVRi1nMLwKAWhYbEokZnvBAe15rgFaHGkggVEP9z',
    '51JBzSTU5rAM8gLAVQKgp4WoZerQcSqWC7BitBzgUNAm',
    'LAKEuKJQYVFpf4vyjX7iuf9ajHo3k9FiyewYKf6VxPV',
    'ErvMUdtMC7AX55zKdYSyy4DnWNCrTsWn5GwprSG7ocnx',
    'FQwewNXahV7MiZcLpY6p1xhUs2acVGQ3U5Xxc7FzV571', -- blockdaemon
    'he1iusunGwqrNtafDtLdhsUQDFvo13z9sUa36PauBtk', -- helius
    '5s3vajJvaAbabQvxFdiMfg14y23b2jvK6K2Mw4PYcYK', -- QuickNode
    'JEJzKYzyYJJjtn6Yb1P7r6YV75TdSNmmJT49sgDoHvmk', -- Alchemy
    'SyNdica7qx3njeVKNgXvV7KC1NjPYNS4fyb3NxgevLH', -- Syndica 
    '6F5xdRXh2W3B2vhte12VG79JVUkUSLYrHydGX1SAadfZ', -- AllNodes
    'CcaHc2L43ZWjwCHART3oZoJvHLAe9hzT2DJNUpBzoTN1' -- Figment
    '9QU2QSxhb24FUX3Tu2FpczXjpK3VYrvRudywSZaM29mF', -- everstake
    'DumiCKHVqoCQKD8roLApzR5Fit8qGV5fVQsJV9sTZk4a' -- staking facilities
  ]) AS vote_account
),

validator_rewards_by_epoch AS (
  WITH validator_inflation AS (
    SELECT 
      CAST(epoch AS STRING) AS epoch,
      vote_account,
      CAST(amount AS NUMERIC) AS validator_inflation_amount
    FROM 
      `data-store-406413.mainnet_beta_stakes.rewards_validators_inflation`
    WHERE 
      -- epoch 758 is the first where we can find data about inflation 
      CAST(epoch AS INT64) >= 758
      AND vote_account IN (SELECT vote_account FROM selected_validators)
  ),

  validator_mev AS (
    SELECT 
      CAST(epoch AS STRING) AS epoch,
      vote_account,
      CAST(amount AS NUMERIC) AS validator_mev_amount
    FROM 
      `data-store-406413.mainnet_beta_stakes.rewards_validators_mev`
    WHERE 
      CAST(epoch AS INT64) >= 758
      AND vote_account IN (SELECT vote_account FROM selected_validators)
  ),

  validator_blocks AS (
    SELECT 
      CAST(epoch AS STRING) AS epoch,
      vote_account,
      identity_account,
      CAST(amount AS NUMERIC) AS validator_blocks_amount
    FROM 
      `data-store-406413.mainnet_beta_stakes.rewards_validators_blocks`
    WHERE 
      CAST(epoch AS INT64) >= 758
      AND vote_account IN (SELECT vote_account FROM selected_validators)
  ),

  -- Rewards that need to be mapped from stake_account to vote_account
  stake_inflation_with_vote AS (
    SELECT 
      CAST(r.epoch AS STRING) AS epoch,
      r.stake_account,
      CAST(r.amount AS NUMERIC) AS stake_inflation_amount,
      s.vote_account
    FROM 
      `data-store-406413.mainnet_beta_stakes.rewards_inflation` r
    JOIN 
      `data-store-406413.mainnet_beta_stakes.stakes` s
      ON r.stake_account = s.stake_account AND r.epoch = s.epoch
    WHERE 
      CAST(r.epoch AS INT64) >= 758
      AND vote_account IN (SELECT vote_account FROM selected_validators)
  ),

  stake_mev_with_vote AS (
    SELECT 
      CAST(r.epoch AS STRING) AS epoch,
      r.stake_account,
      CAST(r.amount AS NUMERIC) AS stake_mev_amount,
      s.vote_account
    FROM 
      `data-store-406413.mainnet_beta_stakes.rewards_mev` r
    JOIN 
      `data-store-406413.mainnet_beta_stakes.stakes` s
      ON r.stake_account = s.stake_account AND r.epoch = s.epoch
    WHERE 
      CAST(r.epoch AS INT64) >= 758
      AND vote_account IN (SELECT vote_account FROM selected_validators)
  ),

  stake_inflation_by_validator AS (
    SELECT 
      epoch,
      vote_account,
      SUM(stake_inflation_amount) AS total_stake_inflation_amount
    FROM 
      stake_inflation_with_vote
    GROUP BY 
      epoch, vote_account
  ),

  stake_mev_by_validator AS (
    SELECT 
      epoch,
      vote_account,
      SUM(stake_mev_amount) AS total_stake_mev_amount
    FROM 
      stake_mev_with_vote
    GROUP BY 
      epoch, vote_account
  ),

  -- Final summary joining all rewards by vote_account and epoch
  validator_rewards AS (
    SELECT 
      CAST(COALESCE(vi.epoch, vm.epoch, vb.epoch, si.epoch, sm.epoch) AS STRING) AS epoch,
      COALESCE(vi.vote_account, vm.vote_account, vb.vote_account, si.vote_account, sm.vote_account) AS vote_account,
      vb.identity_account,
      (
        COALESCE(CAST(vi.validator_inflation_amount AS NUMERIC), 0) + 
        COALESCE(CAST(vm.validator_mev_amount AS NUMERIC), 0) + 
        COALESCE(CAST(vb.validator_blocks_amount AS NUMERIC), 0) + 
        COALESCE(CAST(si.total_stake_inflation_amount AS NUMERIC), 0) + 
        COALESCE(CAST(sm.total_stake_mev_amount AS NUMERIC), 0)
      ) AS total_rewards_lamports,
      (
        COALESCE(CAST(vb.validator_blocks_amount AS NUMERIC), 0)
      ) AS block_rewards_lamports
    FROM 
      validator_inflation vi
    FULL OUTER JOIN 
      validator_mev vm ON vi.vote_account = vm.vote_account AND vi.epoch = vm.epoch
    FULL OUTER JOIN 
      validator_blocks vb ON COALESCE(vi.vote_account, vm.vote_account) = vb.vote_account AND COALESCE(vi.epoch, vm.epoch) = vb.epoch
    FULL OUTER JOIN 
      stake_inflation_by_validator si ON COALESCE(vi.vote_account, vm.vote_account, vb.vote_account) = si.vote_account AND COALESCE(vi.epoch, vm.epoch, vb.epoch) = si.epoch
    FULL OUTER JOIN 
      stake_mev_by_validator sm ON COALESCE(vi.vote_account, vm.vote_account, vb.vote_account, si.vote_account) = sm.vote_account AND COALESCE(vi.epoch, vm.epoch, vb.epoch, si.epoch) = sm.epoch
  )
  SELECT * FROM validator_rewards
),

total_staked_by_epoch AS (
  SELECT
    CAST(epoch AS STRING) AS epoch,
    SUM(CAST(active AS NUMERIC) + CAST(deactivating AS NUMERIC)) / 1e9 AS total_staked_sol
  FROM
    `data-store-406413.mainnet_beta_stakes.stakes`
  WHERE
    CAST(epoch AS INT64) >= 758
  GROUP BY
    epoch
),

validator_staked_by_epoch AS (
  SELECT
    CAST(epoch AS STRING) AS epoch,
    vote_account,
    SUM(CAST(active AS NUMERIC) + CAST(deactivating AS NUMERIC)) / 1e9 AS validator_staked_sol
  FROM
    `data-store-406413.mainnet_beta_stakes.stakes`
  WHERE
    CAST(epoch AS INT64) >= 758
    AND vote_account IN (SELECT vote_account FROM selected_validators)
  GROUP BY
    epoch, vote_account
),

validator_apy_by_epoch AS (
  SELECT
    r.epoch,
    r.vote_account,
    r.total_rewards_lamports / 1e9 AS validator_rewards_sol,
    r.block_rewards_lamports / 1e9 AS validator_block_rewards_sol,
    vs.validator_staked_sol,
    ts.total_staked_sol,
    -- Calculate APY with safeguards against numeric overflow
    -- BigQuery fails with POW functions
    CASE 
      WHEN vs.validator_staked_sol > 0 THEN 
        CASE
          -- If per-epoch return is too high, use a simplified calculation to avoid overflow
          -- TODO: maybe simplified version should be one to be used
          WHEN (r.total_rewards_lamports / 1e9) / vs.validator_staked_sol > 0.1 THEN 
            ((r.total_rewards_lamports / 1e9) / vs.validator_staked_sol) * 183 * 100
          ELSE
            (POW(1 + LEAST((r.total_rewards_lamports / 1e9) / vs.validator_staked_sol, 0.1), 183) - 1) * 100
        END
      ELSE 0 
    END AS validator_apy_percentage,
    CASE 
      WHEN vs.validator_staked_sol > 0 THEN 
        CASE
          WHEN (r.block_rewards_lamports / 1e9) / vs.validator_staked_sol > 0.1 THEN 
            ((r.block_rewards_lamports / 1e9) / vs.validator_staked_sol) * 183 * 100
          ELSE
            (POW(1 + LEAST((r.block_rewards_lamports / 1e9) / vs.validator_staked_sol, 0.1), 183) - 1) * 100
        END
      ELSE 0 
    END AS validator_block_apy_percentage
  FROM
    validator_rewards_by_epoch r
  JOIN
    total_staked_by_epoch ts ON r.epoch = ts.epoch
  LEFT JOIN
    validator_staked_by_epoch vs ON r.epoch = vs.epoch AND r.vote_account = vs.vote_account
),
base_data AS (
  SELECT
    vote_account,
    epoch,
    SUM(validator_apy_percentage) AS validator_apy_percentage
  FROM
    validator_apy_by_epoch
    GROUP BY
    epoch, vote_account
)

SELECT
  vote_account,
  MAX(IF(epoch = '777', validator_apy_percentage, NULL)) AS validator_apy_percentage_777,
  MAX(IF(epoch = '776', validator_apy_percentage, NULL)) AS validator_apy_percentage_776,
    MAX(IF(epoch = '775', validator_apy_percentage, NULL)) AS validator_apy_percentage_775,
    MAX(IF(epoch = '774', validator_apy_percentage, NULL)) AS validator_apy_percentage_774,
    MAX(IF(epoch = '773', validator_apy_percentage, NULL)) AS validator_apy_percentage_773,
    MAX(IF(epoch = '772', validator_apy_percentage, NULL)) AS validator_apy_percentage_772,
    MAX(IF(epoch = '771', validator_apy_percentage, NULL)) AS validator_apy_percentage_771,
    MAX(IF(epoch = '770', validator_apy_percentage, NULL)) AS validator_apy_percentage_770,
    MAX(IF(epoch = '769', validator_apy_percentage, NULL)) AS validator_apy_percentage_769,
    MAX(IF(epoch = '768', validator_apy_percentage, NULL)) AS validator_apy_percentage_768,
    MAX(IF(epoch = '767', validator_apy_percentage, NULL)) AS validator_apy_percentage_767
FROM
  base_data
GROUP BY
  vote_account
ORDER BY
  vote_account DESC

apy.sql

WITH validator_rewards_by_epoch AS (
  WITH validator_inflation AS (
    SELECT 
      CAST(epoch AS STRING) AS epoch,
      vote_account,
      CAST(amount AS NUMERIC) AS validator_inflation_amount
    FROM 
      `data-store-406413.mainnet_beta_stakes.rewards_validators_inflation`
    WHERE 
      -- epoch 758 is the first where we can find data about inflation 
      CAST(epoch AS INT64) >= 758
  ),

  validator_mev AS (
    SELECT 
      CAST(epoch AS STRING) AS epoch,
      vote_account,
      CAST(amount AS NUMERIC) AS validator_mev_amount
    FROM 
      `data-store-406413.mainnet_beta_stakes.rewards_validators_mev`
    WHERE 
      CAST(epoch AS INT64) >= 758
  ),

  validator_blocks AS (
    SELECT 
      CAST(epoch AS STRING) AS epoch,
      vote_account,
      identity_account,
      CAST(amount AS NUMERIC) AS validator_blocks_amount
    FROM 
      `data-store-406413.mainnet_beta_stakes.rewards_validators_blocks`
    WHERE 
      CAST(epoch AS INT64) >= 758
  ),

  -- Rewards that need to be mapped from stake_account to vote_account
  stake_inflation_with_vote AS (
    SELECT 
      CAST(r.epoch AS STRING) AS epoch,
      r.stake_account,
      CAST(r.amount AS NUMERIC) AS stake_inflation_amount,
      s.vote_account
    FROM 
      `data-store-406413.mainnet_beta_stakes.rewards_inflation` r
    JOIN 
      `data-store-406413.mainnet_beta_stakes.stakes` s
      ON r.stake_account = s.stake_account AND r.epoch = s.epoch
    WHERE 
      CAST(r.epoch AS INT64) >= 758
  ),

  stake_mev_with_vote AS (
    SELECT 
      CAST(r.epoch AS STRING) AS epoch,
      r.stake_account,
      CAST(r.amount AS NUMERIC) AS stake_mev_amount,
      s.vote_account
    FROM 
      `data-store-406413.mainnet_beta_stakes.rewards_mev` r
    JOIN 
      `data-store-406413.mainnet_beta_stakes.stakes` s
      ON r.stake_account = s.stake_account AND r.epoch = s.epoch
    WHERE 
      CAST(r.epoch AS INT64) >= 758
  ),

  stake_inflation_by_validator AS (
    SELECT 
      epoch,
      vote_account,
      SUM(stake_inflation_amount) AS total_stake_inflation_amount
    FROM 
      stake_inflation_with_vote
    GROUP BY 
      epoch, vote_account
  ),

  stake_mev_by_validator AS (
    SELECT 
      epoch,
      vote_account,
      SUM(stake_mev_amount) AS total_stake_mev_amount
    FROM 
      stake_mev_with_vote
    GROUP BY 
      epoch, vote_account
  ),

  validator_rewards AS (
    SELECT 
      CAST(COALESCE(vi.epoch, vm.epoch, vb.epoch, si.epoch, sm.epoch) AS STRING) AS epoch,
      COALESCE(vi.vote_account, vm.vote_account, vb.vote_account, si.vote_account, sm.vote_account) AS vote_account,
      vb.identity_account,
      (
        COALESCE(CAST(vi.validator_inflation_amount AS NUMERIC), 0) + 
        COALESCE(CAST(vm.validator_mev_amount AS NUMERIC), 0) + 
        COALESCE(CAST(vb.validator_blocks_amount AS NUMERIC), 0) + 
        COALESCE(CAST(si.total_stake_inflation_amount AS NUMERIC), 0) + 
        COALESCE(CAST(sm.total_stake_mev_amount AS NUMERIC), 0)
      ) AS total_rewards_lamports,
      (
        COALESCE(CAST(vb.validator_blocks_amount AS NUMERIC), 0)
      ) AS block_rewards_lamports
    FROM 
      validator_inflation vi
    FULL OUTER JOIN 
      validator_mev vm ON vi.vote_account = vm.vote_account AND vi.epoch = vm.epoch
    FULL OUTER JOIN 
      validator_blocks vb ON COALESCE(vi.vote_account, vm.vote_account) = vb.vote_account AND COALESCE(vi.epoch, vm.epoch) = vb.epoch
    FULL OUTER JOIN 
      stake_inflation_by_validator si ON COALESCE(vi.vote_account, vm.vote_account, vb.vote_account) = si.vote_account AND COALESCE(vi.epoch, vm.epoch, vb.epoch) = si.epoch
    FULL OUTER JOIN 
      stake_mev_by_validator sm ON COALESCE(vi.vote_account, vm.vote_account, vb.vote_account, si.vote_account) = sm.vote_account AND COALESCE(vi.epoch, vm.epoch, vb.epoch, si.epoch) = sm.epoch
  )
  SELECT * FROM validator_rewards
),

total_staked_by_epoch AS (
  SELECT
    CAST(epoch AS STRING) AS epoch,
    SUM(CAST(active AS NUMERIC) + CAST(deactivating AS NUMERIC)) / 1e9 AS total_staked_sol
  FROM
    `data-store-406413.mainnet_beta_stakes.stakes`
  WHERE
    CAST(epoch AS INT64) >= 758
  GROUP BY
    epoch
),

validator_staked_by_epoch AS (
  SELECT
    CAST(epoch AS STRING) AS epoch,
    vote_account,
    SUM(CAST(active AS NUMERIC) + CAST(deactivating AS NUMERIC)) / 1e9 AS validator_staked_sol
  FROM
    `data-store-406413.mainnet_beta_stakes.stakes`
  WHERE
    CAST(epoch AS INT64) >= 758
  GROUP BY
    epoch, vote_account
),

validator_apy_by_epoch AS (
  SELECT
    r.epoch,
    r.vote_account,
    r.total_rewards_lamports / 1e9 AS validator_rewards_sol,
    r.block_rewards_lamports / 1e9 AS validator_block_rewards_sol,
    vs.validator_staked_sol,
    ts.total_staked_sol,
    -- Calculate APY with safeguards against numeric overflow
    CASE 
      WHEN vs.validator_staked_sol > 0 THEN 
        CASE
          -- If per-epoch return is too high, use a simplified calculation to avoid overflow
          WHEN (r.total_rewards_lamports / 1e9) / vs.validator_staked_sol > 0.1 THEN 
            ((r.total_rewards_lamports / 1e9) / vs.validator_staked_sol) * 185 * 100
          ELSE
            (POW(1 + LEAST((r.total_rewards_lamports / 1e9) / vs.validator_staked_sol, 0.1), 185) - 1) * 100
        END
      ELSE 0 
    END AS validator_apy_percentage,
    CASE 
      WHEN vs.validator_staked_sol > 0 THEN 
        CASE
          WHEN (r.block_rewards_lamports / 1e9) / vs.validator_staked_sol > 0.1 THEN 
            ((r.block_rewards_lamports / 1e9) / vs.validator_staked_sol) * 185 * 100
          ELSE
            (POW(1 + LEAST((r.block_rewards_lamports / 1e9) / vs.validator_staked_sol, 0.1), 185) - 1) * 100
        END
      ELSE 0 
    END AS validator_block_apy_percentage
  FROM
    validator_rewards_by_epoch r
  JOIN
    total_staked_by_epoch ts ON r.epoch = ts.epoch
  LEFT JOIN
    validator_staked_by_epoch vs ON r.epoch = vs.epoch AND r.vote_account = vs.vote_account
)

SELECT
  epoch,
  total_staked_sol,
  SUM(validator_rewards_sol) AS total_rewards_sol,
  SUM(validator_rewards_sol) / total_staked_sol AS epoch_yield,
  CASE
    WHEN SUM(validator_rewards_sol) / total_staked_sol > 0.1 THEN 
      (SUM(validator_rewards_sol) / total_staked_sol) * 185 * 100
    ELSE
      (POW(1 + LEAST(SUM(validator_rewards_sol) / total_staked_sol, 0.1), 185) - 1) * 100
  END AS network_apy_percentage,
  SUM(validator_block_rewards_sol) AS total_block_rewards_sol,
  SUM(validator_block_rewards_sol) / total_staked_sol AS block_epoch_yield,
  CASE
    WHEN SUM(validator_block_rewards_sol) / total_staked_sol > 0.1 THEN 
      (SUM(validator_block_rewards_sol) / total_staked_sol) * 185 * 100
    ELSE
      (POW(1 + LEAST(SUM(validator_block_rewards_sol) / total_staked_sol, 0.1), 185) - 1) * 100
  END AS network_block_apy_percentage,
  
  -- Validator APY distribution statistics
  MAX(validator_apy_percentage) AS max_validator_apy,
  MIN(validator_apy_percentage) AS min_validator_apy,
  AVG(validator_apy_percentage) AS avg_validator_apy,
  APPROX_QUANTILES(validator_apy_percentage, 100)[OFFSET(10)] AS validator_apy_p10,
  APPROX_QUANTILES(validator_apy_percentage, 100)[OFFSET(25)] AS validator_apy_p25,
  APPROX_QUANTILES(validator_apy_percentage, 100)[OFFSET(50)] AS validator_apy_median,
  APPROX_QUANTILES(validator_apy_percentage, 100)[OFFSET(75)] AS validator_apy_p75,
  APPROX_QUANTILES(validator_apy_percentage, 100)[OFFSET(90)] AS validator_apy_p90,
  APPROX_QUANTILES(validator_apy_percentage, 100)[OFFSET(95)] AS validator_apy_p95,
  APPROX_QUANTILES(validator_apy_percentage, 100)[OFFSET(96)] AS validator_apy_p96,
  APPROX_QUANTILES(validator_apy_percentage, 100)[OFFSET(97)] AS validator_apy_p97,
  APPROX_QUANTILES(validator_apy_percentage, 100)[OFFSET(98)] AS validator_apy_p98,
  APPROX_QUANTILES(validator_apy_percentage, 100)[OFFSET(99)] AS validator_apy_p99,
  STDDEV(validator_apy_percentage) AS validator_apy_stddev,
  
  -- Block rewards APY distribution statistics
  MAX(validator_block_apy_percentage) AS max_block_apy,
  MIN(validator_block_apy_percentage) AS min_block_apy,
  AVG(validator_block_apy_percentage) AS avg_block_apy,
  APPROX_QUANTILES(validator_block_apy_percentage, 100)[OFFSET(10)] AS block_apy_p10,
  APPROX_QUANTILES(validator_block_apy_percentage, 100)[OFFSET(25)] AS block_apy_p25,
  APPROX_QUANTILES(validator_block_apy_percentage, 100)[OFFSET(50)] AS block_apy_median,
  APPROX_QUANTILES(validator_block_apy_percentage, 100)[OFFSET(75)] AS block_apy_p75,
  APPROX_QUANTILES(validator_block_apy_percentage, 100)[OFFSET(90)] AS block_apy_p90,
  APPROX_QUANTILES(validator_block_apy_percentage, 100)[OFFSET(95)] AS block_apy_p95,
  APPROX_QUANTILES(validator_block_apy_percentage, 100)[OFFSET(96)] AS block_apy_p96,
  APPROX_QUANTILES(validator_block_apy_percentage, 100)[OFFSET(97)] AS block_apy_p97,
  APPROX_QUANTILES(validator_block_apy_percentage, 100)[OFFSET(98)] AS block_apy_p98,
  APPROX_QUANTILES(validator_block_apy_percentage, 100)[OFFSET(99)] AS block_apy_p99,
  STDDEV(validator_block_apy_percentage) AS block_apy_stddev
FROM
  validator_apy_by_epoch
WHERE validator_apy_percentage > 0 AND validator_block_apy_percentage > 0
GROUP BY
  epoch, total_staked_sol
ORDER BY
  epoch DESC