アクティビティに基づいてグループを作成するクエリ

alefattorini · 2023 年 8 月 30 日午前 9:50

コミュニティ内で、以下に基づいてユーザーをセグメント化する必要があります。

受け取った「いいね！」の数（30、100、200）
読まれた投稿数（1k、2k、5k）
過去1年間の投稿数の最小値

データエクスプローラーを使用してこれを実行するにはどうすればよいですか？
これらのパラメータを入力すると、ユーザーのリストが表示され、手動でグループに追加できるようなクエリを作成したいです。非常に簡単です。
何かヒントはありますか？どこから始めればよいですか？

JammyDodger · 2023 年 8 月 31 日午後 3:53

これならできると思います。

-- [params]
-- int :likes_received
-- int :posts_read

SELECT
    us.user_id,
    us.likes_received,
    us.posts_read_count
FROM user_stats us
  JOIN users u on u.id = us.user_id
WHERE u.last_posted_at > CURRENT_DATE - INTERVAL '1 YEAR'
  AND us.likes_received >= :likes_received
  AND us.posts_read_count >= :posts_read
ORDER BY 2 DESC, 3 DESC

alefattorini · 2023 年 9 月 1 日午後 12:39

素晴らしいですね！\n過去1年間で少なくとも10回投稿されたかどうかを確認するにはどうすればよいですか？\nあなたのクエリのように1回だけではなく、\n\nこのクエリをどのように統合できますか？ Posts created for period

JammyDodger · 2023 年 9 月 3 日午前 11:10

確認ですが、いいねと投稿の閲覧数は「すべて」を対象としていますか、それとも過去1年間を対象としたカウントでもありますか？

merefield · 2023 年 9 月 3 日午前 11:35

これらは既存の信頼レベルグループに酷似しています（それらの人口も同様の尺度で自動化されています） - 既存のしきい値を変更して、すべて完了させるのはどうでしょうか？

/admin/site_settings/category/trust

例：TL2（メンバーは trust_level_2 またはお使いの言語での同等のものに含まれます）：

pfaffman · 2023 年 9 月 3 日午前 11:38

自動化スクリプトは、バッジを取得したユーザーをグループに追加するようになりました。バッジにカスタムSQLを使用できる場合は、それを自動化できますが、信頼レベルのように聞こえます。

JammyDodger · 2023 年 9 月 3 日午前 11:48

カスタム作成の利点は理解できます。たとえば、TL3のみが一定期間の最小エンゲージメントに依存しています。そのため、このようなものも、年間を通じてエンゲージメントが低下したユーザーを各カスタムグループから除外できる可能性があります。

また、それらはストック機能に縛られず、グループ対応機能や特定のプレミアムカテゴリを利用できる可能性があります。

ただし、これらが具体的にどのように設定されているかは不明なため、トラストレベルを通じて達成可能かもしれません。

alefattorini · 2023 年 9 月 4 日午前 11:12

いいねと読まれた投稿はすべて対象です（前者は投稿だけでなく良い貢献に焦点を当てるため、後者はバランスを取るためです）。
投稿の最小数は過去1年間のみです。これはメンバーがまだ継続的に活動しているかを理解するためのパラメータです。

それは良い方法かもしれませんが、私の場合はTL1、TL2、TL3を大幅に変更する必要があり、以下の制限を考慮する必要があります。

すみません、理解できません。バッジを使用すべきですか？
上記のクエリをどのように変更すれば、バッジに挿入できますか？

JammyDodger · 2023 年 9 月 4 日午前 11:41

In that case, I think something like this could provide the manual look-up:

-- [params]
-- int :likes_received
-- int :posts_read


WITH user_activity AS (

    SELECT 
        p.user_id, 
        COUNT(p.id) as posts_count
    FROM posts p
    LEFT JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at::date >= CURRENT_DATE - INTERVAL '1 YEAR'
        AND t.deleted_at IS NULL
        AND p.deleted_at IS NULL
        AND t.archetype = 'regular'
    GROUP BY 1
)

SELECT 
    us.user_id,
    us.likes_received,
    us.posts_read_count,
    ua.posts_count
FROM user_stats us
  JOIN user_activity ua ON UA.user_id = us.user_id
