Android app SuggestedEdits examples: captions edited, last 30 days

SQL

use commonswiki_p;

select 
--    user_name,
--    user_editcount,
 --   DATEDIFF(STR_TO_DATE(rc1.rc_timestamp, "%Y%m%d%H%i%s"), STR_TO_DATE(user_registration, "%Y%m%d%H%i%s")) as user_age, -- user age in days
    floor(rc1.rc_timestamp/1000000) as rc_day,
    substring(rc1.rc_title, 1, 40) as title_cut,
    rc1.rc_user_text as rc1_user_text,
    REGEXP_SUBSTR(REGEXP_SUBSTR(REGEXP_SUBSTR(rc1.rc_comment, "^.*?[*].*?[*]/"), "[|](.*?)[*]/"), "[A-Za-z0-9\-]+") AS lang1,
    rc1.rc_comment as rc1_comment, 
    rc2.rc_user_text as rc2_user_text,    
    REGEXP_SUBSTR(REGEXP_SUBSTR(REGEXP_SUBSTR(rc2.rc_comment, "^.*?[*].*?[*]/"), "[|](.*?)[*]/"), "[A-Za-z0-9\-]+") AS lang2,    
    rc2.rc_comment as rc2_comment
from 
	change_tag as ct1, 
    change_tag_def as ctd1, 
    recentchanges_compat as rc1 
    	LEFT JOIN recentchanges_compat as rc2 ON 
        	rc1.rc_cur_id=rc2.rc_cur_id 
            AND rc1.rc_id<rc2.rc_id 
            AND rc1.rc_user!=rc2.rc_user
            AND rc1.rc_source=rc2.rc_source
	    LEFT JOIN change_tag as ct2 ON
        	rc1.rc_id=ct2.ct_rc_id
            AND ct2.ct_tag_id=506, -- 506 = "mw-reverted"
    user
where 
	rc1.rc_id=ct1.ct_rc_id 
    and ct1.ct_tag_id=ctd1.ctd_id 
    and ctd1.ctd_name like "apps-suggested-edits" 
    and rc1.rc_user=user_id
    and rc1.rc_comment like "%wbsetlabel%" 
    and rc2.rc_comment like "%wbsetlabel%"
    and ct2.ct_rc_id IS NULL
	-- lang1 = lang2     
    and REGEXP_SUBSTR(REGEXP_SUBSTR(REGEXP_SUBSTR(rc1.rc_comment, "^.*?[*].*?[*]/"), "[|](.*?)[*]/"), "[A-Za-z0-9\-]+") 
    	= REGEXP_SUBSTR(REGEXP_SUBSTR(REGEXP_SUBSTR(rc2.rc_comment, "^.*?[*].*?[*]/"), "[|](.*?)[*]/"), "[A-Za-z0-9\-]+")

order by rc1.rc_cur_id, rc1.rc_id, rc2.rc_id
SQL

Checking query status...
