SQL
AخA
/*
Executed in 135.04 seconds as of Wed, 28 Aug 2024 12:17:23 UTC: 2426 rows
Executed in 29.82 seconds as of Sat, 14 Sep 2024 12:37:09 UTC: 5 rows
*/
SELECT
page_title as category,
cat_pages as cat_size,
cat_content,
page_len as text_size,
actor_name as last_user,
left(rev_timestamp,12) as last_edit,
length(page_title) as len_name,
page_is_new as is_new,
"" as wikidata,
page_id,
DATEDIFF(NOW(),rev_timestamp) as age
FROM
revision
JOIN actor ON rev_actor = actor_id
JOIN (
SELECT
page_title, cat_pages,
if (cat_pages = 0, "-", if (cat_pages = cat_subcats, "S", if(cat_pages = cat_files, "F", "S+F"))) as cat_content,
page_len, page_is_new, page_id
FROM
category
JOIN (
SELECT
page_title, page_id, page_len, page_is_new, page_touched, COUNT(cl2.cl_to) as ct
FROM
categorylinks as cl1,
page,
categorylinks as cl2
WHERE
cl1.cl_to = 'Uses_of_Wikidata_Infobox_with_no_item'
AND cl1.cl_type = 'subcat'
AND cl1.cl_from = page_id
AND page_namespace = 14
AND page_is_redirect = 0
AND cl2.cl_from = page_id
AND cl2.cl_type = "subcat"
GROUP BY page_title, page_id, page_len,page_is_new, page_touched
HAVING ct = 1
) AS pagetmp ON cat_title = page_title ) AS pagetmp ON rev_page = pagetmp.page_id
AND rev_timestamp = (
SELECT
MAX(rev_timestamp)
FROM
revision AS last
WHERE
last.rev_page = pagetmp.page_id
)
ORDER BY page_id DESC
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.