データエクスプローラー - 月別アクティブメンバーの割合

コミュニティ データとレポート
1

Data Explorer プラグインを利用・探索してくださっている皆様、こんにちは！

@michebs さん、これまでの質問へのサポートに感謝します。データエクスプローラーのクエリについて、もう一点お伺いしたいことがあります。

月ごとのアクティブユーザーの割合を取得するクエリを作成した方はいますか？

ここで言う「アクティブ」とは、その月にスレッドに対して「いいね」または「返信」を行ったユーザーを指します。回数は問いませんが、少なくとも 1 回は必要です。割合はコミュニティの全メンバー数を基準に計算されます。例えば、ある月に 2 人のユーザーが関与した場合、1 人が投稿に「いいね」をし、もう 1 人が「返信」をしたとすると、分子は 2 となり、これをコミュニティの全メンバー数で割ります。

2

お役に立てれば幸いです。
なお、ユーザーの総数は時間とともに変動するため、このクエリで考慮されているのは、アクションが実行された月および年までの累積ユーザー数です。

WITH tt_users_by_month AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        COUNT(*) AS "new_users_month"
    FROM users
    WHERE id > 0
    GROUP BY date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

total_users AS (
    SELECT
        year, 
        month, 
        SUM(new_users_month) over (ORDER BY year, month rows between unbounded preceding AND current row) AS total
    FROM tt_users_by_month ORDER BY year, month
)

SELECT 
    date_part('year', ua.created_at) AS year, 
    date_part('month', ua.created_at) AS month,
    TRUNC(COUNT(DISTINCT user_id)::decimal/tu.total*100,2) AS "%"
FROM 
    user_actions ua
INNER JOIN total_users tu ON (date_part('year', ua.created_at) = tu.year AND date_part('month', ua.created_at) = tu.month)
WHERE action_type IN (1,5)
	GROUP BY date_part('year', created_at), date_part('month', created_at), total
3

はい、全くその通りですね！ありがとうございます！