WHERE us.likes_received >= :likes_received
  AND us.posts_read_count >= :posts_read
  AND ua.posts_count >= 10
ORDER BY 2 DESC, 3 DESC, 4 DESC

And tweaking it/stripping it down to just usernames would provide a list you could copy and paste into the ‘Add Users’ box on the group(s) page if you exported the results as a csv (and opened it in something like notepad, for instance):

-- [params]
-- int :likes_received
-- int :posts_read


WITH user_activity AS (

    SELECT 
        p.user_id, 
        COUNT(p.id) as posts_count
    FROM posts p
    LEFT JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at::date >= CURRENT_DATE - INTERVAL '1 YEAR'
        AND t.deleted_at IS NULL
        AND p.deleted_at IS NULL
        AND t.archetype = 'regular'
    GROUP BY 1
)

SELECT 
    u.username
FROM user_stats us
  JOIN user_activity ua ON UA.user_id = us.user_id
  JOIN users u ON u.id = us.user_id
WHERE us.likes_received >= :likes_received
  AND us.posts_read_count >= :posts_read
  AND ua.posts_count >= 10
ORDER BY 1

This is also possible. You would need one badge (and one badge query) for each group, and an accompanying automation using the 'User Group Membership through Badge` script. You could also automate the badges too rather than granting them manually by enabling the Custom Triggered Badges (Enable Badge SQL and Creating triggered custom badge queries)

There are a lot of moving parts though, so you may want to keep it simple at this stage.

alefattorini · 2023 年 9 月 5 日午前 11:02

それは素晴らしいですね！Jammy、本当にありがとうございます。

JammyDodger · 2023 年 9 月 5 日午後 12:35

心配いりません。最初の方法で期待通りの結果が得られているか確認でき、2番目の方法でグループへの追加が容易になるはずです。

調整が必要なことがあればお知らせください。

alefattorini · 2023 年 9 月 5 日午後 12:49

私はそれらを（私の下手なSQLスキルで）マージして改善しました。ユーザー名が必要な場合は、CSVをダウンロードしてユーザー名列をコピー＆ペーストするだけです。
グループを分割できるようにlikes_received_maxを追加し、上記のグループを除外しました。

例：
first_steps: 5いいね（<30）、500投稿閲覧、昨年>5投稿
beginners: 30いいね（<100）、1000投稿閲覧、昨年>10投稿
padawan: 100いいね、2000投稿閲覧、昨年>10投稿
hero: 200いいね、5000投稿閲覧、昨年>10投稿

-- [params]
-- int :likes_received
-- int :posts_read
-- int :likes_received_max
-- int :posts_count


WITH user_activity AS (
    SELECT
        p.user_id,
        COUNT(p.id) as posts_count
    FROM posts p
    LEFT JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at::date >= CURRENT_DATE - INTERVAL '1 YEAR'
        AND t.deleted_at IS NULL
        AND p.deleted_at IS NULL
        AND t.archetype = 'regular'
    GROUP BY 1
)

SELECT
    us.user_id,
    u.username,
    us.likes_received,
    us.posts_read_count,
    ua.posts_count,
    u.title
FROM user_stats us
  JOIN user_activity ua ON UA.user_id = us.user_id
  JOIN users u ON u.id = us.user_id
WHERE us.likes_received >= :likes_received
  AND us.posts_read_count >= :posts_read
  AND ua.posts_count >= :posts_count
  AND us.likes_received < :likes_received_max
ORDER BY 2 ASC, 3 ASC, 4 ASC

system · 2023 年 10 月 5 日午後 12:50

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.

トピック		返信	表示
Badge for posts with Likes from a specific group Data & reporting sql-triggered-badge	24	780	2023 年 8 月 27 日
Data explorer query for all active users (Lurkers + Posters) Data & reporting sql-query	5	914	2024 年 11 月 25 日
Stats for group members Data & reporting sql-query	4	171	2018 年 7 月 18 日
Data Explorer query to show progress towards trust level 3 requirements Data & reporting sql-query	2	2945	2023 年 4 月 29 日
The default badge queries Data & reporting reference , sql-triggered-badge	1	868	2023 年 8 月 27 日

アクティビティに基づいてグループを作成するクエリ

関連トピック