self-categorized categories - Commons

SQL

# Query from https://en.wikipedia.org/wiki/Wikipedia:Database_reports/Self-categorized_categories/Configuration

SELECT
  REPLACE(page_title,'_',' ') as category_name,
  cat_pages as cat_size,
  if (cat_pages = 1, "-", if (cat_pages = cat_subcats, "S", if(cat_pages = cat_files-1, "F", "S+F"))) as cat_content,
  page_len as text_size,
  actor_name as last_user,
  left(rev_timestamp, 12) as last_edit,
  DATEDIFF(NOW(),rev_timestamp) as age,
  length(page_title) as len_name,
  page_is_new as is_new,
  page_id
FROM
  revision
  JOIN (
        SELECT
          page_id, page_title, page_len, page_is_new
FROM page
JOIN categorylinks
ON cl_to = page_title
WHERE page_id = cl_from
AND page_namespace = 14
AND page_id <> 3617544    
  ) AS pagetmp ON rev_page = pagetmp.page_id
  JOIN actor ON rev_actor = actor_id
  JOIN category ON cat_title = pagetmp.page_title
  AND rev_timestamp = (
    SELECT
      MAX(rev_timestamp)
    FROM
      revision AS last
    WHERE
      last.rev_page = pagetmp.page_id
  )

ORDER BY
  page_id DESC
SQL

Checking query status...
