各暦月のユーザー数を遡って取得

データとレポート
10

興味深い質問ですね。

まず、こちらの例を見てみました。しかし、これは削除されたユーザーを無視しています。その時点で登録されており、現在も存在するユーザーの数しか取得できず、その間に削除されたユーザーは含まれません。

そこで、その月に最後に登録されたユーザーのIDを取得するというアイデアを考えました。これはその時点でのユーザー数の最大値になります。その後、削除されたユーザー数をこの値から差し引くことができます。ただし、ボットアカウント（例：forum-helper）はIDが負の値ですが、削除されている場合はカウントされます。（しかし、これはおそらく些細な誤差でしょう）。私のクエリは以下の通りです。

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


WITH month_dates AS (
    -- 開始日と終了日の間の月末日を生成する
    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 (
    -- 各月末日について、その日より前に作成された最も新しいユーザーを見つける
    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 (
    -- 各月末日までの累積削除数を計算する
    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 テーブルに保存されています。しかし、これは出発点として役立つかもしれません。

「いいね！」 5