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,
    REGEXP_SUBSTR(REGEXP_SUBSTR(REGEXP_SUBSTR(rc1.rc_comment, "^.*?[*].*?[*]/"), "[|](.*?)[*]/"), "[A-Za-z0-9\-]+") AS lang1,
    rc1.rc_user_text as rc1_user_text,
    rc1.rc_comment as rc1_comment, 
    REGEXP_SUBSTR(REGEXP_SUBSTR(REGEXP_SUBSTR(rc2.rc_comment, "^.*?[*].*?[*]/"), "[|](.*?)[*]/"), "[A-Za-z0-9\-]+") AS lang2,    
    rc2.rc_user_text as rc2_user_text,    
    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,
    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 REGEXP_SUBSTR(rc1.rc_comment, "^.*?[*].*?[*]/") = REGEXP_SUBSTR(rc2.rc_comment, "^.*?[*].*?[*]/")

-- group by rc1.rc_id
group by t
order by rc1.rc_cur_id, rc1.rc_id, rc2.rc_id
SQL

Checking query status...
