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