for pete - Quarry

SQL

set @page_title = 'Gautam Adani';

with sock_cat as 
(
    select
        -- only select first master for cases where account in multiple cats
        min(if(substring(cl.cl_to,1,1) = 'S', substring(cl_to, 36), substring(cl_to, 26))) as master_from_cat, 
        a.actor_id,
        replace(convert(p.page_title using utf8mb4),'_',' ') sock,
        -- only select first for cases where account in multiple cats with different status
        min(if(substring(cl.cl_to,1,1) = 'S', 'suspected', 'confirmed')) sock_status 
    from
        categorylinks cl
        join page p on cl_from = p.page_id
        join category cat on cl.cl_to = cat.cat_title
        join actor a on a.actor_name = replace(p.page_title,'_',' ') -- join to actor to get IPs
    where
        cl.cl_to LIKE "%Wikipedia_sockpuppets_of_%"
    group by 2,3
),
revisions as (
    select 
        ru.rev_timestamp,
        ar.actor_id,
        ar.actor_user,
        convert(ar.actor_name using utf8mb4) actor_name,
        p.page_namespace,
        case
            when page_namespace = 0 then "Article"
            when page_namespace = 1 then "Talk"
        else null
        end as namespace_desc
    from
        actor_revision ar
        join revision_userindex ru on ru.rev_actor = ar.actor_id 
        join page p on p.page_id = ru.rev_page
    where 
        p.page_title = replace(@page_title,' ','_')
        and p.page_namespace in (0,1)
)
select
    date_format(r.rev_timestamp, '%Y-%m-%dT%H:%i:%s') rev_timestamp, 
    r.actor_id, 
    r.actor_user, 
    r.actor_name, 
    r.page_namespace, 
    r.namespace_desc, 
    date_format(b.ipb_timestamp, '%Y-%m-%dT%H:%i:%s') blocked,
    convert(regexp_replace(c.comment_text,'[{}]','') using utf8mb4) block_log,
    convert(sc.master_from_cat using utf8mb4) master_from_cat,
    sc.sock_status 
from 
    revisions r
    join ipblocks as b on b.ipb_user = r.actor_user -- joins on user id so will inevitably exclude IPs. the alternative to include IPs is very slow.
    join comment_ipblocks c on b.ipb_reason_id = c.comment_id
    left join sock_cat sc on sc.actor_id = r.actor_id 
where
    convert(c.comment_text using utf8mb4) rlike '(checkuser|sock|multiple accounts|evasion|proxy)'
    or sc.master_from_cat is not null
order by r.page_namespace, r.rev_timestamp;
SQL

Checking query status...
