Skip to content

Instantly share code, notes, and snippets.

@ochaloup

ochaloup/1. block-rewards-flipside.sql

Created September 5, 2025 16:28
Show Gist options

  • Select an option

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

Select an option

Learn more about clone URLs
Save ochaloup/2d1d59e6b9902e478d7d6baffd3d539d to your computer and use it in GitHub Desktop.
Download ZIP
FlipSide block rewards data investigation
Raw
1. block-rewards-flipside.sql
-- 827
SELECT * FROM solana.gov.fact_validators
WHERE vote_pubkey = 'BT8LZUvQVwFHRGw2Dwv7UeqDUq7btfjegLpuz5bwgziD' and epoch = 827;
-- EMPTY
SELECT * FROM solana.gov.fact_vote_accounts
WHERE vote_pubkey = 'BT8LZUvQVwFHRGw2Dwv7UeqDUq7btfjegLpuz5bwgziD' and epoch >= 823 and epoch <= 827
order by epoch desc;
-- EPOCH VOTE_PUBKEY NODE_PUBKEY AUTHORIZED_VOTER AUTHORIZED_WITHDRAWER
-- 827 BT8LZUvQVwFHRGw2Dwv7UeqDUq7btfjegLpuz5bwgziD 7pnQN2YLCzVr94XHEwC6dCJpBLANhad4sbXwsZMbPnF3 8f8ictfTPjKtJcn8Av8jfqh7uvU3UbDtsreADQTbB1No FHwaQwCr8PMcv9dHx96deVhUbRQGhrQvc2jZTp1uGwyh
-- 826 BT8LZUvQVwFHRGw2Dwv7UeqDUq7btfjegLpuz5bwgziD 8f8ictfTPjKtJcn8Av8jfqh7uvU3UbDtsreADQTbB1No FimxwskHym6UxYbPyTNovPKqnaHnUuSDDXangz7ysUmQ FHwaQwCr8PMcv9dHx96deVhUbRQGhrQvc2jZTp1uGwyh
-- 825 BT8LZUvQVwFHRGw2Dwv7UeqDUq7btfjegLpuz5bwgziD 8f8ictfTPjKtJcn8Av8jfqh7uvU3UbDtsreADQTbB1No FimxwskHym6UxYbPyTNovPKqnaHnUuSDDXangz7ysUmQ FHwaQwCr8PMcv9dHx96deVhUbRQGhrQvc2jZTp1uGwyh
-- 824 BT8LZUvQVwFHRGw2Dwv7UeqDUq7btfjegLpuz5bwgziD FimxwskHym6UxYbPyTNovPKqnaHnUuSDDXangz7ysUmQ 6k8XrwBQhS3LcMGDte72JbBCXH6VGWqpPDJm5Z8w9Aty FHwaQwCr8PMcv9dHx96deVhUbRQGhrQvc2jZTp1uGwyh
-- 823 BT8LZUvQVwFHRGw2Dwv7UeqDUq7btfjegLpuz5bwgziD 6k8XrwBQhS3LcMGDte72JbBCXH6VGWqpPDJm5Z8w9Aty 6k8XrwBQhS3LcMGDte72JbBCXH6VGWqpPDJm5Z8w9Aty FHwaQwCr8PMcv9dHx96deVhUbRQGhrQvc2jZTp1uGwyh
SELECT * FROM solana.gov.fact_validators
WHERE node_pubkey in ('8f8ictfTPjKtJcn8Av8jfqh7uvU3UbDtsreADQTbB1No','7pnQN2YLCzVr94XHEwC6dCJpBLANhad4sbXwsZMbPnF3', 'FHwaQwCr8PMcv9dHx96deVhUbRQGhrQvc2jZTp1uGwyh', 'FimxwskHym6UxYbPyTNovPKqnaHnUuSDDXangz7ysUmQ')
and epoch = 827;
--- EMPTY
SELECT FLOOR(block_id / 432000) epoch, pubkey, count(1), sum(reward_amount_sol) as total_rewards
FROM solana.gov.fact_rewards_fee
WHERE pubkey in ('8f8ictfTPjKtJcn8Av8jfqh7uvU3UbDtsreADQTbB1No','7pnQN2YLCzVr94XHEwC6dCJpBLANhad4sbXwsZMbPnF3', 'FimxwskHym6UxYbPyTNovPKqnaHnUuSDDXangz7ysUmQ') and epoch = 827
group by epoch, pubkey;
-- EPOCH PUBKEY COUNT(1) TOTAL_REWARDS
-- 827 8f8ictfTPjKtJcn8Av8jfqh7uvU3UbDtsreADQTbB1No 420 15.093846007
-- 835
SELECT * FROM solana.gov.fact_validators
WHERE vote_pubkey = 'oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY' and epoch = 835;
-- EPOCH NODE_PUBKEY VOTE_PUBKEY ACTIVE_STAKE
-- 835 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
SELECT * FROM solana.gov.fact_vote_accounts
WHERE vote_pubkey = 'oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY' and epoch >= 831 and epoch <= 835
order by epoch desc;
-- EPOCH VOTE_PUBKEY NODE_PUBKEY AUTHORIZED_VOTER AUTHORIZED_WITHDRAWER
-- 835 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
-- 834 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
-- 833 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF 6e54R866P9Y3k1Bq6Qei3gXCSiyFFqwq9mLhNKJKunk6 BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
-- 832 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF 6e54R866P9Y3k1Bq6Qei3gXCSiyFFqwq9mLhNKJKunk6 BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
-- 831 oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 6e54R866P9Y3k1Bq6Qei3gXCSiyFFqwq9mLhNKJKunk6 3aytWWQzNYC8DSL4ppv78zAEAVCBR745EozeoqNXmoLk BfzkHpxCYnyc4an6X15HxEoBhEAJ2JXxpvHaLrE7yJrb
SELECT * FROM solana.gov.fact_validators
WHERE node_pubkey in ('HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd','5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF')
and epoch = 835;
-- EPOCH NODE_PUBKEY VOTE_PUBKEY ACTIVE_STAKE
-- 835 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
SELECT FLOOR(block_id / 432000) epoch, pubkey, count(1), sum(reward_amount_sol) as total_rewards
FROM solana.gov.fact_rewards_fee
WHERE pubkey in ('HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd','5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF') and epoch = 835
group by epoch, pubkey;
-- EPOCH PUBKEY COUNT(1) TOTAL_REWARDS
-- 835 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF 56 1.252002441
Raw
2. schedule.sh
epoch=835
slot=$((epoch*432000)) # first slot in epoch
# vote account=oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY
# identity=HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd
# identity=5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF
curl $RPC_URL -s -X \
POST -H "Content-Type: application/json" -d '
{
"jsonrpc": "2.0",
"id": 1,
"method": "getLeaderSchedule",
"params": [
'$slot',
{
"commitment": "finalized",
"identity": "'$identity'"
}
]
}
'
# identity=5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF
{"jsonrpc":"2.0","result":{"5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF":[10056,10057,10058,10059,32516,32517,32518,32519,58648,58649,58650,58651,70048,70049,70050,70051,72296,72297,72298,72299,73368,73369,73370,73371,79764,79765,79766,79767,90936,90937,90938,90939,105264,105265,105266,105267,143672,143673,143674,143675,203228,203229,203230,203231,210208,210209,210210,210211,221320,221321,221322,221323,261940,261941,261942,261943,336404,336405,336406,336407,353960,353961,353962,353963,363888,363889,363890,363891,366040,366041,366042,366043,380724,380725,380726,380727,381212,381213,381214,381215,414400,414401,414402,414403,430512,430513,430514,430515]},"id":1}
# identity=HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd
{"jsonrpc":"2.0","result":null,"id":1}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment