Retrospectivamente puxando o número de usuários de cada mês do calendário

Dados e relatórios
10

Uma pergunta interessante.

Primeiro dei uma olhada no exemplo aqui. Mas isso ignora usuários excluídos. Você só obtém o número de usuários que foram registrados naquela época e ainda são, não aqueles que foram excluídos no ínterim.
Minha ideia foi, portanto, pegar o ID do usuário que se registrou por último no mês. Este é o número máximo possível de usuários naquela época. O número de usuários excluídos pode então ser subtraído disso. No entanto, contas de bot (como forum-helper) têm um ID negativo, mas são contadas se forem excluídas. (Mas isso provavelmente é um desvio menor). Minha consulta foi:

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


WITH month_dates AS (
    -- Gerar datas de fim de mês entre a data de início e a data de fim
    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 (
    -- Para cada data de fim de mês, encontrar o usuário mais recente criado antes dessa data
    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 (
    -- Calcular as exclusões cumulativas até cada data de fim de mês
    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

Mas o que ele não leva em conta é a (des)ativação, que também são armazenadas na tabela user_histories. Mas talvez isso ajude você como um ponto de partida.

5 curtidas