Retrospectively pulling number of users each calendar month

Data & reporting
10

An interesting question.

I first took a look at the example here. But that does ignore deleted users. You only get the number of users who were registered at that time and still are, not those who have been deleted in the meantime.
My idea was therefore to take the ID of the user who last registered in the month. This is the maximum possible number of users at that time. The number of deleted users can then be subtracted from this. However, bot accounts (such as forum-helper) have a negative ID, but are counted if they are deleted. (But this is probably a minor deviation). My query was:

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

But what it doesn’t take into account is (de)activation, which are also stored in the user_histories table. But maybe it will help you as a starting point.

5 Likes