Untitled query #86526

SQL

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

Checking query status...
