Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SELECT validator,
-- Calculate the score for inclusion distance in isolation
COALESCE(SUM(0.875 / optimal_inclusion_distance) FILTER (WHERE optimal_inclusion_distance IS NOT NULL), 0) / (COUNT(*) * 0.00875) AS inclusion_distance_score,
-- Calculate score for correct source in isolation
(COALESCE(SUM(1) FILTER (WHERE correct_source), 0) -
COALESCE(SUM(1) FILTER (WHERE NOT correct_source), 0)) / (COUNT(*) * 0.01) AS source_score,
-- Calculate score for correct target in isolation
(COALESCE(SUM(1) FILTER (WHERE correct_target), 0) -
COALESCE(SUM(1) FILTER (WHERE NOT correct_target), 0)) / (COUNT(*) * 0.01) AS target_score,
-- Calculate score for correct head in isolation
(COALESCE(SUM(1) FILTER (WHERE correct_head), 0) -
COALESCE(SUM(1) FILTER (WHERE NOT correct_head), 0)) / (COUNT(*) * 0.01) AS head_score,
-- Calculate overall score
(COALESCE(SUM(0.875 / optimal_inclusion_distance) FILTER (WHERE optimal_inclusion_distance IS NOT NULL), 0) +
COALESCE(SUM(1) FILTER (WHERE correct_source), 0) -
COALESCE(SUM(1) FILTER (WHERE NOT correct_source), 0) +
COALESCE(SUM(1) FILTER (WHERE correct_target), 0) -
COALESCE(SUM(1) FILTER (WHERE NOT correct_target), 0) +
COALESCE(SUM(1) FILTER (WHERE correct_head), 0) -
COALESCE(SUM(1) FILTER (WHERE NOT correct_head), 0)) / COUNT(*) / 3.875 * 100 AS score
FROM (
SELECT v.f_index AS validator,
MIN(a.f_inclusion_slot) - MIN(bi.f_slot) + 1 AS optimal_inclusion_distance,
MIN(a.f_inclusion_slot) - c.f_slot AS inclusion_distance,
-- Source must be correct to include the attestation
MIN(a.f_inclusion_slot) IS NOT NULL AS correct_source,
bt.f_root IS NOT NULL AS correct_target,
bh.f_root IS NOT NULL AS correct_head
FROM t_validators v
-- Find all the attestations the validator was scheduled for
JOIN t_beacon_committees c ON v.f_index = ANY(c.f_committee)
-- Find the blocks that the produced attestation could have been included in (min slot is used for optimal inclusion distance)
JOIN t_blocks bi ON bi.f_slot > c.f_slot
-- Find the attestations actually produced
LEFT JOIN t_attestations a ON a.f_slot = c.f_slot AND a.f_committee_index = c.f_index AND v.f_index = ANY(a.f_aggregation_indices)
-- Check if the head root matches the last block at or before the attestation slot
LEFT JOIN t_blocks bh ON bh.f_root = a.f_beacon_block_root AND bh.f_slot = (SELECT MAX(b2.f_slot) FROM t_blocks b2 WHERE b2.f_slot <= a.f_slot)
-- Check if the target root matches the last block at or before the first slot of the epoch
LEFT JOIN t_blocks bt ON a.f_target_root = bt.f_root AND bt.f_slot = (SELECT MAX(b3.f_slot) FROM t_blocks b3 WHERE b3.f_slot <= a.f_target_epoch * 32)
-- Filter out attestations within the last 32 slots as they haven't had their full chance to be included
WHERE c.f_slot < (SELECT MAX(b4.f_slot) - 32 FROM t_blocks b4)
-- Filter to the slots and validators we're interested in
AND c.f_slot >= 0 AND c.f_slot <= 1000 AND v.f_index IN (1,2,3,4,5,6)
GROUP BY v.f_index, c.f_slot, c.f_index, bh.f_root, bt.f_root, a.f_target_root, a.f_target_epoch
) x
GROUP BY validator;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment