Untitled query #75418

SQL

WITH active_users AS (
    SELECT actor.actor_user
    FROM revision_userindex AS revision
    INNER JOIN actor_revision AS actor ON revision.rev_actor = actor.actor_id
    WHERE TRUE
      AND actor.actor_user IS NOT NULL
),

gender_data AS (
    SELECT up_user, up_value AS gender
    FROM user_properties
    WHERE up_property = 'gender'
),

active_users_with_gender AS (
    SELECT active_users.actor_user, gender_data.gender
    FROM active_users
    LEFT JOIN gender_data ON active_users.actor_user = gender_data.up_user
)

SELECT DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(rev_timestamp, '%Y%m%d%H%i%s'), '+00:00', '+03:00'), '%Y-%m') AS month,
       COUNT(DISTINCT active_users_with_gender.actor_user) AS user_count,
       active_users_with_gender.gender
FROM revision_userindex AS revision
LEFT JOIN active_users_with_gender ON revision.actor_user = active_users_with_gender.actor_user
GROUP BY month, active_users_with_gender.gender
ORDER BY month, active_users_with_gender.gender;
SQL

Checking query status...
