Retrospectivamente recopilando el número de usuarios de cada mes calendario

Datos e informes
10

Una pregunta interesante.

Primero eché un vistazo al ejemplo aquí. Pero eso ignora a los usuarios eliminados. Solo obtienes el número de usuarios que se registraron en ese momento y todavía están, no aquellos que han sido eliminados en el ínterin.
Mi idea fue, por lo tanto, tomar el ID del usuario que se registró por última vez en el mes. Este es el número máximo posible de usuarios en ese momento. Luego se puede restar el número de usuarios eliminados. Sin embargo, las cuentas de bots (como forum-helper) tienen un ID negativo, pero se cuentan si se eliminan. (Pero esto es probablemente una desviación menor). Mi consulta fue:

-- [params]
-- date :start_date
-- date :end_date


WITH month_dates AS (
    -- Generate end-of-month dates between the start and end date
    SELECT DATE_TRUNC('month', generate_series)::date + INTERVAL '1 month' - INTERVAL '1 day' AS month_end
    FROM generate_series(:start_date::date, :end_date::date, '1 month'::interval)
),
recent_user AS (
    -- For each end-of-month date, find the most recent user created before that date
    SELECT md.month_end,
           (SELECT id
            FROM users u
            WHERE u.created_at < md.month_end
            ORDER BY u.created_at DESC
            LIMIT 1) AS user_max_id
    FROM month_dates md
),
cumulative_deletion_count AS (
    -- Calculate the cumulative deletions up to each end-of-month date
    SELECT md.month_end,
           (SELECT COUNT(*)
            FROM user_histories uh
            WHERE uh.action = 1 AND uh.updated_at < md.month_end) AS deletions_count
    FROM month_dates md
)
SELECT
    md.month_end,
    ru.user_max_id,
    cdc.deletions_count,
    ru.user_max_id - cdc.deletions_count AS number_of_users
FROM
    month_dates md
LEFT JOIN recent_user ru ON md.month_end = ru.month_end
LEFT JOIN cumulative_deletion_count cdc ON md.month_end = cdc.month_end
ORDER BY md.month_end

Pero lo que no tiene en cuenta es la (des)activación, que también se almacena en la tabla user_histories. Pero tal vez te sirva como punto de partida.

5 Me gusta