Android app SuggestedEdits: captions only, last 30 days

Fork of Android app SuggestedEdits: captions only, last 30 days by Zache

This query is marked as a draft This query has been published by Zache.

SQL

use commonswiki_p;

select 
	count(distinct(rc_id)) as edits, 
    sum(ctd2.ctd_name="android app edit") as android_app_edits, -- checksum to see that we seeing right ting
    sum(ctd2.ctd_name="mw-reverted") as reverted_edits, 
    sum(ctd2.ctd_name="mw-manual-revert") as reverts, 
    ctd1.ctd_name, rc_comment, 
    group_concat(distinct(ctd2.ctd_name)) as tags 
from 
	change_tag as ct1, 
    change_tag_def as ctd1, 
    recentchanges_compat, 
    change_tag as ct2, 
    change_tag_def as ctd2 
where 
	rc_id=ct1.ct_rc_id 
    and ct1.ct_tag_id=ctd1.ctd_id 
    and ctd1.ctd_name like "apps-suggested-edits" 
    and ct2.ct_rc_id=rc_id 
    and ctd2.ctd_id=ct2.ct_tag_id 
    and rc_comment like "%label%" 
group by ctd1.ctd_name;

SQL

Checking query status...