Fork of Urdu-Ukrainian-week-new by Ilya
This query is marked as a draft This query has been published by Ilya.

SQL

x
 
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('[[Користувач:', rev_user_text, '|', ']]') 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
            20170128 * 1000000 <= rev_timestamp  AND 
            rev_timestamp < 20170131 * 1000000
    WHERE
      article.page_namespace = 0 AND
      article.page_is_redirect = 0
    GROUP BY rev_user_text 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...