# How many members open the Welcome PM?

**URL:** https://meta.discourse.org/t/how-many-members-open-the-welcome-pm/275043
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [February 27, 2019, 8:53pm UTC](https://meta.discourse.org/t/how-many-members-open-the-welcome-pm/275043 "2019-02-27T20:53:09Z")
**Posts on this page:** 5
**Page:** 1

<div class="post-metadata">

### Author: ![Jumanji](https://avatars.discourse-cdn.com/v4/letter/j/cab0a1/32.png) [@Jumanji](https://meta.discourse.org/u/Jumanji)
#### Post date: [February 27, 2019, 8:53pm UTC](https://meta.discourse.org/t/how-many-members-open-the-welcome-pm/275043/1 "2019-02-27T20:53:09Z")

</div>

Can somebody help with a query showing how many members open the Welcome PM?

Thanks!

---

<div class="post-metadata">

### Author: ![simon](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simon/32/339122_2.png) [@simon](https://meta.discourse.org/u/simon)
#### Post date: [February 28, 2019, 3:25am UTC](https://meta.discourse.org/t/how-many-members-open-the-welcome-pm/275043/2 "2019-02-28T03:25:23Z")

</div>

### how many members open the Welcome PM

> [@Jumanji](#):
>
> a query showing how many members open the Welcome PM?

To get a count of the number of non-admin users who have read the welcome PM sent by the discobot user, you could try this:

```sql
SELECT
COUNT(1) AS number_of_opens
FROM topics t
JOIN topic_users tu
ON tu.topic_id = t.id
JOIN users u
ON u.id = tu.user_id
WHERE t.user_id = -2
AND u.admin = false
AND tu.last_read_post_number IS NOT NULL

```

---

<div class="post-metadata">

### Author: ![Jumanji](https://avatars.discourse-cdn.com/v4/letter/j/cab0a1/32.png) [@Jumanji](https://meta.discourse.org/u/Jumanji)
#### Post date: [February 28, 2019, 10:13pm UTC](https://meta.discourse.org/t/how-many-members-open-the-welcome-pm/275043/3 "2019-02-28T22:13:49Z")

</div>

I’ll give this a go. Many thanks!

---

<div class="post-metadata">

### Author: ![Jumanji](https://avatars.discourse-cdn.com/v4/letter/j/cab0a1/32.png) [@Jumanji](https://meta.discourse.org/u/Jumanji)
#### Post date: [March 10, 2019, 3:07am UTC](https://meta.discourse.org/t/how-many-members-open-the-welcome-pm/275043/4 "2019-03-10T03:07:01Z")

</div>

Okay excellent! This worked! Thanks so much.

Now for more granularity. How can I get this number, but just for the last X months? I tried copying a part of query from another query created within the plugin, but no go.

Any thoughts?

---

<div class="post-metadata">

### Author: ![SidV](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sidv/32/119460_2.png) [@SidV](https://meta.discourse.org/u/SidV)
#### Post date: [March 11, 2019, 12:25am UTC](https://meta.discourse.org/t/how-many-members-open-the-welcome-pm/275043/5 "2019-03-11T00:25:07Z")

</div>

### Number of non-admin users who opened the welcome PM in the last N months

> [@Jumanji](#):
>
> but just for the last X months?

Try this:

```sql
-- [params]
-- int :months_ago = 1
WITH query_period as (
    SELECT
        date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
        date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
)
SELECT
COUNT(1) AS number_of_opens
FROM topics t
JOIN topic_users tu
ON tu.topic_id = t.id
RIGHT JOIN query_period qp
    ON t.created_at >= qp.period_start
JOIN users u
ON u.id = tu.user_id
WHERE t.user_id = -2
AND u.admin = false
AND tu.last_read_post_number IS NOT NULL
AND t.created_at <= qp.period_end

```