Untitled query #86410

SQL

WITH GenderEdits AS (
    SELECT  
        up.up_value AS Gender,
        YEAR(r.rev_timestamp) AS Year,
        COUNT(CASE WHEN r.rev_id = 'article' THEN r.rev_id END) AS article_edits,  -- Article edits count
        COUNT(r.rev_id) AS total_edits  -- Total edits count (including article and non-article edits)
    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
    WHERE 
        YEAR(r.rev_timestamp) BETWEEN 2019 AND 2024 -- 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 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
ORDER BY 
    Year ASC;
SQL

Checking query status...
