编辑代码

WITH daily_active_users AS (
    SELECT
        a.dt,
        a.user_id,
        u.gender
    FROM
        active_user_di a
    JOIN
        dim_user_df u ON a.user_id = u.user_id AND a.dt = u.dt
),
gender_active_users AS (
    SELECT
        dt,
        gender,
        COUNT * AS active_users_count
    FROM
        daily_active_users
    GROUP BY
        dt, gender
)
SELECT
    dt,
    gender,
    active_users_count
FROM
    gender_active_users
ORDER BY
    dt, gender;