What cool data explorer queries have you come up with?

(Rishabh Nambiar) #189

List all open PMs

sorted by recent activity

  t.id AS topic_id, 
  topics t 
  JOIN posts p ON t.id = p.topic_id 
  t.archetype = 'private_message' 
  AND t.user_id > 0 
  AND t.reply_count > 1 
  AND NOT t.closed 
  t.updated_at DESC
People misusing possible loopholes when PMs are disabled?

I think you should open new topic about this…

But, you may try (and learn SQL) :wink:

Point 1:

-- [params]
-- string :gname = executive
-- string :uname = SidV
SELECT u.username, g.id as "group id", g.name
FROM group_users gu, users u, groups g
WHERE g.id = gu.group_id
AND u.id = gu.user_id
AND g.name ILIKE :gname
AND u.username ILIKE :uname

Point 2

I can’t understand your point… what do you need?

Check the query list and try to adapt some query for your need.

(cosmo) #191

Basically write a query to display any topics read in category 1 or 2 by any user not assigned to group A

(Simon Cossar) #192

This query will return all topic_users (users who have viewed or posted in a topic) who are not members of the group that is given for the query’s :group_name, for topics in categories that have the group in their security settings. This can be used to find admins who have viewed a topic in a category that has group permissions added to it.

-- string :group_name

