with validators_infos as(

select

epoch,

node_pubkey,

vote_pubkey,

active_stake,

epoch_active,

epoch_credits,

'Frankendancer Version: ' || software_version as software_version

from solana.gov.fact_validators

where

inserted_timestamp > dateadd(day, -{{n_days}}, current_date())

and software_version like '%0.%'

and software_version like '%.20113%'

,time_vote_credit as(

select

vi.software_version,

pe.value:epoch::number as epoch,

(pe.value:previousCredits::number - lag(pe.value:previousCredits::number) over (

partition by va.vote_pubkey

order by pe.value:epoch::number

)) / (432000 * 16) * 100 as effectiveness

from validators_infos as vi

inner join solana.gov.fact_rewards_voting rv

on rv.vote_pubkey = vi.vote_pubkey

and rv.epoch_earned = vi.epoch

inner join solana.gov.fact_vote_accounts va

on va.epoch = rv.epoch_earned

and va.vote_pubkey = rv.vote_pubkey,