Wikimarathon 2024 Ukraine [with newbies]

SQL

USE ukwiki_p;
SET SESSION group_concat_max_len = 10000;

SELECT  @rownum := @rownum + 1 AS rank,
  IFNULL(actor_name, 'Всього'),
  newbie
FROM
  (
    SELECT
      CONCAT(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,
    IF(user_registration > 20240120 * 1000000, 'так', 'ні') as newbie
    FROM page AS article
    JOIN revision
      ON rev_page = article.page_id AND
      rev_parent_id = 0    AND
      20240125 * 1000000 <= rev_timestamp AND 
      rev_timestamp < 20240205 * 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
      JOIN linktarget ON tl_target_id = lt_id AND
      lt_title = 'Вікімарафон_2023' AND
      lt_namespace = 10
    join actor on revision.rev_actor = actor_id and actor_user is not null
    join `user` on actor_user = user_id
    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
SQL

Checking query status...
