select

fv.*,

dep.platform

from ethereum.beacon_chain.fact_validators fv

left join ethereum.beacon_chain.ez_deposits dep

on fv.pubkey=dep.pubkey

where fv.validator_status='active_ongoing'

, active_validators_in_slot as (

select

slot_number,

count(distinct pubkey) as total_validators

from ethereum.beacon_chain.fact_validators

where validator_status='active_ongoing'

group by slot_number

, combined as (

select

val.slot_number,

val.platform,

count(distinct val.pubkey) as validators,

max(active_validators_in_slot.total_validators) as total_validators

from validator_table_with_label val

join active_validators_in_slot

on val.slot_number=active_validators_in_slot.slot_number

where val.platform!='not labeled'

group by val.slot_number,val.platform

)

, percentage_validators_table as (

select

*,

100*validators/total_validators as percentage_share_validators,

from combined

order by percentage_share_validators desc