Analysis of Article Edits by Gender

SQL

WITH GenderEdits AS (
    SELECT  
        up_value AS Gender,
        COUNT(r.rev_id) AS revision_count
    FROM 
        user u
    LEFT JOIN 
        user_properties up ON up.up_user = u.user_id
    LEFT JOIN
        revision r ON r.rev_user = u.user_id
    WHERE 
        YEAR(u.user_registration) BETWEEN 2019 AND 2024
        AND up.up_property = 'gender'
        AND up.up_value IN ('male', 'female') 
    GROUP BY 
        up_value
)
SELECT
    SUM(CASE WHEN Gender = 'male' THEN revision_count ELSE 0 END) AS male_edits,
    SUM(CASE WHEN Gender = 'female' THEN revision_count ELSE 0 END) AS female_edits,
    ROUND(SUM(CASE WHEN Gender = 'male' THEN revision_count ELSE 0 END) / 
          NULLIF(SUM(CASE WHEN Gender = 'female' THEN revision_count ELSE 0 END), 0), 2) AS male_female_ratio
FROM GenderEdits;
SQL

Checking query status...
