Skip to content

Instantly share code, notes, and snippets.

View ochaloup's full-sized avatar

chalda ochaloup

32 followers · 8 following
View GitHub Profile
@ochaloup
ochaloup / vote account apy
Last active September 9, 2025 13:48
huge apy
epoch vote_account apy_scientific total_active_lamports total_rewards_lamports validator_inflation_rewards stakers_inflation_rewards_lamports
----- -------------------------------------------- -------------- --------------------- ---------------------- --------------------------- ----------------------------------
779 6H9J5xtcqGwh2hd2GpBHfvrnDicWk8GtvpnypH7piktA 4.81e+882 1000000 68123423142 <null> <null>
783 CiTYUYPAPHdcri5yEfsmqVcs54J6j8X1QaiFLgYqMVe 1.59e+394 8719484 1247374241 <null> <null>
783 ange1wwDfuSc5Row98UuqUp7AamCDGntfy16mnWSeUH 4.17e+01 1313949860150 31202838502 <null> <null>
783 Dd3TkbM6Yntz9ETLf7fmCME5RNW9yGwaMvpDhY4Exk1i 4.23e+27 4119303941 1716427973
@ochaloup
ochaloup / 1. block-rewards-flipside.sql
Created September 5, 2025 16:28
FlipSide block rewards data investigation
-- 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
@ochaloup
ochaloup / 1. fact_validators
Last active September 5, 2025 16:10
Snowflake investigation on validator identity switching
SELECT * FROM solana.gov.fact_validators
WHERE vote_pubkey = 'oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY' and epoch >= 830
ORDER BY epoch DESC;
EPOCH NODE_PUBKEY VOTE_PUBKEY ACTIVE_STAKE
840 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 98717320
839 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 98717320
836 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
835 HbPxQBkW4x3HaZsixZNQj3aUm8p5esRCjJieg4C9Jeyd oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
834 5BdZZ16w5jqrqJXWiZSWL8MB6v8GPVdGp3EHGaGBfuzF oDDiLXv87uRfbAB8PZthCtQyqof2Jomv7fpTeoBp6AY 85485258112653
@ochaloup
ochaloup / bonds-settlement-program-accounts-rpc-query.sh
Created June 9, 2025 13:34
Find validator bonds settlement by stake authority value
// discriminator settlement [55, 11, 219, 33, 36, 136, 40, 182]: AD24AwEsvU5
curl $RPC_URL -X POST -H "Content-Type: application/json" -d '
{
"jsonrpc": "2.0",
"id": 1,
"method": "getProgramAccounts",
"params": [
"vBoNdEvzMrSai7is21XgVYik65mqtaKXuSdMBJ1xkW4",
{
"encoding": "base64",
@ochaloup
ochaloup / select apy.sql
Last active June 18, 2025 14:25
Select APY
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;
@ochaloup
ochaloup / apy filtered.sql
Last active May 21, 2025 13:41
bq etl stakes apy select
WITH selected_validators AS (
SELECT vote_account FROM UNNEST([
'EdGevanAjM8a6Gg9KxBVrmVdZAUGAZ9xaVd7t9R4H2x',
'Haz7b47sZBpxh9SwggGndN3fAyNQ1S949BPdxWXS3ab6',
'juicQdAnksqZ5Yb8NQwCLjLWhykvXGktxnQCDvMe6Nx',
'5iJDEVRi1nMLwKAWhYbEokZnvBAe15rgFaHGkggVEP9z',
'51JBzSTU5rAM8gLAVQKgp4WoZerQcSqWC7BitBzgUNAm',
'LAKEuKJQYVFpf4vyjX7iuf9ajHo3k9FiyewYKf6VxPV',
'ErvMUdtMC7AX55zKdYSyy4DnWNCrTsWn5GwprSG7ocnx',
'FQwewNXahV7MiZcLpY6p1xhUs2acVGQ3U5Xxc7FzV571', -- blockdaemon
@ochaloup
ochaloup / pnpm-publish.md
Created April 23, 2025 12:20
PNPM publish

On how to test the CLI will work properly when released.

  • Checking what are files to be published

    pnpm publish --dry-run

  • Checking how the publish data looks like

    

    • 




  


  




    


          
    

      

        
        

          

              @ochaloup
          

          

            
                ochaloup
                / block_rewards_solana.sh
            
            

              Last active
              December 4, 2024 15:04
            

              
                Getting block rewards data from Solana ror an epoch
              
          

        

      

        

  

    
    


        



  


  

        

          
          export RPC_URL=...
        

        

          
          

        

        

          
          # --- Finding the list of blocks that were created in epoch
        

        

          
          SLOTS_PER_EPOCH=432000
        

        

          
          START_BLOCK=302400000
        

        

          
          EPOCH=$(($START_BLOCK/$SLOTS_PER_EPOCH))
        

        

          
          echo "Epoch: $EPOCH"
        

        

          
          I=0
        

        

          
          is_end=false
        

        

          
          while ! $is_end; do
        

  





    


  




    


          
    

      

        
        

          

              @ochaloup
          

          

            
                ochaloup
                / flipside claimsettlement tx fee query.sql
            
            

              Last active
              June 10, 2025 11:10
            

              
                flipside spending tx fees query
              
          

        

      

        

  

    
    


        



  


  

        

          
          -- block first/end: https://github.com/ochaloup/solana-list-epoch-boundaries
        

        

          
          WITH claim_settlements AS (select
        

        

          
                  floor(block_id/432000) AS epoch,
        

        

          
                  sum(pre_balances[0])- sum(post_balances[0]) as spending,
        

        

          
                from solana.core.fact_transactions,
        

        

          
                  LATERAL FLATTEN(input => instructions) ixs,
        

        

          
                where 1=1
        

        

          
                  and ixs.value:programId = 'vBoNdEvzMrSai7is21XgVYik65mqtaKXuSdMBJ1xkW4'
        

        

          
                  -- tx fee payer is at the first account key index 0
        

        

          
                  and account_keys[0]:pubkey = 'BNFeevU8uB8xtMSVXMDddzLQvPdKoV82S8dSWVv1KQre'
        

  





    


  




    


          
    

      

        
        

          

              @ochaloup
          

          

            
                ochaloup
                / 01 get-discriminator.sh
            
            

              Last active
              October 24, 2024 05:52
            

              
                Marinade Liquid Stakng Program - Delayed Ticket
              
          

        

      

        

  

    
    


        



  


  

        

          
          # gettting anchor discriminator (8 bytes at start of the Solana anchor generated account)
        

        

          
          git clone https://github.com/marinade-finance/liquid-staking-program
        

        

          
          cd liquid-staking-program
        

        

          
          anchor expand
        

        

          
          grep -e 'Discriminator.*TicketAccountData' -A 3 .anchor/expanded-macros/marinade-finance/marinade-finance-*
        

        

          
          

        

        

          
          

        

        

          
          >         impl anchor_lang::Discriminator for TicketAccountData {
        

        

          
          >            const DISCRIMINATOR: [u8; 8] = [133, 77, 18, 98, 211, 1, 231, 3];