Fork of CEE Spring 2020 UkWiki by AntonProtsiuk (WMUA)
This query is marked as a draft This query has been published by AntonProtsiuk (WMUA).

SQL

x
 
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
            20220126 * 1000000 <= rev_timestamp AND 
            rev_timestamp < 20220201 * 10000000  
      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 = 'Вікімарафон_2022' AND
                           tl_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
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...