Stats for July 2016 CIS-A2K

This query is marked as a draft This query has been published by రహ్మానుద్దీన్.
SQL

use knwiki_p;
select concat('[[',p.page_title,']]') as Article_Name,concat('[[User:',r.rev_user_text,'|',r.rev_user_text,']]') as User_Name,p.page_len as Size from revision r, page p where r.rev_parent_id = 0 and r.rev_len < 2048 and p.page_len < 2048 and p.page_namespace = 0 and p.page_is_redirect = 0 and rev_timestamp < '20160800000000' and rev_timestamp > '20160630235959' and r.rev_page = p.page_id;
use gomwiki_p;
select concat('[[',p.page_title,']]') as Article_Name,concat('[[User:',r.rev_user_text,'|',r.rev_user_text,']]') as User_Name,p.page_len as Size from revision r, page p where r.rev_parent_id = 0 and r.rev_len < 2048 and p.page_len < 2048 and p.page_namespace = 0 and p.page_is_redirect = 0 and rev_timestamp < '20160800000000' and rev_timestamp > '20160630235959' and r.rev_page = p.page_id;
use orwiki_p;
select concat('[[',p.page_title,']]') as Article_Name,concat('[[User:',r.rev_user_text,'|',r.rev_user_text,']]') as User_Name,p.page_len as Size from revision r, page p where r.rev_parent_id = 0 and r.rev_len < 2048 and p.page_len < 2048 and p.page_namespace = 0 and p.page_is_redirect = 0 and rev_timestamp < '20160800000000' and rev_timestamp > '20160630235959' and r.rev_page = p.page_id;
use mrwiki_p;
select concat('[[',p.page_title,']]') as Article_Name,concat('[[User:',r.rev_user_text,'|',r.rev_user_text,']]') as User_Name,p.page_len as Size from revision r, page p where r.rev_parent_id = 0 and r.rev_len < 2048 and p.page_len < 2048 and p.page_namespace = 0 and p.page_is_redirect = 0 and rev_timestamp < '20160800000000' and rev_timestamp > '20160630235959' and r.rev_page = p.page_id;
use tewiki_p;
select concat('[[',p.page_title,']]') as Article_Name,concat('[[User:',r.rev_user_text,'|',r.rev_user_text,']]') as User_Name,p.page_len as Size from revision r, page p where r.rev_parent_id = 0 and r.rev_len < 2048 and p.page_len < 2048 and p.page_namespace = 0 and p.page_is_redirect = 0 and rev_timestamp < '20160800000000' and rev_timestamp > '20160630235959' and r.rev_page = p.page_id;
select 'Active Kannada Wikipedians' as '';
use knwiki_p;
SET @n = 5; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20160701"; /* to replace with dynamic value */

SELECT
    user
FROM (
    /* Get revisions to content pages that are still visible */
    SELECT
        rev_user as user,
        SUM(rev_id IS NOT NULL) AS revisions
    FROM
        revision
    WHERE
        rev_user != 0
    AND
        rev_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY user

    UNION ALL

    /* Get revisions to content pages that have been archived */
    SELECT
        ar_user as user,
        SUM(ar_id IS NOT NULL) AS revisions
    FROM
        archive
    WHERE
        ar_user != 0
    AND
        ar_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY
        user
    ) AS user_content_revision_count

GROUP BY
    user
HAVING
    SUM(revisions) >= @n;
select 'Active Marathi Wikipedians' as ''; 
use mrwiki_p;
SET @n = 5; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20160701"; /* to replace with dynamic value */

SELECT
    user
FROM (
    /* Get revisions to content pages that are still visible */
    SELECT
        rev_user as user,
        SUM(rev_id IS NOT NULL) AS revisions
    FROM
        revision
    WHERE
        rev_user != 0
    AND
        rev_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY user

    UNION ALL

    /* Get revisions to content pages that have been archived */
    SELECT
        ar_user as user,
        SUM(ar_id IS NOT NULL) AS revisions
    FROM
        archive
    WHERE
        ar_user != 0
    AND
        ar_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY
        user
    ) AS user_content_revision_count

GROUP BY
    user
HAVING
    SUM(revisions) >= @n;
select 'Active Konkani Wikipedians' as '';
use gomwiki_p;
SET @n = 5; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20160701"; /* to replace with dynamic value */

SELECT
    user
FROM (
    /* Get revisions to content pages that are still visible */
    SELECT
        rev_user as user,
        SUM(rev_id IS NOT NULL) AS revisions
    FROM
        revision
    WHERE
        rev_user != 0
    AND
        rev_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY user

    UNION ALL

    /* Get revisions to content pages that have been archived */
    SELECT
        ar_user as user,
        SUM(ar_id IS NOT NULL) AS revisions
    FROM
        archive
    WHERE
        ar_user != 0
    AND
        ar_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY
        user
    ) AS user_content_revision_count

GROUP BY
    user
HAVING
    SUM(revisions) >= @n;

select 'Active Odia Wikipedians' as '';
use orwiki_p;
SET @n = 5; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20160701"; /* to replace with dynamic value */

SELECT
    user
