flipside spending tx fees query

flipside claimsettlement tx fee query.sql

-- 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'
        -- ClaimSettlementV2 Anchor IX discriminator: '[188, 53, 132, 151, 88, 50, 52, 238]'
        -- base58: YUs67RekvZs
        -- hexformat: bc358497583234ee
        -- udf_base58_to_hex starts with '0x', taking index 3 to work only with discriminator data
        -- or to use: utils.udf_hex_to_base58(concat('0x', substring(utils.udf_base58_to_hex(ixs.value:data)
        -- and substring(utils.udf_base58_to_hex(ixs.value:data), 3, 16) = 'bc358497583234ee'
        -- start of epoch 685
        and BLOCK_ID >= 685*432000
        -- and SUCCEEDED = TRUE
        group by tx_id, block_id
)
select
  epoch,
  count(*) as number_of_transactions,
  sum(spending) as spending_lamports,
  sum(spending) / 1e9 as spending_sol
  from claim_settlements
  group by epoch
  order by epoch;

fund bond flipside query.sql

with
  fund_ixs as (
    select
      tx_id,
      -- https://github.com/marinade-finance/validator-bonds/blob/main/programs/validator-bonds/src/instructions/bond/fund_bond.rs#L48 - 4th account in ix, index 3
      ixs.value:accounts[3] stake_account,
      ixs.value:accounts[1] bond_account,
      ixs.value:data ix_data,
      account_keys,
      post_balances,
      block_timestamp,
      block_id
    from solana.core.fact_transactions,
      LATERAL FLATTEN(input => instructions) ixs,
    where 1=1
      -- start of epoch 690, block_id: epoch * 432000
      and BLOCK_ID >= 298080000
      and ixs.value:programId = 'vBoNdEvzMrSai7is21XgVYik65mqtaKXuSdMBJ1xkW4'
      -- FundBond Anchor IX discriminator: '[58, 44, 212, 175, 30, 17, 68, 62]'
      -- base58: AjNYrvLyYzh
      and ixs.value:data = 'AjNYrvLyYzh'
      -- vote account: CaraHZBReeNNYAJ326DFsvy41M2p1KWTEoBAwBL6bmWZ
      and bond_account = '3zVyxrxkR2a3oWoBku7CaAG7UW6JS65vqyoTdG1Djig9'
  )
select
  block_timestamp,
  block_id,
  floor(block_id / 432000) as epoch,
  tx_id,
  stake_account,
  bond_account,
  -- filtering by stake_account = accounts.value:pubkey that defines value of account.index
  post_balances[accounts.index] / 1e9 balance,
  ix_data
from
  fund_ixs,
  LATERAL FLATTEN(input => account_keys) accounts
WHERE 1=1
  and stake_account = accounts.value:pubkey
ORDER BY block_timestamp ASC

fund settlement flipside query .sql

with
  fund_ixs as (
    select
      tx_id,
      -- https://github.com/marinade-finance/validator-bonds/blob/main/programs/validator-bonds/src/instructions/settlement/fund_settlement.rs - 5th account in ix, index 4
      -- fundsettlement ix was changed from epoch 679 and stake is at index 5 instead of 4 as before
      CASE
           WHEN BLOCK_ID >= 293328000
          THEN 5
        ELSE 4
      END AS stake_account_idx,
      CASE
           WHEN BLOCK_ID >= 293328000
          THEN 8
        ELSE 7
      END AS split_stake_account_idx,
      ixs.value:accounts[stake_account_idx] stake_account,
      ixs.value:accounts[split_stake_account_idx] split_stake_account,
      ixs.value:accounts[1] bond_account,
      ixs.value:data ix_data,
      account_keys,
      post_balances,
      pre_balances,
      block_timestamp,
      block_id
    from solana.core.fact_transactions,
      LATERAL FLATTEN(input => instructions) ixs,
    where 1=1
      and BLOCK_ID >= 685 * 432000
      and BLOCK_ID < 690 * 432000
      -- vote account: apySoLRzPkGf8EEinWQjAbL6B4frjgx4gG4YePoM9NJ, bond: 523VBk32etGmgkCYg5hCkHVQcCJkX7ix34deaMH9SAGZ
      -- vote account: G1itch7djMvG4vMRToAgs45cVqrLGKFbzrgV4qNDsG1X, bond: Gew6nLmRydzA5MbL5jqmoz2K3hTCvH1wFSQRayyVuUYZ
      -- vote account: CaraHZBReeNNYAJ326DFsvy41M2p1KWTEoBAwBL6bmWZ, bond: 3zVyxrxkR2a3oWoBku7CaAG7UW6JS65vqyoTdG1Djig9
      and bond_account = '3zVyxrxkR2a3oWoBku7CaAG7UW6JS65vqyoTdG1Djig9'
      and ixs.value:programId = 'vBoNdEvzMrSai7is21XgVYik65mqtaKXuSdMBJ1xkW4'
      -- FundSettlement Anchor IX discriminator: '[179, 146, 113, 34, 30, 92, 26, 19]'
      -- base58: X35Gz7Wk1Y6
      and ixs.value:data = 'X35Gz7Wk1Y6'
  )
select
  block_timestamp,
  floor(block_id / 432000) AS epoch,
  block_id,
  tx_id,
  stake_account,
  split_stake_account,
  bond_account,
  pre_balances[accounts.index] / 1e9 stake_account_pre_balance,
  -- filtering by stake_account to accounts.value:pubkey and the .index is the stake account post balance
  post_balances[accounts.index] / 1e9 funded_amount,
  ix_data
from
  fund_ixs,
  LATERAL FLATTEN(input => account_keys) accounts
WHERE 1=1
  and stake_account = accounts.value:pubkey
ORDER BY floor(block_id/432000) ASC