Ретроспективное получение количества пользователей за каждый календарный месяц

Создание сообщества Данные и отчетность
10

Интересный вопрос.

Сначала я посмотрел на пример здесь. Однако он не учитывает удалённых пользователей. Вы получаете только количество пользователей, которые были зарегистрированы на тот момент и всё ещё существуют, но не тех, кто был удалён за это время.

Поэтому моя идея заключалась в том, чтобы взять ID пользователя, который зарегистрировался последним в течение месяца. Это максимально возможное количество пользователей на тот момент. Затем из этого числа можно вычесть количество удалённых пользователей. Однако учётные записи ботов (например, forum-helper) имеют отрицательный ID, но они учитываются при удалении. (Но это, вероятно, незначительное отклонение). Мой запрос был следующим:

-- [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

Однако этот запрос не учитывает (де)активацию, которые также хранятся в таблице user_histories. Но, возможно, это поможет вам как отправная точка.