Data Explorer を介してプルイベント RSVP リストを取得

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

こんなのはどうでしょうか：

-- [params]
-- text :keyword
-- int :min_engagements
-- null string :tag_name
-- post_id :event_post

WITH keyword_posts AS (

    SELECT
        p.id AS post_id
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
      LEFT JOIN topic_tags tt ON tt.topic_id = t.id
      LEFT JOIN tags tg ON tg.id = tt.tag_id
    WHERE p.raw ILIKE '%' || :keyword || '%'
      AND (:tag_name is null OR tg.name = :tag_name)
),

user_engagement AS (

    SELECT
        ua.user_id,
        COUNT(DISTINCT ua.target_post_id) AS engaged_posts_count
    FROM user_actions ua
    JOIN keyword_posts kp ON kp.post_id = ua.target_post_id
    WHERE ua.action_type IN (1, 4, 5, 6)
    AND ua.user_id NOT IN (SELECT user_id FROM group_users WHERE group_id = 41)
    GROUP BY ua.user_id
)

SELECT
    ue.user_id,
    ue.engaged_posts_count
FROM user_engagement ue
WHERE ue.engaged_posts_count >= :min_engagements
  AND ue.user_id NOT IN (SELECT user_id FROM discourse_post_event_invitees WHERE post_id = :event_post AND status = 0)
ORDER BY ue.engaged_posts_count DESC

event_url パラメータは、トピックの URL を受け入れますが、末尾に投稿番号が付加されます。たとえば、https://developer.sailpoint.com/discuss/t/deploying-the-beyondtrust-password-safe-connector-for-identityiq-with-michel-bluteau/68228/1 のようになります（末尾に /1 がないトピックバージョンではなく）。