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.

Checking query status...