Rückwirkend die Anzahl der Nutzer pro Kalendermonat erfassen

Daten & Berichterstattung
10

Eine interessante Frage.

Ich habe mir zuerst das Beispiel hier angesehen. Aber das ignoriert gelöschte Benutzer. Man erhält nur die Anzahl der Benutzer, die zu diesem Zeitpunkt registriert waren und es immer noch sind, nicht aber diejenigen, die in der Zwischenzeit gelöscht wurden.
Meine Idee war daher, die ID des Benutzers zu nehmen, der zuletzt im Monat registriert wurde. Das ist die maximal mögliche Anzahl von Benutzern zu diesem Zeitpunkt. Davon kann dann die Anzahl der gelöschten Benutzer abgezogen werden. Bot-Konten (wie forum-helper) haben jedoch eine negative ID, werden aber mitgezählt, wenn sie gelöscht werden. (Das ist aber wahrscheinlich eine geringfügige Abweichung). Meine Abfrage war:

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

Was sie aber nicht berücksichtigt, ist die (De-)Aktivierung, die ebenfalls in der Tabelle user_histories gespeichert ist. Aber vielleicht hilft sie Ihnen als Ausgangspunkt.

5 „Gefällt mir“