Parcel #ifyg91f1jec0yjd
Created by Anonymous
Public
Created April 11, 2025 Expires in 18 days
Loading editor...
with constants as (
select
-- MEV bot addresses
'DwYVzJaAW683T474NP2nAe7S84cLrbhsUWqCcKko9Lr8' as mev_bot_1,
'7w8LrUrxUS4kcR8rpRcZqzVUfjMq7efssjyxWU6aB39s' as mev_bot_2,
-- Validator addresses
'6dwKX2BK1JowEVXvKemcfw2arNeTe6RHYrs4FomxSVPw' as validator_1,
'7bLCyBuFdPFYbBuztRvqrX9e13Dt6mfpoxdnMDhXvZaV' as validator_2,
'F9Sq9BxVPCBG4UMU1XAF8JBSeKhyWQdLv2PPizy1xQZx' as validator_3,
-- Vote account addresses
'BT8LZUvQVwFHRGw2Dwv7UeqDUq7btfjegLpuz5bwgziD' as vote_1,
'9fgw3MScN9xDUTLZJaiqTBF9BmoASJGWmJCdUKSjzsXs' as vote_2,
'36MVUhntTiTY7nsLyoCdRj4wbs2rvw2nPEZiM5XCkJLb' as vote_3,
-- Marinade settlement accounts
'5afnqCqESnDFYYXh4pKjrDbPSpbwZN3k5DA8cRhyQRiE' as settlement_1,
'88peqqYQALFmoBzzg5Zjiw6o1VxJ6NDKdYvR7kr9F1Cz' as settlement_2,
'rnKeLoKPagsepqEwW9QrYQWS1bi51p7qYn4k5gaGmrU' as settlement_3,
-- MEV Capital withdrawer
'HiDF7GaTWmDvU489CHzBKP8pUYRttkkcVtabzFsqivfd' as mev_capital_withdrawer,
-- Mint accounts
'GHPCChGqtKf4sFaN1wPPCapcweKXBBngB3hF7D6nT29e' as mint_account_1,
'DQ97nu7t7fbhAtZUyam8EzNsxUzw5bgEE5seBfevPwRK' as mint_account_2,
-- Program IDs
'4R3gSG8BpU4t19KYj8CfnbtRpnT8gtk4dvTHxVRwc2r7' as jito_tip_program,
'vBoNdEvzMrSai7is21XgVYik65mqtaKXuSdMBJ1xkW4' as marinade_program,
-- Threshold values
324863999 as min_block_id,
300974766 as mint_min_block_id,
751 as min_epoch
),
mev_bot_balance_changes AS (
SELECT
ep.epoch as epoch,
sb.block_id as block_id,
sb.account_address as mev_bot,
SUM(sb.balance - sb.pre_balance) as collectedTips
FROM
solana.core.fact_sol_balances sb
INNER JOIN solana.gov.dim_epoch ep ON block_id BETWEEN ep.start_block AND ep.end_block
CROSS JOIN constants c
WHERE
sb.account_address IN(c.mev_bot_1, c.mev_bot_2)
AND sb.block_id >= c.min_block_id
GROUP BY
epoch, block_id, mev_bot
),
fee_rewards AS (
SELECT
epoch_earned AS epoch,
pubkey,
SUM(reward_amount_sol) AS total_fee_rewards
FROM
solana.gov.fact_rewards_fee
CROSS JOIN constants c
WHERE
epoch_earned > c.min_epoch
AND pubkey in (c.validator_1, c.validator_2, c.validator_3)
GROUP BY
epoch_earned, pubkey
),
jito_tip_dist_program AS (
SELECT
DISTINCT ep.epoch,
ei.signers[0] AS validator,
ei.instruction['parsed']['info']['newAccount'] AS tip_distribution_account
FROM
solana.core.fact_events_inner ei
INNER JOIN solana.gov.dim_epoch ep ON ei.block_id BETWEEN ep.start_block AND ep.end_block
CROSS JOIN constants c
WHERE
ei.instruction_program_id = c.jito_tip_program
AND ei.event_type = 'createAccount'
AND ei.signers[0] IN (c.validator_1, c.validator_2, c.validator_3)
AND ei.block_id >= c.min_block_id
AND ep.epoch > c.min_epoch
),
balance_change_info AS (
SELECT
ep.epoch,
sb.block_id,
sb.tx_id,
sb.account_address,
sb.balance,
sb.balance - sb.pre_balance AS collectedTips
FROM
solana.core.fact_sol_balances sb
INNER JOIN (SELECT DISTINCT tip_distribution_account FROM jito_tip_dist_program) jtd
ON sb.account_address = jtd.tip_distribution_account
INNER JOIN solana.gov.dim_epoch ep ON sb.block_id BETWEEN ep.start_block AND ep.end_block
CROSS JOIN constants c
WHERE
sb.balance > sb.pre_balance
AND sb.block_id >= c.min_block_id
),
collected_mev_jito AS (
SELECT
jdp.validator AS pubkey,
bci.epoch as epoch,
SUM(bci.collectedTips) AS collected_jito_tips
FROM
balance_change_info bci
INNER JOIN jito_tip_dist_program jdp
ON jdp.tip_distribution_account = bci.account_address AND jdp.epoch = bci.epoch
GROUP BY
bci.epoch, jdp.validator
),
mev_tips as (
SELECT
bci.epoch as epoch,
bci.mev_bot as mev_bot,
SUM(bci.collectedTips) AS collected_mev_tips
FROM
mev_bot_balance_changes bci
WHERE
bci.collectedTips > 0
GROUP BY
bci.epoch, bci.mev_bot
),
validator_stake_accounts AS (
SELECT
stake_pubkey,
vote_pubkey,
epoch,
authorized_withdrawer,
active_stake
FROM
solana.gov.fact_stake_accounts
CROSS JOIN constants c
WHERE
vote_pubkey IN (c.vote_1, c.vote_2, c.vote_3)
AND epoch - activation_epoch > CASE
WHEN authorized_withdrawer = c.mev_capital_withdrawer THEN 0
WHEN activation_epoch = 766 THEN 1
ELSE 0
END
),
validator_stakes AS (
SELECT
epoch,
CASE
WHEN vote_pubkey = c.vote_1 THEN c.validator_1
WHEN vote_pubkey = c.vote_2 THEN c.validator_2
WHEN vote_pubkey = c.vote_3 THEN c.validator_3
ELSE vote_pubkey
END AS pubkey,
CASE
WHEN vote_pubkey = c.vote_1 THEN c.mev_bot_1
WHEN vote_pubkey = c.vote_2 THEN c.mev_bot_1
WHEN vote_pubkey = c.vote_3 THEN c.mev_bot_2
ELSE vote_pubkey
END AS mev_bot,
SUM(active_stake) AS total_stake,
SUM(
CASE
WHEN authorized_withdrawer = c.mev_capital_withdrawer THEN active_stake
ELSE 0
END
) AS total_mev_capital_stake
FROM
validator_stake_accounts
CROSS JOIN constants c
GROUP BY
epoch, pubkey, mev_bot
),
total_stake_by_epoch AS (
SELECT
epoch,
mev_bot,
SUM(total_stake) AS epoch_total_stake
FROM
validator_stakes
GROUP BY
epoch, mev_bot
),
mev_tips_per_validator AS (
SELECT
s.epoch,
s.pubkey,
s.total_stake,
t.collected_mev_tips * s.total_stake / e.epoch_total_stake AS mev_tips
FROM
validator_stakes s
JOIN total_stake_by_epoch e ON s.epoch = e.epoch AND e.mev_bot = s.mev_bot
LEFT JOIN mev_tips t ON s.epoch = t.epoch AND t.mev_bot = s.mev_bot
),
marinade_cost AS (
SELECT
CAST(DECODED_ARGS['initSettlementArgs']['epoch'] AS INTEGER) as epoch,
CASE
WHEN DECODED_ACCOUNTS[1]['pubkey'] = c.settlement_1 THEN c.validator_1
WHEN DECODED_ACCOUNTS[1]['pubkey'] = c.settlement_2 THEN c.validator_2
WHEN DECODED_ACCOUNTS[1]['pubkey'] = c.settlement_3 THEN c.validator_3
ELSE DECODED_ACCOUNTS[1]['pubkey']
END AS pubkey,
CAST(DECODED_ARGS['initSettlementArgs']['maxTotalClaim'] AS INTEGER) / 1000000000 as marinade_cost
FROM
solana.core.ez_events_decoded
CROSS JOIN constants c
WHERE
EVENT_TYPE = 'initSettlement'
AND PROGRAM_ID = c.marinade_program
AND DECODED_ACCOUNTS[1]['pubkey'] IN (c.settlement_1, c.settlement_2, c.settlement_3)
AND block_id > c.min_block_id
),
profit_calculation_base AS (
SELECT
vas.epoch,
vas.pubkey,
CASE
WHEN vas.pubkey = c.validator_1 THEN 'mev capital 0'
WHEN vas.pubkey = c.validator_2 THEN 'marinade ny 0'
WHEN vas.pubkey = c.validator_3 THEN 'endersol'
ELSE 'unknown'
END as name,
COALESCE(mc.marinade_cost, 0) as marinade_cost,
COALESCE(mp.mev_tips, 0) as mev_tips,
COALESCE(fr.total_fee_rewards, 0) AS block_rewards,
COALESCE(jt.collected_jito_tips, 0) AS jito_tips,
COALESCE(vas.total_mev_capital_stake, 0) as mev_capital_stake,
COALESCE(vas.total_stake, 0) as total_stake
FROM
validator_stakes vas
CROSS JOIN constants c
LEFT JOIN mev_tips_per_validator mp ON vas.epoch = mp.epoch AND mp.pubkey = vas.pubkey
LEFT JOIN fee_rewards fr ON vas.epoch = fr.epoch AND vas.pubkey = fr.pubkey
LEFT JOIN collected_mev_jito jt ON vas.epoch = jt.epoch AND vas.pubkey = jt.pubkey
LEFT JOIN marinade_cost mc ON vas.epoch = mc.epoch AND vas.pubkey = mc.pubkey
),
validator_data AS (
SELECT
pcb.epoch,
pcb.name as pubkey,
pcb.marinade_cost,
pcb.mev_tips,
pcb.block_rewards,
pcb.jito_tips,
pcb.mev_capital_stake,
pcb.total_stake,
-- MEV Capital revenue calculations
(pcb.mev_capital_stake / NULLIF(pcb.total_stake, 0)) * pcb.mev_tips as mev_capital_mev_tips,
(pcb.mev_capital_stake / NULLIF(pcb.total_stake, 0)) * pcb.block_rewards as mev_capital_block_rewards,
(pcb.mev_capital_stake / NULLIF(pcb.total_stake, 0)) * pcb.jito_tips as mev_capital_jito_tips,
-- MEV Capital profit calculations with tiered rates
(
pcb.mev_tips * (
-- Profit for the first 100k of stake at 50%
(LEAST(pcb.mev_capital_stake, 100000) / NULLIF(pcb.total_stake, 0)) * 0.5 +
-- Profit for stake above 100k at 60%
(CASE WHEN pcb.mev_capital_stake > 100000
THEN ((pcb.mev_capital_stake - 100000) / NULLIF(pcb.total_stake, 0))
ELSE 0 END) * 0.6
)
) AS mev_capital_mev_profit,
(
pcb.block_rewards * (
-- Profit for the first 100k of stake at 50%
(LEAST(pcb.mev_capital_stake, 100000) / NULLIF(pcb.total_stake, 0)) * 0.5 +
-- Profit for stake above 100k at 60%
(CASE WHEN pcb.mev_capital_stake > 100000
THEN ((pcb.mev_capital_stake - 100000) / NULLIF(pcb.total_stake, 0))
ELSE 0 END) * 0.6
)
) AS mev_capital_block_rewards_profit,
-- Jito profit is all to MEV Capital - here's the fixed line:
((pcb.mev_capital_stake / NULLIF(pcb.total_stake, 0)) * pcb.jito_tips) as mev_capital_jito_profit,
-- APY calculations
POWER(1 + ((pcb.mev_tips / NULLIF(pcb.total_stake, 0)) * (365.0 / 2) / 182.5), 182.5) - 1 as total_mev_tip_apy,
POWER(1 + ((pcb.jito_tips / NULLIF(pcb.total_stake, 0)) * (365.0 / 2) / 182.5), 182.5) - 1 as total_jito_tip_apy,
POWER(1 + ((pcb.block_rewards / NULLIF(pcb.total_stake, 0)) * (365.0 / 2) / 182.5), 182.5) - 1 as total_block_rewards_apy,
CASE WHEN pcb.mev_capital_stake > 0
THEN POWER(1 + (((
pcb.mev_tips * (
(LEAST(pcb.mev_capital_stake, 100000) / NULLIF(pcb.total_stake, 0)) * 0.5 +
(CASE WHEN pcb.mev_capital_stake > 100000
THEN ((pcb.mev_capital_stake - 100000) / NULLIF(pcb.total_stake, 0))
ELSE 0 END) * 0.6
)
) / pcb.mev_capital_stake) * (365.0 / 2) / 182.5), 182.5) - 1
ELSE 0
END as mev_capital_mev_tip_apy,
CASE WHEN pcb.mev_capital_stake > 0
THEN POWER(1 + ((((pcb.mev_capital_stake / NULLIF(pcb.total_stake, 0)) * pcb.jito_tips) / pcb.mev_capital_stake) * (365.0 / 2) / 182.5), 182.5) - 1
ELSE 0
END as mev_capital_jito_tip_apy,
CASE WHEN pcb.mev_capital_stake > 0
THEN POWER(1 + (((
pcb.block_rewards * (
(LEAST(pcb.mev_capital_stake, 100000) / NULLIF(pcb.total_stake, 0)) * 0.5 +
(CASE WHEN pcb.mev_capital_stake > 100000
THEN ((pcb.mev_capital_stake - 100000) / NULLIF(pcb.total_stake, 0))
ELSE 0 END) * 0.6
)
) / pcb.mev_capital_stake) * (365.0 / 2) / 182.5), 182.5) - 1
ELSE 0
end as mev_capital_block_rewards_apy,
0.075 as base_apy,
-- Business metrics
(pcb.mev_tips + pcb.block_rewards -
(pcb.mev_tips * (
(LEAST(pcb.mev_capital_stake, 100000) / NULLIF(pcb.total_stake, 0)) * 0.5 +
(CASE WHEN pcb.mev_capital_stake > 100000
THEN ((pcb.mev_capital_stake - 100000) / NULLIF(pcb.total_stake, 0))
ELSE 0 END) * 0.6
)) -
(pcb.block_rewards * (
(LEAST(pcb.mev_capital_stake, 100000) / NULLIF(pcb.total_stake, 0)) * 0.5 +
(CASE WHEN pcb.mev_capital_stake > 100000
THEN ((pcb.mev_capital_stake - 100000) / NULLIF(pcb.total_stake, 0))
ELSE 0 END) * 0.6
))
) as marinade_revenue,
(pcb.mev_tips + pcb.block_rewards -
(pcb.mev_tips * (
(LEAST(pcb.mev_capital_stake, 100000) / NULLIF(pcb.total_stake, 0)) * 0.5 +
(CASE WHEN pcb.mev_capital_stake > 100000
THEN ((pcb.mev_capital_stake - 100000) / NULLIF(pcb.total_stake, 0))
ELSE 0 END) * 0.6
)) -
(pcb.block_rewards * (
(LEAST(pcb.mev_capital_stake, 100000) / NULLIF(pcb.total_stake, 0)) * 0.5 +
(CASE WHEN pcb.mev_capital_stake > 100000
THEN ((pcb.mev_capital_stake - 100000) / NULLIF(pcb.total_stake, 0))
ELSE 0 END) * 0.6
)) - pcb.marinade_cost
) as marinade_profit,
(pcb.mev_tips + pcb.block_rewards -
(pcb.mev_tips * (
(LEAST(pcb.mev_capital_stake, 100000) / NULLIF(pcb.total_stake, 0)) * 0.5 +
(CASE WHEN pcb.mev_capital_stake > 100000
THEN ((pcb.mev_capital_stake - 100000) / NULLIF(pcb.total_stake, 0))
ELSE 0 END) * 0.6
)) -
(pcb.block_rewards * (
(LEAST(pcb.mev_capital_stake, 100000) / NULLIF(pcb.total_stake, 0)) * 0.5 +
(CASE WHEN pcb.mev_capital_stake > 100000
THEN ((pcb.mev_capital_stake - 100000) / NULLIF(pcb.total_stake, 0))
ELSE 0 END) * 0.6
))
) as vintara_revenue,
(pcb.mev_tips + pcb.block_rewards -
(pcb.mev_tips * (
(LEAST(pcb.mev_capital_stake, 100000) / NULLIF(pcb.total_stake, 0)) * 0.5 +
(CASE WHEN pcb.mev_capital_stake > 100000
THEN ((pcb.mev_capital_stake - 100000) / NULLIF(pcb.total_stake, 0))
ELSE 0 END) * 0.6
)) -
(pcb.block_rewards * (
(LEAST(pcb.mev_capital_stake, 100000) / NULLIF(pcb.total_stake, 0)) * 0.5 +
(CASE WHEN pcb.mev_capital_stake > 100000
THEN ((pcb.mev_capital_stake - 100000) / NULLIF(pcb.total_stake, 0))
ELSE 0 END) * 0.6
)) - pcb.marinade_cost
) as vintara_profit
FROM
profit_calculation_base pcb
),
epoch_times AS (
SELECT
d.epoch,
fb_start.block_timestamp AS start_time,
fb_end.block_timestamp AS end_time
FROM
solana.gov.dim_epoch d
LEFT JOIN solana.core.fact_blocks fb_start ON d.start_block = fb_start.block_id
LEFT JOIN solana.core.fact_blocks fb_end ON d.end_block = fb_end.block_id
WHERE
d.epoch > 695
),
epoch_prices AS (
SELECT
et.epoch as price_epoch,
AVG(ez.price) AS avg_sol_price
FROM
epoch_times et
JOIN solana.price.ez_prices_hourly ez
ON ez.hour BETWEEN et.start_time AND COALESCE(et.end_time, CURRENT_TIMESTAMP())
WHERE
ez.is_native = true
GROUP BY
et.epoch
),
mint_balance_changes AS (
SELECT
ep.epoch as epoch,
sb.block_id as block_id,
SUM(sb.balance - sb.pre_balance) as collectedTips
FROM
solana.core.fact_sol_balances sb
INNER JOIN solana.gov.dim_epoch ep ON block_id BETWEEN ep.start_block AND ep.end_block
CROSS JOIN constants c
WHERE
sb.account_address IN (c.mint_account_1, c.mint_account_2)
AND sb.block_id >= c.mint_min_block_id
GROUP BY
epoch, block_id
),
mint_validator_returns AS (
SELECT
bci.epoch as epoch,
'mint validator' as pubkey,
0 as marinade_cost,
SUM(bci.collectedTips) AS mev_tips,
0 as block_rewards,
0 as jito_tips,
0 as mev_capital_stake,
0 as mev_capital_mev_tips,
0 as mev_capital_block_rewards,
0 as mev_capital_jito_tips,
0 as total_stake,
0 as mev_capital_mev_profit,
0 as mev_capital_block_rewards_profit,
0 as mev_capital_jito_profit,
0 as total_mev_tip_apy,
0 as total_jito_tip_apy,
0 as total_block_rewards_apy,
0 as mev_capital_mev_tip_apy,
0 as mev_capital_jito_tip_apy,
0 as mev_capital_block_rewards_apy,
0 as base_apy,
0 as mev_capital_total_apy,
0 as marinade_revenue,
0 as marinade_profit,
mev_tips / 2 AS vintara_revenue,
mev_tips / 2 AS vintara_profit
FROM
mint_balance_changes bci
WHERE
bci.collectedTips > 0
GROUP BY
bci.epoch
)
-- Final query adding mev_capital_total_apy and USD profit values
SELECT
vd.*,
(vd.base_apy + vd.mev_capital_block_rewards_apy + vd.mev_capital_jito_tip_apy + vd.mev_capital_mev_tip_apy) as mev_capital_total_apy,
vd.vintara_profit * p.avg_sol_price as vintara_profit_usd
FROM
validator_data vd
LEFT JOIN epoch_prices p ON p.price_epoch = vd.epoch
UNION ALL
SELECT
mvr.*,
mvr.vintara_profit * p.avg_sol_price as vintara_profit_usd
FROM
mint_validator_returns mvr
LEFT JOIN epoch_prices p ON p.price_epoch = mvr.epoch
ORDER BY
epoch DESC