Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
Fork of
Thematic Weeks on UkWP [Template]
by
AntonProtsiuk (WMUA)
This query is marked as a draft
This query has been published
by
AntonProtsiuk (WMUA)
.
Toggle Highlighting
SQL
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 20220201 * 1000000 <= rev_timestamp AND rev_timestamp < 20210222 * 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 = 'Safer_Internet_Week 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
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...