with dataset as

(select block_id, date_trunc('day', block_timestamp) AS day,

event_attributes:address as validator,

CASE WHEN (event_attributes:missed_blocks > 100) THEN 'Offline' ELSE 'Online' END as online_offline,

event_attributes

from terra.transitions

where block_timestamp > CURRENT_DATE-180 and event = 'liveness'

)

select day, validator, online_offline, COUNT(validator) as count_on_off from dataset

GROUP BY day, Validator, Online_offline order by validator)

select day, AVG(count_on_off) from dataset_2 where Online_offline = 'Offline' group by day