How many members open the Welcome PM?

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

Thanks!

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:

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
15 Likes

I’ll give this a go. Many thanks!

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?

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

Try this:

-- [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
4 Likes