To get all articles for a wikiproject, their rev id, their rev_timestamps, their manual assessments

SQL

-- First, get all articles from the "Climate_change" WikiProject
WITH ClimateChangeArticles AS (
    SELECT
        p.page_title as Title,
        p.page_id as ID
    FROM
        page_assessments pa
    JOIN
        page_assessments_projects pap ON pa.pa_project_id = pap.pap_project_id
    JOIN
        page p ON pa.pa_page_id = p.page_id
    WHERE
        pap.pap_project_title = 'Climate change'
  
    limit 5
)

-- Then, use the result to get the revisions of those articles
SELECT
    r.rev_id,
    r.rev_timestamp,
    p.page_title
FROM
    revision r
JOIN
    page p ON r.rev_page = p.page_id
WHERE
    p.page_title IN (SELECT Title FROM ClimateChangeArticles)
ORDER BY
    r.rev_timestamp DESC;

SQL

Checking query status...