Query editor
withbase as (SELECTget_href(get_chain_explorer('solana') || '/account/' || account_arguments[6] , account_arguments[6] ) as stake_account, account_arguments[6] as stake_account_raw, get_href(get_chain_explorer('solana') || '/account/' || account_arguments[7] , account_arguments[7] ) as vote_account, account_arguments[7] as vote_account_raw, bal.sol_balance, tx_id, c.block_time, row_number() over (partition by account_arguments[6] order by c.block_time desc) as latest_validatorFROM (SELECT *FROM solana.instruction_callsWHERE executing_account = 'SPoo1Ku8WFXoNDMHPsrGSTSG1Y47rzgn41SLUNakuHy'AND contains(account_arguments, '6iQKfEyhr3bZMotVkW6beNZz5CPAkiwvgV2CTje9pVSS') --JitoSOL authorityAND bytearray_substring(data, 1, 1) = 0x01 --add validator-- AND bytearray_substring(data, 1, 1) = 0x02 --remove validatorAND tx_success) cLEFT JOIN solana_utils.latest_balances as bal ON bal.address = c.account_arguments[6]AND sol_balance > 1AND cardinality(account_arguments) > 6-- AND account_arguments[6] = 'DvJ7vLLmtuDuZa274Z16xBQCd2JZHXBUVDuEpsj8sHZW'-- AND account_arguments[7] = 'juicQdAnksqZ5Yb8NQwCLjLWhykvXGktxnQCDvMe6Nx'order by 2 desc), get_commissions as (SELECT*, row_number() over (partition by recipient order by block_slot desc) as latest_rewardsFROM solana.rewardsWHERE recipient IN (SELECt vote_account_raw FROM base)AND block_time > now() - interval '30' day)SELECTb.stake_account, sol_balance, r_stake.rewards as stake_fees_earned_sol, b.vote_account, r_vote.rewards as vote_fees_earned_sol, cast(c.commission as double)/100 as commission, b.block_time as delegated_atFROM base bLEFT JOIN solana_utils.total_rewards r_stake ON r_stake.recipient = b.stake_account_raw AND r_stake.reward_type = 'Staking'LEFT JOIN solana_utils.total_rewards r_vote ON r_vote.recipient = b.vote_account_raw AND r_vote.reward_type = 'Voting'LEFT JOin get_commissions c ON c.recipient = b.vote_account_raw AND latest_rewards = 1WHERE latest_validator = 1and sol_balance > 1000order by sol_balance desc