Fork of
Vinnytsia2017-week-improve
by Шиманський Василь
This query is marked as a draft
This query has been published
by Шиманський Василь.
SQL
x
USE ukwiki_p;
SET SESSION group_concat_max_len = 10000;
SELECT @rownum := @rownum + 1 AS rank,
IFNULL(article, 'Всього'),
user_number,
size_increase,
users
FROM
(
SELECT
CONCAT('[[', REPLACE(page_title, '_', ' '), ']]') as article,
COUNT(1) AS user_number,
SUM(user_size_increase) as size_increase,
GROUP_CONCAT(
DISTINCT
REPLACE(
CONCAT(
'[[Користувач:', rev_user_text, '|', ']]',
' (', user_size_increase ,')')
, '_', ' ')
ORDER BY user_size_increase DESC, rev_user_text
SEPARATOR ' · '
) AS users
FROM
(SELECT
rev.rev_user_text,
article.page_title,
SUM(rev.rev_len) - SUM(parent_rev.rev_len) AS user_size_increase
FROM page article
-- отримати версії статей
JOIN revision rev
ON rev.rev_page = article.page_id AND
20170901 * 1000000 <= rev_timestamp AND
rev_timestamp < 20170918 * 1000000
-- разом із попередніми версіями
JOIN revision parent_rev
ON parent_rev.rev_id = rev.rev_parent_id
-- і на сторінці обговорення чи у статі
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 = 'Rivne-week-improve' AND
tl_namespace = 10
WHERE
article.page_namespace = 0 AND
article.page_is_redirect = 0
GROUP BY rev_user_text, article.page_title) r
GROUP BY article WITH ROLLUP) t,
(SELECT @rownum := -1) r
ORDER BY size_increase DESC, user_number 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.