Edit span of linked users

Timestamps of the first and last currently-live edits, and the time between those, for each of the users linked from [[User:WhatamIdoing/Sandbox 3]].

For [[WP:RAQ#Age of dormant accounts]] circa 24 October 2024.

SQL

WITH user_activity(ua_name, ua_first_edit, ua_last_edit) AS
(
  SELECT REPLACE(actor_name, '_', ' '), MIN(rev_timestamp), MAX(rev_timestamp)
  FROM pagelinks
  JOIN page ON page_id = pl_from and page_namespace = 2 AND page_title = 'WhatamIdoing/Sandbox_3'
  JOIN linktarget ON lt_id = pl_target_id AND lt_namespace = 2
  JOIN actor_revision ON actor_name = REPLACE(lt_title, '_', ' ')
  JOIN revision_userindex ON rev_actor = actor_id
  GROUP BY actor_name
)
SELECT ua_name,
       CAST(ua_first_edit AS DATETIME) as first_live_edit,
       CAST(ua_last_edit AS DATETIME) as last_live_edit,
       FLOOR(DATEDIFF(ua_last_edit, ua_first_edit) / 365.25) AS diff_years,
       FLOOR(DATEDIFF(ua_last_edit, ua_first_edit) / (365.25 / 12)) % 12 AS diff_months,
       FLOOR(DATEDIFF(ua_last_edit, ua_first_edit) % (365.25 / 12)) AS diff_days
FROM user_activity;

SQL

Checking query status...