FROM (
    /* Get revisions to content pages that are still visible */
    SELECT
        rev_user as user,
        SUM(rev_id IS NOT NULL) AS revisions
    FROM
        revision
    WHERE
        rev_user != 0
    AND
        rev_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY user

    UNION ALL

    /* Get revisions to content pages that have been archived */
    SELECT
        ar_user as user,
        SUM(ar_id IS NOT NULL) AS revisions
    FROM
        archive
    WHERE
        ar_user != 0
    AND
        ar_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY
        user
    ) AS user_content_revision_count

GROUP BY
    user
HAVING
    SUM(revisions) >= @n;

select 'Active Telugu Wikipedians' as '';
use tewiki_p;
SET @n = 5; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20160701"; /* to replace with dynamic value */

SELECT
    user
FROM (
    /* Get revisions to content pages that are still visible */
    SELECT
        rev_user as user,
        SUM(rev_id IS NOT NULL) AS revisions
    FROM
        revision
    WHERE
        rev_user != 0
    AND
        rev_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY user

    UNION ALL

    /* Get revisions to content pages that have been archived */
    SELECT
        ar_user as user,
        SUM(ar_id IS NOT NULL) AS revisions
    FROM
        archive
    WHERE
        ar_user != 0
    AND
        ar_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY
        user
    ) AS user_content_revision_count

GROUP BY
    user
HAVING
    SUM(revisions) >= @n;
select 'Very Active Telugu Wikipedians' as '';
use tewiki_p;
SET @n = 100; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20160701"; /* to replace with dynamic value */

SELECT
    user
FROM (
    /* Get revisions to content pages that are still visible */
    SELECT
        rev_user as user,
        SUM(rev_id IS NOT NULL) AS revisions
    FROM
        revision
    WHERE
        rev_user != 0
    AND
        rev_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY user

    UNION ALL

    /* Get revisions to content pages that have been archived */
    SELECT
        ar_user as user,
        SUM(ar_id IS NOT NULL) AS revisions
    FROM
        archive
    WHERE
        ar_user != 0
    AND
        ar_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY
        user
    ) AS user_content_revision_count

GROUP BY
    user
HAVING
    SUM(revisions) >= @n;
select 'Very Active Kannada Wikipedians' as '';
use knwiki_p;
SET @n = 100; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20160701"; /* to replace with dynamic value */

SELECT
    user
FROM (
    /* Get revisions to content pages that are still visible */
    SELECT
        rev_user as user,
        SUM(rev_id IS NOT NULL) AS revisions
    FROM
        revision
    WHERE
        rev_user != 0
    AND
        rev_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY user

    UNION ALL

    /* Get revisions to content pages that have been archived */
    SELECT
        ar_user as user,
        SUM(ar_id IS NOT NULL) AS revisions
    FROM
        archive
    WHERE
        ar_user != 0
    AND
        ar_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY
        user
    ) AS user_content_revision_count

GROUP BY
    user
HAVING
    SUM(revisions) >= @n;
    
select 'Very Active Konkani Wikipedians' as ''; 
use gomwiki_p;
SET @n = 100; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20160701"; /* to replace with dynamic value */

SELECT
    user
FROM (
    /* Get revisions to content pages that are still visible */
    SELECT
        rev_user as user,
        SUM(rev_id IS NOT NULL) AS revisions
    FROM
        revision
    WHERE
        rev_user != 0
    AND
        rev_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY user

    UNION ALL

    /* Get revisions to content pages that have been archived */
    SELECT
        ar_user as user,
        SUM(ar_id IS NOT NULL) AS revisions
    FROM
        archive
    WHERE
        ar_user != 0
    AND
        ar_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY
        user
    ) AS user_content_revision_count

GROUP BY
    user
HAVING
    SUM(revisions) >= @n;
    
select 'Very Active Marathi Wikipedians' as '';

use mrwiki_p;
SET @n = 100; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20160701"; /* to replace with dynamic value */

SELECT
    user
FROM (
    /* Get revisions to content pages that are still visible */
    SELECT
        rev_user as user,
        SUM(rev_id IS NOT NULL) AS revisions
    FROM
        revision
    WHERE
        rev_user != 0
    AND
        rev_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY user

    UNION ALL

    /* Get revisions to content pages that have been archived */
    SELECT
        ar_user as user,
        SUM(ar_id IS NOT NULL) AS revisions
    FROM
        archive
    WHERE
        ar_user != 0
    AND
        ar_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY
        user
    ) AS user_content_revision_count

GROUP BY
    user
HAVING
    SUM(revisions) >= @n;
select 'Very Active Odia Wikipedians' as '';
    
use orwiki_p;
SET @n = 100; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20160701"; /* to replace with dynamic value */

SELECT
    user
FROM (
    /* Get revisions to content pages that are still visible */
    SELECT
        rev_user as user,
        SUM(rev_id IS NOT NULL) AS revisions
    FROM
        revision
    WHERE
        rev_user != 0
    AND
        rev_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY user

    UNION ALL

    /* Get revisions to content pages that have been archived */
    SELECT
        ar_user as user,
        SUM(ar_id IS NOT NULL) AS revisions
    FROM
        archive
    WHERE
        ar_user != 0
    AND
        ar_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    GROUP BY
        user
    ) AS user_content_revision_count

GROUP BY
    user
HAVING
    SUM(revisions) >= @n;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
