Skip to content

Instantly share code, notes, and snippets.

@ochaloup

ochaloup/select apy.sql

Last active June 18, 2025 14:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ochaloup/ed127fb24812a127d862500c4409f02d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ochaloup/ed127fb24812a127d862500c4409f02d to your computer and use it in GitHub Desktop.
Download ZIP
Select APY
Raw
select apy.sql
DECLARE minEpoch INT64 DEFAULT 788;
DECLARE maxEpoch INT64 DEFAULT 793;
DECLARE epochsPerYear INT64 DEFAULT 183;
DECLARE stakeAuthorities ARRAY<STRING> DEFAULT [
"STNi1NHDUi6Hvibvonawgze8fM83PFLeJhuGMEXyGps",
"EX1Fs34ajye3BTMSjTkMdZ8P4hb99vQFWzmueqhKGpH6"
];
-- Numbers defined in institutional config subtracted from APY to define fee paid to Marinade and lamports left on validator
DECLARE marinadeApyPercentFee DECIMAL DEFAULT 0;
DECLARE validatorApyPercentRewards DECIMAL DEFAULT 0.5;
WITH selected_validators AS (
SELECT vote_account FROM UNNEST([
'FQwewNXahV7MiZcLpY6p1xhUs2acVGQ3U5Xxc7FzV571', -- Blockdaemon
'9GJmEHGom9eWo4np4L5vC6b6ri1Df2xN8KFoWixvD1Bs', -- Block Logic
'ErvMUdtMC7AX55zKdYSyy4DnWNCrTsWn5GwprSG7ocnx', -- CatalystX - Innovation OnChain
'CAf8jfgqhia5VNrEF4A7Y9VLD3numMq9DVSceq7cPhNY', -- Chainflow
'Chorus6Kis8tFHA7AowrPMcRJk3LbApHTYpgSNXzY5KE', -- Chorus One
'EdGevanAjM8a6Gg9KxBVrmVdZAUGAZ9xaVd7t9R4H2x', -- Edgevana
'9Gko8QZBbV5SrEvHKtQHcMrGGSfgFP3KJUozEGifu25x', -- GateOmega
'FdGcvmbpebUwYA3vSywnagsaC3Tq3pAVmcR6VoxVcdV9', -- GlobalStake
'FCvNkHa4U3yh7AXWGGL2jWLWiSRouR8EtzY5WVTHKTHa', -- H2O Nodes
'Cat8oWQiFfrR3c7BcceTYcpnYCzSWfCPjMXT7mfHXvEP', -- Hashkey Cloud
'51JBzSTU5rAM8gLAVQKgp4WoZerQcSqWC7BitBzgUNAm', -- Kraken/Staked
'LAKEuKJQYVFpf4vyjX7iuf9ajHo3k9FiyewYKf6VxPV', -- LakeStake
'NeodymeDFipD7eA1ShrLJAZTBdHWcFsDB9YkoHshZNk', -- Neodyme
'oRAnGeU5h8h2UkvbfnE5cjXnnAa4rBoaxmS4kbFymSe', -- OrangeFinVentures
'AS3nKBQfKs8fJ8ncyHrdvo4FDT6S8HMRhD75JjCcyr1t', -- Overclock
'juicQdAnksqZ5Yb8NQwCLjLWhykvXGktxnQCDvMe6Nx', -- ProStaking
'5s3vajJvaAbabQvxFdiMfg14y23b2jvK6K2Mw4PYcYK', -- Quicknode
'RBFvvcGPBpgkBYmJGsphoDQJD8sszSuorM7TorWm12Y', -- Rockaway
'EARNynHRWg6GfyJCmrrizcZxARB3HVzcaasvNa8kBS72', -- SolanaCompass
'SLaYv7tCwetrFGbPCRnqpHswG5qqKino78EYpbGF7xY', -- Solayer
'4vqwZsEEEsKtSqqEWbLyFAciWg66jGLP9zrbcZ1Hsrxb', -- Solstice
'5iJDEVRi1nMLwKAWhYbEokZnvBAe15rgFaHGkggVEP9z', -- Stardust Staking
'GNZ1PAAS33davY4Q1BMEpZEpVBtRtGvSpcTH5wYVkkVt', -- Stakeconomy
'7VGU4ZwR1e1AFekqbqv2gvjeg47e1PwMPm4BfLt6rxNk', -- Stakefish
'R1vAoSPFQdCc6wsAEMtxWXjqptSeN1YUiq2Zni1of21', -- Superfast
'Haz7b47sZBpxh9SwggGndN3fAyNQ1S949BPdxWXS3ab6', -- Temporal
'EBVj3uwSKZpqEb1K267JaPxDQhULVqCy6hYeQqjsPh81', -- Titan Analytics
'Ec37CQZjwRgGnuMmUi3BnEBXS5Xa3siakAPxPkHtahSf', -- Validation Cloud
'6hZL2FZim27WkQccMfygvvXH2eow5u3wR6XUJHbMoeWP', -- Xandeum Labs
'9jYFwBfbjYmvasFbJyES9apLJDTkwtbgSDRWanHEvcRw' -- Watchtower
]) AS vote_account
),
staked_sol AS (
WITH institutional_stake AS (
SELECT
stakes.epoch,
stakes.vote_account,
-- stake etl active means 'effective'
SUM(COALESCE(active, 0)) AS institutional_stake
FROM
`data-store-406413.mainnet_beta_stakes.stakes` stakes
WHERE
stakes.epoch BETWEEN minEpoch AND maxEpoch
AND stakes.stake_authority IN UNNEST(stakeAuthorities)
AND vote_account IN (SELECT vote_account FROM selected_validators)
GROUP BY
epoch, stakes.vote_account
),
total_stake AS (
SELECT
stakes.epoch,
stakes.vote_account,
SUM(COALESCE(active, 0)) AS total_stake
FROM
`data-store-406413.mainnet_beta_stakes.stakes` stakes
WHERE
stakes.epoch BETWEEN minEpoch AND maxEpoch
AND vote_account IN (SELECT vote_account FROM selected_validators)
GROUP BY
epoch, stakes.vote_account
)
SELECT
ts.epoch,
ts.vote_account,
ts.total_stake,
iss.institutional_stake
FROM total_stake ts
FULL OUTER JOIN
institutional_stake iss ON ts.vote_account = iss.vote_account AND ts.epoch = iss.epoch
),
validator_rewards_by_epoch AS (
WITH validator_inflation AS (
SELECT
epoch,
vote_account,
amount AS validator_inflation_amount
FROM
`data-store-406413.mainnet_beta_stakes.rewards_validators_inflation`
WHERE 1=1
AND epoch BETWEEN minEpoch AND maxEpoch
AND vote_account IN (SELECT vote_account FROM selected_validators)
),
validator_mev AS (
SELECT
epoch,
vote_account,
amount as validator_mev_amount
FROM
`data-store-406413.mainnet_beta_stakes.rewards_validators_mev`
WHERE 1=1
AND epoch BETWEEN minEpoch AND maxEpoch
AND vote_account IN (SELECT vote_account FROM selected_validators)
),
validator_blocks AS (
SELECT
epoch,
vote_account,
identity_account,
amount AS validator_blocks_amount
FROM
`data-store-406413.mainnet_beta_stakes.rewards_validators_blocks`
WHERE 1=1
AND epoch BETWEEN minEpoch AND maxEpoch
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
rewards.epoch,
rewards.stake_account,
rewards.amount AS stake_inflation_amount,
stakes.vote_account
FROM
`data-store-406413.mainnet_beta_stakes.rewards_inflation` rewards
JOIN
`data-store-406413.mainnet_beta_stakes.stakes` stakes
ON rewards.stake_account = stakes.stake_account AND rewards.epoch = stakes.epoch
WHERE 1=1
AND rewards.epoch BETWEEN minEpoch AND maxEpoch
AND stakes.vote_account IN (SELECT vote_account FROM selected_validators)
),
stake_mev_with_vote AS (
SELECT
rewards.epoch AS epoch,
rewards.stake_account,
rewards.amount AS stake_mev_amount,
stakes.vote_account
FROM
`data-store-406413.mainnet_beta_stakes.rewards_mev` rewards
JOIN
`data-store-406413.mainnet_beta_stakes.stakes` stakes
ON rewards.stake_account = stakes.stake_account AND rewards.epoch = stakes.epoch
WHERE 1=1
AND rewards.epoch BETWEEN minEpoch AND maxEpoch
AND stakes.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
),
-- Finally joining all rewards by vote_account and epoch
validator_rewards AS (
SELECT
COALESCE(vi.epoch, vm.epoch, vb.epoch, si.epoch, sm.epoch) AS epoch,
COALESCE(vi.vote_account, vm.vote_account, vb.vote_account, si.vote_account, sm.vote_account) AS vote_account,
vb.identity_account,
(
SELECT AS STRUCT
ss.total_stake AS total_stake,
ss.institutional_stake AS institutional_stake,
SAFE_DIVIDE(ss.institutional_stake, ss.total_stake) AS stake_ratio
FROM staked_sol ss
WHERE ss.epoch = COALESCE(vi.epoch, vm.epoch, vb.epoch, si.epoch, sm.epoch)
AND ss.vote_account = COALESCE(vi.vote_account, vm.vote_account, vb.vote_account, si.vote_account, sm.vote_account)
) AS stake,
COALESCE(vi.validator_inflation_amount, 0) AS validator_inflation_rewards,
COALESCE(vm.validator_mev_amount, 0) as validator_mev_rewards,
COALESCE(vb.validator_blocks_amount, 0) AS validator_block_rewards,
COALESCE(si.total_stake_inflation_amount, 0) AS staker_inflation_rewards,
COALESCE(sm.total_stake_mev_amount, 0) AS staker_mev_rewards,
(
COALESCE(vi.validator_inflation_amount, 0) +
COALESCE(vm.validator_mev_amount, 0) +
COALESCE(vb.validator_blocks_amount, 0) +
COALESCE(si.total_stake_inflation_amount, 0) +
COALESCE(sm.total_stake_mev_amount, 0)
) AS total_rewards_lamports,
(
COALESCE(vi.validator_inflation_amount, 0) +
COALESCE(vm.validator_mev_amount, 0) +
COALESCE(vb.validator_blocks_amount, 0)
) AS validator_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
)
-- Query all data from the WITH to expose them to the outer world by selecting "'validator_rewards_by_epoch'"
SELECT * FROM validator_rewards
)
SELECT
epoch,
CASE
WHEN SUM(stake.total_stake) > 0 THEN
CASE
WHEN SUM(vr.total_rewards_lamports) / SUM(vr.stake.total_stake) > 0.1 THEN
((SUM(vr.total_rewards_lamports) / 1e9) / (SUM(vr.stake.total_stake) / 1e9)) * epochsPerYear * 100
ELSE
(POW(1 + ((SUM(vr.total_rewards_lamports) / 1e9) / (SUM(vr.stake.total_stake) / 1e9)), epochsPerYear) - 1) * 100
END
ELSE 0
END AS total_apy_percentage,
SUM(vr.stake.institutional_stake / 1e9) AS institutional_stake,
SUM(vr.validator_rewards_lamports / 1e9 * vr.stake.stake_ratio) AS institutional_validator_rewards,
SUM(vr.total_rewards_lamports / 1e9 * vr.stake.stake_ratio) AS institutional_total_rewards,
FROM validator_rewards_by_epoch vr
GROUP BY epoch
ORDER BY epoch ASC
Raw
z flipside example stake
SELECT
block_timestamp::date as block_date,
COUNT(*) as num_transactions
FROM
solana.core.fact_transactions
WHERE
log_messages::string like '%Program log: Instruction: Stake%'
and account_keys::string like '%q1yEkaNeJ9iRXE7BJejSMkT3PF7HsaL1Eu6em9sQYCw%'
and block_timestamp::date >= current_date - {{time_window}}
and block_timestamp::date < current_date
and succeeded = True
GROUP BY
block_date
ORDER BY
block_date desc
Raw
z lj bids
with
txs as (
select
BLOCK_TIMESTAMP,
instructions
from
solana.core.fact_transactions
where
BLOCK_TIMESTAMP between date_trunc('month', dateadd('month', -1, CURRENT_DATE)) and dateadd('day', -1, date_trunc('month', CURRENT_DATE))
-- date_trunc('month', BLOCK_TIMESTAMP) = date_trunc('month', dateadd('month', -1, CURRENT_DATE))
and succeeded = true
-- array_contains(account_keys, 'vBoNdEvzMrSai7is21XgVYik65mqtaKXuSdMBJ1xkW4'::variant)
-- AND BLOCK_TIMESTAMP < date_trunc('month', CURRENT_DATE)
and CAST(LOG_MESSAGES AS STRING) LIKE '%ClaimSettlement%'
)
SELECT
min(BLOCK_TIMESTAMP) period_start,
max(BLOCK_TIMESTAMP) period_end,
sum(utils.udf_hex_to_int(concat(
substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 7) + 1, 2),
substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 6) + 1, 2),
substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 5) + 1, 2),
substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 4) + 1, 2),
substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 3) + 1, 2),
substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 2) + 1, 2),
substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 1) + 1, 2),
substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + 1, 2)
)) / 1e9) as bids
from
txs,
LATERAL FLATTEN(input => instructions) ixs,
WHERE 1=1
and ixs.value:programId = 'vBoNdEvzMrSai7is21XgVYik65mqtaKXuSdMBJ1xkW4'
and '89SrbjbuNyqSqAALKBsKBqMSh463eLvzS4iVWCeArBgB' = utils.udf_hex_to_base58(concat('0x', substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8 + 32) + 1, 2 * 32)))
Raw
z lj bot queries
with
bots as (
select
column1 as name,
column2 as pubkey
from values
('native staking', 'opNS8ENpEMWdXcJUgJCsJTDp7arTXayoBEeBUg6UezP'),
('liquid staking', 'opLSF7LdfyWNBby5o6FT8UFsr2A4UGKteECgtLSYrSm'),
('psr', 'BNFeevU8uB8xtMSVXMDddzLQvPdKoV82S8dSWVv1KQre'),
('tip', 'Tip2wnUSbEw2VQ6m95Mq8QSbMoX6Cmyn5QBaXjTNFeB')
)
select
bots.name as "Bot",
substr(date_trunc('month', block_timestamp), 0, 10) as "Period",
count(*) as "Tx count",
sum(array_size(instructions)) as "Ix count",
avg(array_size(instructions)) as "Avg ix count",
stddev(array_size(instructions)) as "Std Dev ix count",
sum(units_consumed) "Total CUs",
avg(units_consumed) "Avg CUs",
stddev(units_consumed) "Std Dev CUs",
sum(fee) / 1e9 as "Tx costs",
avg(fee) / 1e9 as "Avg tx costs",
stddev(fee) / 1e9 as "Std Dev tx costs",
from
solana.core.fact_transactions
inner join bots on array_contains(bots.pubkey::variant, signers)
where
date_trunc('day', block_timestamp) between '2025-01-01' and '9999-01-01'
group by
bots.name, date_trunc('month', block_timestamp)
order by
date_trunc('month', block_timestamp) desc,
bots.name asc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment