Jump to content

User:NaomiAmethyst/WikiProject Professional wrestling/Member activity check

From Wikipedia, the free encyclopedia

Query

[edit]
SET group_concat_max_len = 102400;
SELECT
    GROUP_CONCAT(
        CONCAT(
            '* [[User:',
            `user_name`,
            '|]] — ',
            `status`
        )
        SEPARATOR '\n'
    ) AS `wikitext`
FROM
    (
        SELECT
            `user_name`,
            IF(
                `user_editcount` = 0,
                "Has never edited",
                IF(
                    `user_editcount` < 25,
                    "Has less than 25 edits",
                    IF(
                        `lastedit` < UNIX_TIMESTAMP() - 60*60*24*365,
                        "Has not edited within the last year",
                        IF(
                            `lastedit` < UNIX_TIMESTAMP() - 60*60*24*365/2,
                            "Has not edited within the last 6 months",
                            "Active"
                        )
                    )
                )
            ) AS `status`
        FROM
            (
                SELECT
                    `user_id`,
                    `user_name`,
                    `user_editcount`,
                    (
                        SELECT
                            UNIX_TIMESTAMP(`rev_timestamp`) AS `lastedit`
                        FROM
                            `revision`
                        WHERE
                            `rev_user` = `user_id`
                        ORDER BY
                            `rev_timestamp` DESC
                        LIMIT 1
                    ) AS `lastedit`
                FROM
                    `user` AS `u`,
                    (
                        SELECT
                            `pl_title`
                        FROM
                            `pagelinks`
                        WHERE
                            `pl_namespace` = 2
                            AND `pl_from` = (
                                SELECT
                                    `page_id`
                                FROM
                                    `page`
                                WHERE
                                    `page_namespace` = 4
                                    AND `page_title` = 'WikiProject_Professional_wrestling/Members_list'
                            )
                    ) AS `p`
                WHERE
                    `p`.`pl_title` = `u`.`user_name`
            ) AS `userdata`
    ) AS `stuff`
;

Results

[edit]

Time

[edit]

1 row in set (1 min 12.54 sec)