Median account age for EXTCON

Median user_registration of accounts that have either the extendedconfirmed or sysop group (extendedconfirmed is implicit in the latter) that have at least one edit in the last 30 days.

For [[WP:RAQ#Median account age for EXTCON]] circa 19 November 2024.

SQL

WITH active(ts) AS
(
  SELECT COALESCE(user_registration, 0)
  FROM user
  JOIN user_groups ON ug_user = user_id
  JOIN actor_recentchanges ON actor_user = user_id
  WHERE ug_group IN ('extendedconfirmed', 'sysop')
    AND EXISTS (SELECT 1 FROM recentchanges_userindex WHERE rc_actor = actor_id)
  GROUP BY user_name
),
ranked(ts, rank, cnt) AS
(
  SELECT ts,
         ROW_NUMBER() OVER (ORDER BY ts),
         COUNT(ts) OVER ()
  FROM active
),
median AS
(
  SELECT ts
  FROM ranked 
  WHERE rank in (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2))
)
SELECT * FROM median;

SQL

Checking query status...