Rétrospectivement, la récupération du nombre d'utilisateurs chaque mois calendaire

Données et rapports
10

Une question intéressante.

J’ai d’abord examiné l’exemple ici. Mais cela ignore les utilisateurs supprimés. Vous obtenez uniquement le nombre d’utilisateurs qui étaient enregistrés à ce moment-là et qui le sont toujours, pas ceux qui ont été supprimés entre-temps.
Mon idée était donc de prendre l’ID de l’utilisateur qui s’est enregistré en dernier dans le mois. C’est le nombre maximum d’utilisateurs possibles à ce moment-là. Le nombre d’utilisateurs supprimés peut alors être soustrait de cela. Cependant, les comptes de bots (comme forum-helper) ont un ID négatif, mais sont comptés s’ils sont supprimés. (Mais c’est probablement un écart mineur). Ma requête était :

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


WITH month_dates AS (
    -- Générer les dates de fin de mois entre la date de début et la date de fin
    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 (
    -- Pour chaque date de fin de mois, trouver l'utilisateur le plus récent créé avant cette 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 (
    -- Calculer les suppressions cumulées jusqu'à chaque date de fin de mois
    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

Mais ce qu’il ne prend pas en compte, ce sont les (dés)activations, qui sont également stockées dans la table user_histories. Mais cela peut vous aider comme point de départ.

5 « J'aime »