Untitled query #87097

SQL

WITH user_activity(ua_name, ua_first_edit, ua_last_edit) AS
(SELECT user_name,
        (SELECT MIN(rev_timestamp) FROM revision_userindex WHERE rev_actor = actor_id),
        (SELECT MAX(rev_timestamp) FROM revision_userindex WHERE rev_actor = actor_id)
 FROM pagelinks
 JOIN linktarget ON lt_id = pl_target_id AND lt_namespace = 2
 JOIN user ON user_name = REPLACE(lt_title, '_', ' ')
 JOIN actor_user ON actor_user = user_id
 WHERE pl_from = 31538486 -- [[Wikipedia:List of Wikipedians by number of edits/1-1000]], but with the right kind of dash
)
SELECT ua_name,
       CAST(ua_first_edit AS DATETIME) as first_edit,
       CAST(ua_last_edit AS DATETIME) as last_edit,
       CONCAT(FLOOR(DATEDIFF(ua_last_edit, ua_first_edit) / 365),
              ' years ',
              DATEDIFF(ua_last_edit, ua_first_edit) % 365,
              ' days ') AS date_diff
FROM user_activity
WHERE ua_last_edit < '20231014';
SQL

Checking query status...
