/* Overall reviews, accepts, declines, rejects */

select DATE(rc_timestamp) AS date,count(rc_id) as reviews,

        SUM(comment_text like "%Publishing accepted%") as accepts,

        SUM(comment_text like "Declining submission:%") as declines,

        SUM(comment_text like "Rejecting submission:%") as rejects,

        concat(round(SUM(comment_text like "%Publishing accepted%") * 100 / count(rc_id), 1), "%") as "accept %",

        concat(round(SUM(comment_text like "Declining submission:%") * 100 / count(rc_id), 1), "%") as "decline %",

        concat(round(SUM(comment_text like "Rejecting submission:%") * 100 / count(rc_id), 1), "%") as "reject %"

from recentchanges_userindex

left join comment on rc_comment_id = comment_id

where (rc_namespace = 118 or rc_namespace = 2) and (rc_type < 5) 

  and (comment_text like "Declining submission:%" or comment_text like "Rejecting submission:%" or comment_text like "%Publishing accepted%")

  and ( MONTH(rc_timestamp) = 11 )

  and ( YEAR(rc_timestamp) = 2023 )

;

​

/* Probable minimum deletions */

select SUM(comment_text like "% has been declined (%") + SUM(comment_text like "% has been rejected (%") - SUM(comment_text like "Declining submission:%") - SUM(comment_text like "Rejecting submission:%") as declined_then_deleted

from recentchanges_userindex

left join comment on rc_comment_id = comment_id

where (rc_namespace = 118 or rc_namespace = 2 or rc_namespace = 3) and (rc_type < 5) 

  and (comment_text like "Declining submission:%" or comment_text like "Rejecting submission:%" or comment_text like '% has been declined (%'  or comment_text like '% has been rejected (%')

  and ( MONTH(rc_timestamp) = 11 )

  and ( YEAR(rc_timestamp) = 2023 )  

order by declined_then_deleted desc;

​

/* Total accepts check */

select actor_name as editor, COUNT(rev_actor) as accepts,

       (select COUNT(rc_id) from recentchanges_userindex where rc_actor = actor_id ) as recent_edits

from revision_userindex

left join actor on rev_actor = actor_id

where (rev_page = 21063913)

  and ( MONTH(rev_timestamp) = 11 )

  and ( YEAR(rev_timestamp) = 2023 ) 

group by rev_actor 

order by accepts desc, recent_edits;

​

/* Daily reviews, accepts, declines, rejects */

select DATE(rc_timestamp) AS date,count(rc_id) as reviews,

        SUM(comment_text like "%Publishing accepted%") as accepts,

        SUM(comment_text like "Declining submission:%") as declines,

        SUM(comment_text like "Rejecting submission:%") as rejects,

        concat(round(SUM(comment_text like "%Publishing accepted%") * 100 / count(rc_id), 1), "%") as "accept %",

        concat(round(SUM(comment_text like "Declining submission:%") * 100 / count(rc_id), 1), "%") as "decline %",

        concat(round(SUM(comment_text like "Rejecting submission:%") * 100 / count(rc_id), 1), "%") as "reject %"

from recentchanges_userindex

left join comment on rc_comment_id = comment_id

where (rc_namespace = 118 or rc_namespace = 2) and (rc_type < 5) 

  and (comment_text like "Declining submission:%" or comment_text like "Rejecting submission:%" or comment_text like "%Publishing accepted%")

  and ( MONTH(rc_timestamp) = 11 )

  and ( YEAR(rc_timestamp) = 2023 )

group by date desc;

​

​