Ritirando retrospettivamente il numero di utenti ogni mese calendario

Dati e reportistica
10

Una domanda interessante.

Ho prima dato un’occhiata all’esempio qui. Ma questo ignora gli utenti eliminati. Ottieni solo il numero di utenti che si sono registrati in quel momento e lo sono ancora, non quelli che sono stati eliminati nel frattempo.
La mia idea era quindi quella di prendere l’ID dell’utente che si è registrato per ultimo nel mese. Questo è il numero massimo possibile di utenti in quel momento. Il numero di utenti eliminati può quindi essere sottratto da questo. Tuttavia, gli account bot (come forum-helper) hanno un ID negativo, ma vengono conteggiati se vengono eliminati. (Ma questa è probabilmente una deviazione minore). La mia query era:

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

Ma quello che non tiene conto è l’attivazione/(dis)attivazione, che sono anche memorizzate nella tabella user_histories. Ma forse ti aiuterà come punto di partenza.

5 Mi Piace