Fork of Median account age for EXTCON (2) by Cryptic
This query is marked as a draft This query has been published by Cryptic.

SQL

​x
 
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 ts AS 'Resultset 1' FROM median;
​
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) AS
(
  SELECT ts,
         ROW_NUMBER() OVER (ORDER BY ts)
  FROM active
),
counts(cnt) AS
(
  SELECT COUNT(*)
  FROM ranked
),
median AS
(
  SELECT ts
  FROM ranked 
  WHERE rank in (FLOOR(((SELECT * FROM counts) + 1) / 2), CEIL(((SELECT * FROM counts) + 1) / 2))
)
SELECT ts AS 'Resultset 2' FROM median;
​
WITH active(ts) AS
(
  SELECT COALESCE(UNIX_TIMESTAMP(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
)
SELECT FROM_UNIXTIME(MEDIAN(ts) OVER ()) AS 'Resultset 3'
FROM active
LIMIT 1;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query
All SQL code is licensed under CC0 License.

Checking query status...