Untitled query #38554

This query is marked as a draft This query has been published by جار الله.
SQL

use arwiki_p;
SELECT
  creations_ns0.user,
  first_edit.timestamp as first_edit

FROM
  (
    SELECT
      COUNT(*) as count,
      creator.user as user
    FROM
      (
        SELECT
          actor_name as user
        FROM
          (
            SELECT
              rev_page,
              MIN(rev_timestamp) as first_edit
            FROM
              page
              INNER JOIN revision ON page.page_id = revision.rev_page
              INNER JOIN actor ON rev.rev_actor = actor.actor_id
            WHERE
              page.page_namespace = 0
              AND page.page_is_redirect = 0
            GROUP BY
              revision.rev_page
          ) as first_edit
          INNER JOIN revision ON first_edit.rev_page = revision.rev_page
          INNER JOIN actor ON rev.rev_actor = actor.actor_id
          AND first_edit.first_edit = rev_timestamp
      ) as creator
    GROUP BY
      creator.user
  ) as creations_ns0
  LEFT JOIN (
    SELECT
      COUNT(*) as count,
      creator.user as user
    FROM
      (
        SELECT
          actor_name as user
        FROM
          (
            SELECT
              rev_page,
              MIN(rev_timestamp) as first_edit
            FROM
              page
              INNER JOIN revision ON page.page_id = revision.rev_page
              INNER JOIN actor ON rev.rev_actor = actor.actor_id
            WHERE
              page.page_namespace = 10
              AND page.page_is_redirect = 0
            GROUP BY
              revision.rev_page
          ) as first_edit
          INNER JOIN revision ON first_edit.rev_page = revision.rev_page
          INNER JOIN actor ON rev.rev_actor = actor.actor_id
          AND first_edit.first_edit = rev_timestamp
      ) as creator
    GROUP BY
      creator.user
  ) as creations_ns10 ON creations_ns0.user = creations_ns10.user
  LEFT JOIN (
    SELECT
      COUNT(*) as count,
      creator.user as user
    FROM
      (
        SELECT
          actor_name as user
        FROM
          (
            SELECT
              rev_page,
              MIN(rev_timestamp) as first_edit
            FROM
              page
              INNER JOIN revision ON page.page_id = revision.rev_page
              INNER JOIN actor ON rev.rev_actor = actor.actor_id
            WHERE
              page.page_namespace = 12
              AND page.page_is_redirect = 0
            GROUP BY
              revision.rev_page
          ) as first_edit
          INNER JOIN revision ON first_edit.rev_page = revision.rev_page
          AND first_edit.first_edit = rev_timestamp
      ) as creator
    GROUP BY
      creator.user
  ) as creations_ns12 ON creations_ns0.user = creations_ns12.user
  LEFT JOIN (
    SELECT
      COUNT(*) as count,
      creator.user as user
    FROM
      (
        SELECT
          actor_name as user
        FROM
          (
            SELECT
              rev_page,
              MIN(rev_timestamp) as first_edit
            FROM
              page
              INNER JOIN revision ON page.page_id = revision.rev_page
              INNER JOIN actor ON rev.rev_actor = actor.actor_id
            WHERE
              page.page_namespace = 14
              AND page.page_is_redirect = 0
            GROUP BY
              revision.rev_page
          ) as first_edit
          INNER JOIN revision ON first_edit.rev_page = revision.rev_page
          AND first_edit.first_edit = rev_timestamp
      ) as creator
    GROUP BY
      creator.user
  ) as creations_ns14 ON creations_ns0.user = creations_ns14.user
  LEFT JOIN (
    SELECT
      COUNT(*) as count,
      creator.user as user
    FROM
      (
        SELECT
          actor_name as user
        FROM
          (
            SELECT
              rev_page,
              MIN(rev_timestamp) as first_edit
            FROM
              page
              INNER JOIN revision ON page.page_id = revision.rev_page
              INNER JOIN actor ON rev.rev_actor = actor.actor_id
            WHERE
              page.page_namespace = 100
              AND page.page_is_redirect = 0
              AND page.page_title NOT IN (
                SELECT
                  page_title
                FROM
                  page
                WHERE
                  page_title LIKE "%/%"
              )
            GROUP BY
              revision.rev_page
          ) as first_edit
          INNER JOIN revision ON first_edit.rev_page = revision.rev_page
          AND first_edit.first_edit = rev_timestamp
      ) as creator
    GROUP BY
      creator.user
  ) as creations_ns100 ON creations_ns0.user = creations_ns100.user
  LEFT JOIN (
    SELECT
      COUNT(*) as count,
      creator.user as user
    FROM
      (
        SELECT
                actor_name as user
        FROM
          (
            SELECT
              rev_page,
              MIN(rev_timestamp) as first_edit
            FROM
              page
              INNER JOIN revision ON page.page_id = revision.rev_page
              INNER JOIN actor ON rev.rev_actor = actor.actor_id
            WHERE
              page.page_namespace = 104
              AND page.page_is_redirect = 0
            GROUP BY
              revision.rev_page
          ) as first_edit
          INNER JOIN revision ON first_edit.rev_page = revision.rev_page
          AND first_edit.first_edit = rev_timestamp
      ) as creator
    GROUP BY
      creator.user
  ) as creations_ns104 ON creations_ns0.user = creations_ns104.user
  INNER JOIN (
    SELECT
      SELECT actor_name as user,
      min(rev_timestamp) as timestamp
    FROM revision
    INNER JOIN actor ON rev.rev_actor = actor.actor_id
    GROUP BY
      actor_name
  ) as first_edit ON creations_ns0.user = first_edit.user
WHERE creations_ns0.user NOT IN (SELECT user_name FROM user_groups INNER JOIN user ON user_id = ug_user WHERE ug_group = 'bot')
GROUP BY
  creations_ns0.user
ORDER BY
  article_creations DESC
LIMIT
  500;
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...
