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

            20230930 * 220000 <= rev_timestamp AND 

            rev_timestamp < 20231201 * 2200000  

      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

      JOIN linktarget ON tl_target_id = lt_id AND

                         lt_title = 'Wiki_for_school_2023' AND

                         lt_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