What cool data explorer queries have you come up with?

data-explorer
(Carson) #168

Excellent, thanks. I really appreciate it!

I also changed this " HAVING count(p.id) > 5" to get results that were more relevant for my community. I’ll keep tinkering with that. It seems that our moderator team is intervening with people before they have posted too often if their posts are low quality. It looks like this query can help validate my sense about someone and also give an early read on when someone isn’t starting off well.

1 Like
(Rishabh Nambiar) #169

Number of active users per login method

Facebook vs Twitter vs Google vs Github

WITH target_user_ids AS (
SELECT
id
FROM users
WHERE staged = false
AND active = true
AND last_seen_at IS NOT NULL
)

SELECT (
       SELECT Count(DISTINCT user_id)
       FROM user_associated_accounts
       WHERE provider_name = 'google_oauth2'
       AND user_id IN(SELECT id FROM target_user_ids)) AS google,
       (
       SELECT Count(DISTINCT user_id)
       FROM user_associated_accounts
       WHERE provider_name = 'facebook'
       AND user_id IN(SELECT id FROM target_user_ids)) AS facebook,
       (
       SELECT Count(DISTINCT user_id)
       FROM   github_user_infos
       WHERE user_id IN(SELECT id FROM target_user_ids)) AS github,
       (
       SELECT Count(DISTINCT user_id)
       FROM user_associated_accounts
       WHERE provider_name = 'twitter'
       AND user_id IN(SELECT id FROM target_user_ids)) AS twitter
Output

7 Likes
Configuring Facebook login for Discourse
#174

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

Thanks!

(Simon Cossar) #175

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
11 Likes
#176

I’ll give this a go. Many thanks!

(Timofey) #177

Hello! Help me please.
I need to get the topics and sort them by update date.
My query:

-- [params]
-- string :ord
SELECT distinct t.*
FROM topics t
WHERE  posts_count = 1 and deleted_at is null
ORDER BY updated_at :ord

But it does not work:

PG::UndefinedObject: ERROR:  type "updated_at" does not exist
LINE 12: ORDER BY updated_at 'asc'
                  ^
(Jay Pfaffman) #178

Try t.updated_at since that’s why you asked for

(Timofey) #179

unfortunately it doesn’t work either:

PG::InvalidSchemaName: ERROR:  schema "t" does not exist
LINE 12: ORDER BY t.updated_at 'asc'
                  ^
(Jay Pfaffman) #180

Try changing the t.* to just *

(Timofey) #181

This method did not help, as well as others tried by me.
But it works if you remove the parameter from the request, for example:

-- [params]
-- string :ord
SELECT distinct t.*
FROM topics t
WHERE  posts_count = 1 and deleted_at is null
ORDER BY t.updated_at desc
#182

Yes., only declare “params” if you will USE them on query.

You should read this: Declaring Parameters

Good luck

2 Likes
(Timofey) #183

thanks for manual. But I’m interested in, is it possible to pass the asc or desc parameter to ORDER BY?

-- [params]
-- string :ord
SELECT distinct *
FROM topics t
ORDER BY t.updated_at :ord
#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:

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

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

(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