Fork of
CEE Spring 2020 UkWiki
by AntonProtsiuk (WMUA)
This query is marked as a draft
This query has been published
by AntonProtsiuk (WMUA).
SQL
x
USE ukwiki_p;
SET SESSION group_concat_max_len = 10000;
SELECT @rownum := @rownum + 1 AS rank,
IFNULL(actor_name, 'Всього'),
created,
total_len,
articles
FROM
(
SELECT
CONCAT('[[Користувач:', actor_name, '|', actor_name, ']]') as actor_name,
COUNT(1) AS created,
SUM(article.page_len) as total_len,
GROUP_CONCAT(
DISTINCT
-- [[Назва статті]]
CONCAT('[[',
REPLACE(article.page_title, '_', ' '), -- замінюємо у назві '_' на ' '
']]', ' (', article.page_len, ')')
-- список впорядкований за назвою статті
ORDER BY article.page_len DESC
-- розділений пробілом
SEPARATOR ' · '
) AS articles
FROM page article
JOIN revision
ON rev_page = article.page_id AND
rev_parent_id = 0 AND
20220126 * 1000000 <= rev_timestamp AND
rev_timestamp < 20220201 * 10000000
JOIN page talk
ON talk.page_title = article.page_title AND
talk.page_namespace IN (0, 1)
JOIN templatelinks ON
tl_from = talk.page_id AND
tl_title = 'Вікімарафон_2022' AND
tl_namespace = 10
join actor on revision.rev_actor = actor_id and actor_user is not null
WHERE
article.page_namespace = 0 AND
article.page_is_redirect = 0
GROUP BY rev_actor WITH ROLLUP) t,
(SELECT @rownum := -1) r
ORDER BY created DESC, total_len 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.