Fork of
Lviv-week-new
by ViraMotorko (WMUA)
This query is marked as a draft
This query has been published
by ViraMotorko (WMUA).
SQL
x
USE ukwiki_p;
SET SESSION group_concat_max_len = 10000;
SELECT @rownum := @rownum + 1 AS rank,
IFNULL(user_name, 'Всього'),
created,
total_len,
articles
FROM
(
SELECT
CONCAT('[[Користувач:', rev_user_text, '|', ']]') as user_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
20161104 * 1000000 <= rev_timestamp AND
rev_timestamp < 20161202 * 1000000
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 = 'Football_incubator_5000-week-new' AND
tl_namespace = 10
WHERE
article.page_namespace = 0 AND
article.page_is_redirect = 0
GROUP BY rev_user_text 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.