WITH allowed_users AS (
SELECT user_id
FROM group_users gu
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_name
group_categories AS (
SELECT category_id
FROM category_groups cg
JOIN groups g
ON g.id = cg.group_id
WHERE g.name = :group_name

FROM topic_users tu
JOIN topics t
ON t.id = tu.topic_id
WHERE t.category_id IN (SELECT category_id FROM group_categories)
AND tu.user_id NOT IN (SELECT user_id FROM allowed_users)
ORDER BY t.category_id
(Kris) #193

I don’t know even a bit of SQL… but would it be possible to have a query that shows me topics where I’ve been mentioned… but haven’t responded after being mentioned (I imagine this part is tricky, if it’s even possible)?

(PethuVignesh) #194

How to query the View counts of entire Topics with Particular Tag?


I’m not sure, try this:

-- [params]
-- text :tag_name
SELECT COUNT(tt.tag_id)
FROM topic_tags tt, tags t, topic_views tv, topics tp
WHERE t.id = tt.tag_id
AND tp.id = tt.topic_id
AND tv.topic_id = tp.id 
AND t.name = :tag_name
GROUP BY tt.tag_id
(Régis Hanol) #197

This query should do the trick

-- [params]
-- user_id :user

WITH mentions AS (
  SELECT target_topic_id, target_post_id, created_at
    FROM user_actions
   WHERE action_type = 7 -- mentions
     AND user_id = :user
), replies AS (
  SELECT target_topic_id, MAX(created_at) created_at
    FROM user_actions
   WHERE action_type = 5 -- replies
     AND user_id = :user
   GROUP BY target_topic_id    
SELECT DATE(m.created_at) mentioned_at, target_post_id post_id
FROM mentions m
JOIN replies r ON r.target_topic_id = m.target_topic_id
JOIN topics t ON t.id = m.target_topic_id
WHERE m.created_at > r.created_at
AND t.deleted_at IS NULL
AND NOT t.archived
AND NOT t.closed
ORDER BY m.created_at DESC
Find posts "solved" in specific month
(DaveK) #200

Would it be possible to extract admin activity for a particular month (set of days)? Kind of like the moderator reports we have access to. My instance has lots of admins which get paid for their work. It’s getting difficult adding up who did the most work. Would love to see an option to see all actions performed by a particular admin, from time read, replied PM’s, replied topics, etc., etc. I couldn’t find anything like that in this topic.

EDIT: Will pay to get this query done, but it has to show every detail tracked per admin selected.

1 Like
(Carson) #201

Is there a way to query what percentage of total posts in selected Categories over a certain time period (e.g. past 7/30 days) have been read by a group (moderators, superusers)?

(Tony) #202


Would it be possible to make a query to get posts that had tag X but been changed to tag Y in a given month? Would be wonderful if someone could help me with that.

When I click on the history of a post I can see that someone has changed the tag X to Y and when, so the data should be able to get through a query :slight_smile:

(DaveK) #203

Found a slight workaround to track basic admin activity I’ve wanted to query before. Simple grant each admin a moderator and he/she will be included in the moderator reports.

(David Kingham) #204

Is it possible to create a query that would tell me how many users have used the mute category feature?

(Mittineague) #205

I think you should be able to query the category users table for notification level 0 to get a count.

discourse/category_user.rb at master · discourse/discourse · GitHub

discourse/notification_levels.rb at master · discourse/discourse · GitHub

(Kyle Risi) #206

Hi Guys,

We have a scheme within our community in which we promote certain regulars to PosBuddy status, these are regulars who share our brands tone of voice and have show an aptitude for answering questions, driving conversation and welcoming new members within the community.

This scheme grants these users to TL4, they have their own private group within the community and have the ability to close, merge and move posts: generally keeping the community tidy. We also give these members lots of great benefits like free products, merch and exclusive invites to our development showcases. So as you can imagine there is great incentives to work their way up to TL3 and contribute to our community.

Often we will want to know which users are almost at TL3, but not quite there yet, to do this we go through hundreds of TL2 users each month and see how far they are away from reaching TL3 and check their requirements for Trust Level 3 table.

We want to make this process easier so we have a data explorer query which shows most of the information we need in a single view, however I can’t seem to work out how to include the following fields into the Query listed below:

  • Topics Replied To
  • Flagged Posts
  • Users Who Flagged
  • Likes Received: unique days
  • Likes Received: unique users
  • Silenced (All time)
  • Suspended (All time)

If anyone could help I would be forever grateful.

-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 100

t as (
    CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) as start,
    CURRENT_TIMESTAMP - (:from_days_ago * (INTERVAL '1 days')) as end
pr as (
    select user_id, 
        count(1) as visits,
        sum(posts_read) as posts_read
    from user_visits, t
    where posts_read > 0
    and visited_at > t.start
    and visited_at < t.end
    group by user_id
pc as (
  select user_id, 
      count(1) as posts_created
  from posts, t
  where created_at > t.start
  and created_at < t.end
  group by user_id
ttopics as (
 select user_id, posts_count
  from topics, t
  where created_at > t.start
  and created_at < t.end
tc as (
  select user_id, 
      count(1) as topics_created
  from ttopics
  group by user_id
twr as (
  select user_id, 
      count(1) as topics_with_replies
  from ttopics
  where posts_count > 1
  group by user_id
tv as (
 select user_id, 
        count(distinct(topic_id)) as topics_viewed
  from topic_views, t
  where viewed_at > t.start
  and viewed_at < t.end
  group by user_id
likes as (
      post_actions.user_id as given_by_user_id, 
      posts.user_id as received_by_user_id
  from t, post_actions
  left join posts
  on post_actions.post_id = posts.id
  where post_actions.created_at > t.start
  and post_actions.created_at < t.end
  and post_action_type_id = 2

lg as (
  select given_by_user_id as user_id, 
      count(1) as likes_given
  from likes
  group by user_id
lr as (
  select received_by_user_id as user_id, 
      count(1) as likes_received
  from likes
  group by user_id
e as (
  select email, user_id
  from user_emails u
  where u.primary = true
select pr.user_id,
       coalesce(visits,0) as "Visits",
       coalesce(topics_viewed,0) as "Topics Viewed",
       coalesce(posts_read,0) as "Posts Read", 
       coalesce(posts_created,0) as "Posts Created",
       coalesce(topics_created,0) as "Topics Created",
       coalesce(topics_with_replies,0) as "Topics with Replies",
       coalesce(likes_given,0) as "Likes Given",
       coalesce(likes_received,0) as "Likes Received"
from pr
left join tv using (user_id)
left join pc using (user_id)
left join tc using (user_id)
left join twr using (user_id)
left join lg using (user_id)
left join lr using (user_id)
left join e using (user_id)
left join users on pr.user_id = users.id
order by 
  visits desc, 
  posts_read desc, 
  posts_created desc
1 Like
(Angus McLeod) #207

In the course of working for @quimgil on improving his use of Discourse groups as a support system, we’ve developed some queries (more to follow). Let us know if you can spot any issues or improvements :slight_smile:

Time to first response for a support group

  • Average number of days for a set period: (2019-04-04 to 2019-04-06 in example)
  • Only counts business days (i.e. excluding Saturday and Sunday)
  • Does not count selected public holidays (2019-04-11 in example)
  • Group name in example is ‘support’
SELECT AVG(t.days)::float AS "Average business days to first response"
  SELECT t.id, t.title, t.created_at, MIN(p.created_at) as "first_reply_created_at", (
    SELECT count(*) FILTER (
      WHERE d not in ('2019-04-11')
      AND extract('ISODOW' FROM d) < 6
    FROM generate_series(t.created_at::timestamp::date
                        , MIN(p.created_at)::timestamp::date
                        , interval '1 day') as s(d)
  ) as "days"
  FROM topics t
  INNER JOIN posts p ON p.topic_id = t.id AND (
    CASE WHEN p.post_number = 1
    THEN p.via_email IS TRUE 
    ELSE true 
  WHERE t.archetype = 'private_message'
  AND t.id IN (
    SELECT topic_id FROM topic_allowed_groups
    WHERE group_id IN (SELECT id FROM groups WHERE name ilike 'support')
  AND t.deleted_at IS NULL
  AND t.created_at::timestamp::date >= '2019-04-04'
  AND t.created_at::timestamp::date <= '2019-04-06'
  AND p.deleted_at IS NULL
  AND p.post_number > 1
  GROUP BY t.id
) t

To test the query is working properly, you can change the SELECT from an average to

t.days as "business days", t.title, t.created_at, t.first_reply_created_at

For example, on my Sandbox when I do this, I get


  • Do you want to count replies on the same day as 0 or 1? (currently it’s 1)
  • How do you want to handle messages with no replies? (i.e. should they be excluded, or how many days should they be ascribed?). Currently messages with no replies are excluded from the count.

Number of messages received in a given group.

  • Group name in example is ‘support’
  • Count for a set period: (2019-04-04 to 2019-04-06 in example)
SELECT count(t) as "Number of messages"
FROM topics t
WHERE t.archetype = 'private_message'
AND t.id IN (
  SELECT topic_id FROM topic_allowed_groups
  WHERE group_id IN (SELECT id FROM groups WHERE name ilike 'support')
  SELECT via_email FROM posts
  WHERE topic_id = t.id AND post_number = 1 
AND t.deleted_at IS NULL
AND t.created_at::timestamp >= '2019-04-04'::timestamp
AND t.created_at::timestamp <= '2019-04-06'::timestamp
[Paid] Data explorer queries for ticketing system use case

@SidV just going to sneak this in here if I can, I think the User Participation Statistics was your creation [Sorry if I got that wrong Dear Author], I’m looking for a way to apply this to a defined user group, I’ve got an open job for it if anyone else can get it working. If we can get it working anyone here can have it for free too.

Add "named group" as a scope for the "User Participation Statistics" script in data explorer


Got this sorted promptly with the professional talent of @pfaffman, our thanks to him. The script I’ll turn over for the community. They way we will be using it is by letting in 100 registrants or so, locking public access down then using the script to identify potential conversions and direct someone to say hi to them.

In this manner we can better predict our conversions, make better use of staff time and adjust the public intake up or down to suit assimilation onto our board.

Change your group ID to suit.

-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 30
-- int :group_id = 73
    SELECT CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) AS first,
        CURRENT_TIMESTAMP - (:from_days_ago * (INTERVAL '1 days')) AS last
group_members as (
    SELECT user_id FROM
    group_users gu
    WHERE gu.group_id= :group_id
pr AS (
    SELECT user_id, COUNT(1) AS visits,
        SUM(posts_read) AS posts_read
    FROM user_visits, t
    WHERE posts_read > 0
        AND visited_at > t.first
        AND visited_at < t.last
pc AS (
    SELECT user_id, COUNT(1) AS posts_created
    FROM posts, t
        created_at > t.first
        AND created_at < t.last
ttopics AS (
    SELECT user_id, posts_count
    FROM topics, t
    WHERE created_at > t.first
        AND created_at < t.last
tc AS (
    SELECT user_id, COUNT(1) AS topics_created
    FROM ttopics
    GROUP BY user_id
twr AS (
    SELECT user_id, COUNT(1) AS topics_with_replies
    FROM ttopics
    WHERE posts_count > 1
    GROUP BY user_id
tv AS (
    SELECT user_id,
        COUNT(DISTINCT(topic_id)) AS topics_viewed
    FROM topic_views, t
    WHERE viewed_at > t.first
        AND viewed_at < t.last
    GROUP BY user_id
likes AS (
    SELECT post_actions.user_id AS given_by_user_id,
        posts.user_id AS received_by_user_id
    FROM t,
        LEFT JOIN
        ON post_actions.post_id = posts.id
        post_actions.created_at > t.first
        AND post_actions.created_at < t.last
        AND post_action_type_id = 2
lg AS (
    SELECT given_by_user_id AS user_id,
        COUNT(1) AS likes_given
    FROM likes
    GROUP BY user_id
lr AS (
    SELECT received_by_user_id AS user_id,
        COUNT(1) AS likes_received
    FROM likes
    GROUP BY user_id
e AS (
    SELECT email, user_id
    FROM user_emails u
    COALESCE(topics_viewed, 0) AS topics_viewed,
    COALESCE(posts_read, 0) AS posts_read,
    COALESCE(posts_created, 0) AS posts_created,
    COALESCE(topics_created, 0) AS topics_created,
    COALESCE(topics_with_replies, 0) AS topics_with_replies,
    COALESCE(likes_given, 0) AS likes_given,
    COALESCE(likes_received, 0) AS likes_received
FROM group_members, pr
LEFT JOIN tv USING (user_id)
LEFT JOIN pc USING (user_id)
LEFT JOIN tc USING (user_id)
LEFT JOIN twr USING (user_id)
LEFT JOIN lg USING (user_id)
LEFT JOIN lr USING (user_id)
LEFT JOIN e USING (user_id)
LEFT JOIN users ON pr.user_id = users.id
WHERE group_members.user_id=pr.user_id
    visits DESC,
    posts_read DESC,
    posts_created DESC

See this message #67
The guru’s name is: @mcwumbly :genie:

Maybe he can help you.

1 Like
(Sven) #211

Users (In Specific Group) Last Seen Since N Days Ago

-- [params] 
-- int :member_group 
-- int :days_since_last_activity 
SELECT u.id                AS user_id, 
       Age(u.last_seen_at) AS last_seen, 
       g.id                AS GROUP_ID 
FROM   users u 
       join group_users gu 
         ON gu.user_id = u.id 
       join GROUPS g 
         ON g.id = gu.group_id 
WHERE  Age(u.last_seen_at) >= ( :days_since_last_activity * '1 day' :: interval ) 
       AND g.id = :member_group 
ORDER  BY u.last_seen_at

Users In Specific Group(s) BUT NOT In Other Group(s)

Info: The Parameters have to be written as arrays. For example: {1,2,3}

-- [params]  
-- string :opt_in_groups
-- string :opt_out_groups
   u.id AS user_id,
   g.id AS GROUP_ID 
   users u 
      group_users gu 
      ON gu.user_id = u.id 
      GROUPS g 
      ON g.id = gu.group_id 
   g.id = ANY (:opt_in_groups::int[]) 
   AND u.id NOT IN 
         u.id AS user_id 
         users u 
            group_users gu 
            ON gu.user_id = u.id 
            GROUPS g 
            ON g.id = gu.group_id 
         g.id = ANY (:opt_out_groups::int[])