# Pull Event RSVP-Liste über Data Explorer

**URL:** <https://meta.discourse.org/t/pull-event-rsvp-list-via-data-explorer/323119>\
**Category:** Data & reporting\
**Tags:** events, sql-query\
**Created:** [23. August 2024 um 18:14 UTC](https://meta.discourse.org/t/pull-event-rsvp-list-via-data-explorer/323119 "2024-08-23T18:14:28Z")\
**Posts on this page:** 1\
**Showing post:** 4

<div class="post-metadata">

**Author:** ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\
**Post date:** [23. August 2024 um 19:12 UTC](https://meta.discourse.org/t/pull-event-rsvp-list-via-data-explorer/323119/4 "2024-08-23T19:12:20Z")

</div>

Wie wäre es mit so etwas:

```sql
-- [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

```

Für den Parameter `event_url` akzeptiert er die Topic-URL, aber mit der Beitragsnummer am Ende, also `https://developer.sailpoint.com/discuss/t/deploying-the-beyondtrust-password-safe-connector-for-identityiq-with-michel-bluteau/68228/1` (anstelle der Topic-Version, die kein `/1` am Ende hat).

---

_[View the full topic](https://meta.discourse.org/t/pull-event-rsvp-list-via-data-explorer/323119)._