valdiatorscore.sql

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;