wikimartisor 2025 results

This query is marked as a draft This query has been published by Gikü.
SQL

# Premiul pentru cele mai multe articole noi (Care au minim 3.500 de octeți)
# Stephan Condurachis (10)
/*
select a.actor_name as participant, count(r.rev_id) as articles
from revision r inner join actor a on r.rev_actor=a.actor_id
	inner join comment c on r.rev_comment_id=c.comment_id
    inner join page p on p.page_id=r.rev_page
where c.comment_text like '%#_iki__r_i_or%' COLLATE utf8mb3_general_ci and p.page_namespace=0
    and r.rev_timestamp>20250220220000 and r.rev_timestamp<20250315220000
	and (select rev_len from revision where rev_page=r.rev_page order by rev_timestamp desc limit 1)>=3500
    and r.rev_parent_id=0
group by a.actor_name
order by articles desc
*/

# Premiul pentru cele mai multe îmbunătățiri la articole existente (Note și referințe de calitate, reformulări și completări secțiuni noi)
# Midinonesti (856)
/*
select a.actor_name as participant, count(r.rev_id) as edits
from revision r inner join actor a on r.rev_actor=a.actor_id
    inner join comment c on r.rev_comment_id=c.comment_id
    inner join page p on p.page_id=r.rev_page
where c.comment_text like '%#_iki__r_i_or%' COLLATE utf8mb3_general_ci and p.page_namespace=0
    and r.rev_timestamp>20250220220000 and r.rev_timestamp<20250315220000
    and r.rev_parent_id<>0
group by a.actor_name
order by edits desc
*/

# Premiul pentru noii editori Wikipedia cu cele mai multe contribuții la campanie (Editor nou care s-a înscris pe Wikipedia în 2025)
# TrixyA (810)
/*
select a.actor_name as participant, count(r.rev_id) as edits
from revision r inner join actor a on r.rev_actor=a.actor_id
    inner join comment c on r.rev_comment_id=c.comment_id
    inner join user u on a.actor_user=u.user_id
    inner join page p on p.page_id=r.rev_page
where c.comment_text like '%#_iki__r_i_or%' COLLATE utf8mb3_general_ci and p.page_namespace=0
    and r.rev_timestamp>20250220220000 and r.rev_timestamp<20250315220000
    and u.user_registration>=20241231220000
group by a.actor_name
order by edits desc
*/

# Premiul pentru cel mai mult conținut adăugat
# Midinonesti (+242168)
/*
select a.actor_name as participant, sum(r_new.rev_len)-sum(coalesce(r_old.rev_len,0)) as contribution_in_bytes
from revision r_new left join revision r_old on r_new.rev_parent_id=r_old.rev_id
    inner join actor a on r_new.rev_actor=a.actor_id
    inner join comment c on r_new.rev_comment_id=c.comment_id
    inner join page p on p.page_id=r_new.rev_page
where c.comment_text like '%#_iki__r_i_or%' COLLATE utf8mb3_general_ci and p.page_namespace=0
    and r_new.rev_timestamp>20250220220000 and r_new.rev_timestamp<20250315220000
group by a.actor_name
order by contribution_in_bytes desc
*/

# Participanți în Wikimărțișor 2025 # 45

select count(distinct r.rev_actor) as participants
from comment c inner join revision r on c.comment_id=r.rev_comment_id
    inner join page p on p.page_id=r.rev_page
where c.comment_text like '%#_iki__r_i_or%' COLLATE utf8mb3_general_ci and p.page_namespace=0
    and r.rev_timestamp>20250220220000 and r.rev_timestamp<20250315220000


# Conținut total adăugat în Wikimărțișor 2025 # +1124342
/*
select
	(select sum(r.rev_len) from comment c inner join revision r on c.comment_id=r.rev_comment_id
         inner join page p on p.page_id=r.rev_page
     where c.comment_text like '%#_iki__r_i_or%' COLLATE utf8mb3_general_ci and p.page_namespace=0
     and r.rev_timestamp>20250220220000 and r.rev_timestamp<20250315220000
    )-(select sum(rev_len) from revision
     where rev_id in (select r1.rev_parent_id from comment c1 inner join revision r1 on c1.comment_id=r1.rev_comment_id
    		inner join page p1 on p1.page_id=r1.rev_page
         where c1.comment_text like '%#_iki__r_i_or%' COLLATE utf8mb3_general_ci and p1.page_namespace=0
         and r1.rev_timestamp>20250220220000 and r1.rev_timestamp<20250315220000
                     ))
as bytes_added
*/

# Editări realizate în Wikimărțișor 2025 # 3027
/*
select count(r.rev_id) as edits from comment c inner join revision r on c.comment_id=r.rev_comment_id
    inner join page p on p.page_id=r.rev_page
where c.comment_text like '%#_iki__r_i_or%' COLLATE utf8mb3_general_ci and p.page_namespace=0
    and r.rev_timestamp>20250220220000 and r.rev_timestamp<20250315220000
*/

# Articole îmbunătățite în Wikimărțișor 2025 # 1039
/*
select count(distinct r.rev_page) as articles_improved from comment c inner join revision r on c.comment_id=r.rev_comment_id
    inner join page p on p.page_id=r.rev_page
where c.comment_text like '%#_iki__r_i_or%' COLLATE utf8mb3_general_ci and p.page_namespace=0
    and r.rev_timestamp>20250220220000 and r.rev_timestamp<20250315220000
    and r.rev_parent_id<>0
*/

# Articole noi create în Wikimărțișor 2025 # 65
/*
select count(distinct r.rev_page) as articles_created from comment c inner join revision r on c.comment_id=r.rev_comment_id
    inner join page p on p.page_id=r.rev_page
where c.comment_text like '%#_iki__r_i_or%' COLLATE utf8mb3_general_ci and p.page_namespace=0
    and r.rev_timestamp>20250220220000 and r.rev_timestamp<20250315220000
    and r.rev_parent_id=0
*/


###### TESTS BELOW
/*select p.page_title,p.page_namespace from comment c inner join revision r on c.comment_id=r.rev_comment_id
	inner join page p on p.page_id=r.rev_page
where c.comment_text like '%#_iki__r_i_or%' COLLATE utf8mb3_general_ci and p.page_namespace=0
    and r.rev_timestamp>20250220220000 and r.rev_timestamp<20250315220000
    and r.rev_parent_id=0*/

#SHOW COLLATION where collation like 'utf8%'
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.
SQL

Checking query status...
