# פנייה לתמיכה בהרשמה פשוטה לאירוע פיזי דרך mailto ומשתמשים בשלבים

**URL:** https://meta.discourse.org/t/query-to-support-low-friction-signup-at-an-in-person-event-via-mailto-and-staged-users/291587
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [14 בינואר,‏ 2024,‏ 9:37am UTC](https://meta.discourse.org/t/query-to-support-low-friction-signup-at-an-in-person-event-via-mailto-and-staged-users/291587 "2024-01-14T09:37:41Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![nathank](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/nathank/32/290039_2.png) [@nathank](https://meta.discourse.org/u/nathank)
#### Post date: [14 בינואר,‏ 2024,‏ 9:37am UTC](https://meta.discourse.org/t/query-to-support-low-friction-signup-at-an-in-person-event-via-mailto-and-staged-users/291587/1 "2024-01-14T09:37:41Z")

</div>

This query returns all users (including their emails) who PM / email into a group inbox, excluding the members of the group.

The use case is for a low friction way to enable people at a physical event to create a staged user for my site.

I’ve found that at an in-person event, many potential users struggle to complete the sign-up process (which includes typing their email address (correctly), username choosing, and password generation as a minimum. It also requires validation of email and subsequent signing in. This is a big ask when standing around chatting to people / listening to a speaker / eating food / etc!

This can be lessened somewhat with OAuth2, but there is still some stuff to do and the less technically savvy seem to struggle with it too.

### My Solution

1. Create a semi-private group with a catchy email-in address (i.e. join@mycommunity.url)

2. Present a QR code which encodes a mailto: link to that address

3. Put the good folk who will manage the subsequent joining up of these people in the group

4. Add the following query to [Data Explorer](https://meta.discourse.org/t/32566?silent=true), and give access to the group:

```sql
-- [params]
-- string :group_name

WITH group_members AS (
    SELECT gu.user_id 
    FROM group_users gu
    JOIN groups g ON g.id = gu.group_id
    WHERE g.name = :group_name
),

non_group_users AS (
    SELECT u.id, u.username, ue.email, u.created_at
    FROM users u
    JOIN user_emails ue ON ue.user_id = u.id
    WHERE NOT EXISTS (
        SELECT 1 
        FROM group_members gm 
        WHERE u.id = gm.user_id
    ) AND ue.primary = TRUE
),

group_messages AS (
    SELECT tg.topic_id 
    FROM topic_allowed_groups tg
    JOIN groups g ON g.id = tg.group_id
    WHERE g.name = :group_name
),

group_message_participants AS (
    SELECT gm.topic_id, tau.user_id AS tau_user_id FROM group_messages gm
    JOIN topic_allowed_users tau ON tau.topic_id = gm.topic_id
    JOIN non_group_users ngu ON ngu.id = tau.user_id
)

SELECT ngu.username, ngu.email, ngu.created_at FROM group_message_participants gmp
JOIN non_group_users ngu ON ngu.id = gmp.tau_user_id
GROUP BY ngu.username, ngu.email, ngu.created_at
ORDER BY ngu.username

```

There is a bit of chasing up to do afterwards, but at least you capture their email addresses reliably in a way that only requires two or three clicks / touches of the user!