SELECT user_name, ug1.ug_group, log_timestamp, ipb_id, ipb_expiry

FROM user_groups AS ug1

LEFT JOIN user ON ug1.ug_user = user_id

LEFT JOIN ipblocks ON user_id = ipb_user

LEFT JOIN actor ON user_id = actor_user

LEFT JOIN user_groups AS ug2 ON ug1.ug_user = ug2.ug_user AND ug2.ug_group = 'bot'

LEFT JOIN (

  SELECT log_actor, log_timestamp

  FROM logging

  WHERE log_type = 'rights'

      AND log_action = 'autopromote'

      AND log_timestamp < 20211201131000 # https://phabricator.wikimedia.org/T295518#7540665

) logging ON log_actor = actor_id

WHERE ug1.ug_group IN ('extendedconfirmed', 'sysop')

    AND ug2.ug_user IS NULL

    AND (

      ug1.ug_group = 'sysop'

      OR (ug1.ug_group = 'extendedconfirmed' AND log_timestamp IS NOT NULL)

    )

GROUP BY ug1.ug_user