全アクティブユーザー（ROM専 + 書き込みユーザー）のデータエクスプローラー クエリ

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

現在のアクティブユーザー数をチームの統計として提供したいと考えています。ここでいう「アクティブユーザー」の定義は以下の通りです：

過去1年間にログインして記事を読んだか、当フォーラムに投稿したユーザー

メール対応モードのユーザーが多く、メールで返信しているため、以下のクエリのように単に「読者数」を集計するだけでは、多くのユーザーを見落としてしまいます：

また、投稿したユーザー数も取得可能です：

これらを OR 条件で結合して両方のグループを取得する方法がわからず、困っています。お手伝いいただけますでしょうか？

「いいね！」 1
2

これは、過去 1 年間にログインして記事を読んだユーザー、または投稿したユーザーをすべて取得するクエリです。Data Explorer には、その数も表示されます。

SELECT p.user_id
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL '365 days'
 AND t.deleted_at IS NULL
 AND t.visible = TRUE
 AND t.closed = FALSE
 AND t.archived = FALSE
 AND t.archetype = 'regular'
 AND p.deleted_at IS NULL
UNION
SELECT u.user_id
FROM user_visits u
WHERE u.posts_read > 0
 AND u.visited_at > CURRENT_TIMESTAMP - INTERVAL '365 days'
ORDER BY user_id

もっと効率的な方法もあるかもしれませんが、これで動作します :slight_smile: 。特定の期間を指定したい場合は、> CURRENT_TIMESTAMP - INTERVAL '365 days'（両方とも）を以下のように変更してください：BETWEEN '20200101'::date AND '20210101'::date。

質問には素晴らしい情報と参考資料が盛り込まれていました！必要な部分を選んで組み合わせるだけで済みました。

「いいね！」 3
3

ありがとうございます！可能であれば、他の方がお手伝いしやすいようにするよう心がけています。

UNION — これが私の SQL 知識の穴でした。すべて解決しましたので、ありがとうございました！より洗練された後に、最終的なクエリをここに投稿します。

「いいね！」 1
4

実際には、より多くの情報が必要で、UNION では少し制限が厳しいと感じたため、少し異なるアプローチを取りました。また、グループ別に内訳を出したかったのです。既存の「アクティブな閲覧者」クエリを、グループ固有の情報を抽出するための別のクエリと組み合わせて修正することで、成功しました（と思う）：

-- [params]
-- int :number_of_days = 365
-- string :group_name = trust_level_0

WITH included_users AS (
SELECT
    gu.user_id
FROM group_users gu
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_name
),
posts_by_user AS (
    SELECT COUNT(*) AS posts, p.user_id
    FROM posts p
    LEFT JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day
        AND t.deleted_at IS NULL
        AND t.visible = TRUE
        AND t.closed = FALSE
        AND t.archived = FALSE
        AND t.archetype = 'regular'
        AND p.deleted_at IS NULL
    GROUP BY p.user_id
), 
posts_read_by_user AS (
    SELECT SUM(posts_read) AS posts_read, uv.user_id
    FROM user_visits uv
    WHERE uv.posts_read > 0
        AND uv.visited_at > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day
    GROUP BY uv.user_id
)

SELECT
    u.id AS "user_id",
    u.username_lower AS "username",
    u.last_seen_at,
    COALESCE(pbu.posts, 0) AS "posts_created",
    COALESCE(prbu.posts_read, 0) AS "posts_read"
FROM users u
LEFT JOIN posts_by_user pbu ON pbu.user_id = u.id
LEFT JOIN posts_read_by_user prbu ON prbu.user_id = u.id
WHERE u.active = true
    AND u.id > 0
    AND u.id IN (SELECT user_id FROM included_users)
    AND (COALESCE(pbu.posts, 0) > 0 OR COALESCE(prbu.posts_read, 0) > 0)
ORDER BY u.id
「いいね！」 4
5

これを共有していただきありがとうございます。これと非常によく似たコードをバッジクエリに使用しようとしましたが、次のエラーが発生しました。

契約違反:
クエリは「granted_at」列を返しません

このクエリをバッジシステムで使用するために、コードで何か不足していますか？私のユースケースは、「lurkers」グループを自動化することです :slight_smile:

6

賢い！

バッジクエリはかなり複雑で、返されるのは「user_id」と「granted_at」のみが必要です。そのため、少しハッキングする必要があります。

バッジクエリに関するトピックを（注意深く読んで）掘り下げて、試してみることをお勧めします。そうでなければ、少し重くなる可能性があるため、1日1回しか実行しないことをお勧めします。

結果をお知らせください！

「いいね！」 1