What cool data explorer queries have you come up with?

data-explorer
#184

I think it is not possible.

Maybe @Simon_Cossar may help us here.
I think “asc” and “desc” are keywords, not values, so you can’t pass them by parameters. :thinking:

0 Likes

(Simon Cossar) #185

I don’t think it’s possible to pass a keyword as a parameter, but a boolean :desc parameter could be used in a CASE expression.

--[params]
-- boolean :desc = false

SELECT
*
FROM generate_series(1, 10) AS num
ORDER BY
  CASE WHEN :desc THEN num END DESC,
  CASE WHEN NOT :desc THEN num END ASC
5 Likes

#186

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?

0 Likes

#187

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

(cosmo) #188

I could use some help. I don’t know my head from my butt with SQL.

I am admining a board for my labor union. I’m an elected rep, but I’m not in leadership. The reason I’m admining it is because I have experience with discourse already, I set it up to modernize how we communicate internally (team collaboration), and I’m the only one at the moment (of 20 of us) who really has any clue how to do anything more advanced in a tech related behind the scenes capacity greater than using a web builder to build a website. Point is our group is end user tech savy, but not “behind the scenes.”

As this is a work space for all of us, including leadership, I’ve setup a couple categories for them to work in which I shouldn’t be able to snoop in. (call it above my pay grade).

Since there’s no real way to hide content from an admin, I created a theme component with CSS to:

  • hide messages buttons in user profiles
  • hide impersonate button in admin profiles
  • hide topic preview of categories on categories page.
  • hide the topic list within the categories from everyone.
    • Then add visibility to the topic lists to members a (primary) group.

Bottom line, I’m creating a series of log and audit trails to be available should they wish to check that I’m not snooping.

  1. they can look and see in site logs if i ever touch that theme component (edit/disable) without them being aware, they can ask why and see detail
  2. if I ever used the impersonate or view message feature, that would be in the logs as well.

Basically, If I ever tamper with the CSS I wrote to hide this stuff they can see and ask me to explain myself.

  1. if I added myself to their group so the content is visible to me, in the group logs it shows me adding/removing myself in the logs.they can ask me to explain myself

What I need help with:
if I ever view the page with the css style-sheets disabled, I can see the URLs to the topics. Then obviously I can open the topics directly. What I want is a data explorer query they (executive team who all get admin access) can run from time to time that shows topics viewed within a category by any user not a member of the “executive” group.
They have no idea this can be done, but I want every base covered. I want to future proof this beyond me. As best as I can with the tools available.

So again, I need a data explorer query to do:

  1. if user is not part of group "executive"
  2. then display topics viewed by user(s) in category “EB” (or category id: 34)

This combined with the CSS white out I’ve applied creates enough of a paper trail that says if someone (me or anyone not on the executive team who may be given admin access in the future), has viewed topics within that category and they are not part of the executive team, they really have had to go out of their way and it’s intentional snooping.

Can anyone help with this?

0 Likes

(Rishabh Nambiar) #189

List all open PMs

sorted by recent activity

SELECT
  t.id AS topic_id, 
  t.user_id 
FROM
  topics t 
  JOIN posts p ON t.id = p.topic_id 
WHERE
  t.archetype = 'private_message' 
  AND t.user_id > 0 
  AND t.reply_count > 1 
  AND NOT t.closed 
GROUP BY
  t.id 
ORDER BY
  t.updated_at DESC
4 Likes

People misusing possible loopholes when PMs are disabled?
#190

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.

3 Likes

(cosmo) #191

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

0 Likes

(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.

--[params]
-- 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
)

SELECT
tu.user_id,
tu.topic_id,
tu.last_visited_at::date
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
3 Likes

(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)?

4 Likes

(PethuVignesh) #194

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

0 Likes

#196

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

(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
6 Likes

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)?

0 Likes

(Tony) #202

Hello

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:

0 Likes

(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.

0 Likes

(David Kingham) #204

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

0 Likes

(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

2 Likes

(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

with
t as (
  select 
    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 (
  select 
      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