Research talk:Active editor spike 2015 (July update)/Work log/2015-07-07

Tuesday, July 7, 2015

I've been tasked with picking this up again. First things first, I've updated the editor_months dataset with the following query:

SELECT
    wiki,
    month,
    user_id,
    user_name,
    user_registration,
    SUM(revisions * archived) AS archived,
    SUM(revisions) AS revisions
FROM (
    SELECT
        LEFT(rev_timestamp, 6) AS month,
        DATABASE() AS wiki,
        rev_user AS user_id,
        FALSE AS archived,
        COUNT(*) AS revisions
    FROM revision
    WHERE rev_timestamp >= "201503"
    GROUP BY LEFT(rev_timestamp, 6), rev_user

    UNION ALL

    SELECT
        LEFT(ar_timestamp, 6) AS month,
        DATABASE() AS wiki,
        ar_user AS user_id,
        TRUE AS archived,
        COUNT(*) AS revisions
    FROM archive
    WHERE ar_timestamp >= "201503"
    GROUP BY LEFT(ar_timestamp, 6), ar_user
) AS editor_months
INNER JOIN user USING (user_id)
GROUP BY wiki, month, user_id
ORDER BY wiki, month;

Multiquery is churning through the wikis gathering that data right now. I expect it to talk about 24 hours. --Halfak (WMF) (talk) 18:28, 7 July 2015 (UTC)