سحب قائمة RSVP لحدث Pull عبر Data Explorer

مجتمع البيانات والتقارير
,
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، فإنه يقبل عنوان الموضوع ولكن مع رقم المنشور في النهاية، مثل https://developer.sailpoint.com/discuss/t/deploying-the-beyondtrust-password-safe-connector-for-identityiq-with-michel-bluteau/68228/1 (بدلاً من نسخة الموضوع التي لا تحتوي على /1 في النهاية).