Untitled query #25269

SQL

use huwiki_p;

SELECT
  CONCAT(
    CASE page_namespace
      WHEN 0 THEN ''
      WHEN 6 THEN 'Fájl:'
      WHEN 10 THEN 'Sablon:'
      WHEN 14 THEN 'Kategória:'
      WHEN 100 THEN 'Portál:'
      WHEN 828 THEN 'Modul:'
    END
  , page_title) AS title,
  CONCAT('https://hu.wikipedia.org/w/index.php?curid=', page_id) AS url,
  DATEDIFF(NOW(), STR_TO_DATE(rev_timestamp, '%Y%m%d%h%i%s')) AS days_unreviewed,
  rev_timestamp AS creation,
  STR_TO_DATE(rev_timestamp, '%Y%m%d%H%i%s') AS date
FROM
  (
    SELECT
      page_id,
      MIN(rev_id) as rev_id
    FROM
      (
        SELECT
          page_id
        FROM
          page
          LEFT JOIN flaggedpages ON fp_page_id = page_id
        WHERE
          fp_page_id IS NULL
          AND page_namespace IN (0, 10, 14, 100, 828) -- files (6) omitted as it is full of 10+ years old unreviewed ones
          AND page_is_redirect = 0
      ) unreviewed_pages
      JOIN revision ON rev_page = page_id
    GROUP BY
      page_id
  ) first_revisions
  JOIN page USING (page_id)
  JOIN revision USING (rev_id)
ORDER BY days_unreviewed DESC
;
SQL

Checking query status...
