Untitled query #86411

SQL

WITH GenderEdits AS (
    SELECT  
        up.up_value AS Gender,
        YEAR(r.rev_timestamp) AS Year,
        COUNT(CASE WHEN p.page_namespace = 0 THEN r.rev_id END) AS article_edits,  -- Article edits (namespace 0 is generally used for articles)
        COUNT(r.rev_id) AS total_edits  -- Total edits (both article and non-article)
    FROM 
        user u
    LEFT JOIN 
        user_properties up ON up.up_user = u.user_id
    LEFT JOIN
        revision r ON r.rev_id = u.user_id
    LEFT JOIN
        page p ON p.page_id = r.rev_page  -- Join page table to check if it's an article (namespace = 0)
    WHERE 
        YEAR(r.rev_timestamp) BETWEEN YEAR(CURDATE()) - 5 AND YEAR(CURDATE())  -- Last 5 years
        AND up.up_property = 'gender'
        AND up.up_value IN ('male', 'female')  -- Only male and female
    GROUP BY 
        up.up_value, YEAR(r.rev_timestamp)
)
SELECT 
    Year,
    SUM(CASE WHEN Gender = 'male' THEN article_edits ELSE 0 END) AS male_article_edits,
    SUM(CASE WHEN Gender = 'female' THEN article_edits ELSE 0 END) AS female_article_edits,
    ROUND(SUM(CASE WHEN Gender = 'male' THEN article_edits ELSE 0 END) / 
          NULLIF(SUM(CASE WHEN Gender = 'female' THEN article_edits ELSE 0 END), 0), 2) AS article_male_female_ratio,
    
    SUM(CASE WHEN Gender = 'male' THEN total_edits ELSE 0 END) AS male_total_edits,
    SUM(CASE WHEN Gender = 'female' THEN total_edits ELSE 0 END) AS female_total_edits,
    ROUND(SUM(CASE WHEN Gender = 'male' THEN total_edits ELSE 0 END) / 
          NULLIF(SUM(CASE WHEN Gender = 'female' THEN total_edits ELSE 0 END), 0), 2) AS total_male_female_ratio
FROM 
    GenderEdits
GROUP BY 
    Year;
SQL

Checking query status...
