Fork of Articles created by new users in fiwiki using content translation by Zache
This query is marked as a draft This query has been published by Zache.

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.

Checking query status...