SQL
x
use fiwiki_p;
select
sum(1) as c,
log_id,
log_page,
log_title,
log_user_text,
count(distinct(rev_id)) as rev_count,
count(distinct(ar_rev_id)) as ar_rev_count,
max(rev_len) as max_rev_len,
max(ar_len) as max_ar_len,
sum(visualeditor) as ve,
sum(contenttranslation) as cx,
sum(mobile_edit) as me,
log_comment
from (
select
l1.log_id,
l1.log_timestamp,
user_registration,
(datediff(str_to_date(l1.log_timestamp, "%Y%m%d%H%i%S"), str_to_date(user_registration, "%Y%m%d%H%i%S"))<=1) as diff,
l1.log_user_text,
l1.log_actor,
l1.log_title,
l1.log_comment,
l1.log_page,
rev_id,
ar_rev_id,
rev_len,
ar_len,
(ctd_name="visualeditor") as visualeditor,
(ctd_name="mobile edit") as mobile_edit,
(ctd_name="contenttranslation") as contenttranslation
from
user,
logging_compat as l1
LEFT JOIN revision ON l1.log_page=rev_page AND l1.log_actor=rev_actor
LEFT JOIN archive ON l1.log_title=ar_title AND ar_namespace=0 AND ar_actor=l1.log_actor AND ar_timestamp>=l1.log_timestamp
LEFT JOIN change_tag ON ct_log_id=l1.log_id
LEFT JOIN change_tag_def ON ctd_id=ct_tag_id
where
user_registration > 20200000000000
and user_registration<20210000000000
and user_editcount>0
and l1.log_user=user_id
and l1.log_namespace=0
and l1.log_type="create"
and l1.log_id>11602246
and l1.log_id<12371120
) as t
where
diff=1
AND contenttranslation>0
group by log_id
) as tt
LEFT JOIN logging_compat as l2 ON tt.log_page=l2.log_page AND l2.log_type="delete" AND l2.log_id>tt.log_id
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.