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('[[Користувач:', actor_name, '|', ']]') 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

 --         20161001 * 1000000 <= rev_timestamp  AND 

 --         rev_timestamp < 20161101 * 1000000

      JOIN actor

        ON rev_actor = actor_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 IN (

                             'Wiki_for_school-week-new'

                           ) AND

                           tl_namespace = 10

    WHERE

      article.page_namespace = 0 AND

      article.page_is_redirect = 0

    GROUP BY actor_name WITH ROLLUP) t,

  (SELECT @rownum := -1) r

ORDER BY created DESC, total_len DESC