What cool data explorer queries have you come up with?

data-explorer
(Sam Saffron) #164

:blush:

Can you pick the 5 most popular queries from your carefully curated list that we are not shipping out-of-the-box yet and do a PR to add them?

Adding queries now is super easy thanks to @rishabhn

https://github.com/discourse/discourse-data-explorer/commit/931bbb67a07a57965c3da8ca471ee2283eeb1f27

9 Likes

#165

@SidV you’re a real lad, thanks for the work you put in.

5 Likes

(Carson) #166

Hi, I love the Top Quality Users in last six months query. I’m wondering… how would we write a similar query to show the Lowest quality users over the past six months? Someone who has posted a lot, but received very few likes, replies, etc.

0 Likes

(Chris Beach) #167

Try removing the “DESC” from this line:

ORDER BY sum(p.score) / count(p) DESC
3 Likes

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

0 Likes

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

0 Likes

(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'
                  ^
0 Likes

(Jay Pfaffman) #178

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

0 Likes

(Timofey) #179

unfortunately it doesn’t work either:

PG::InvalidSchemaName: ERROR:  schema "t" does not exist
LINE 12: ORDER BY t.updated_at 'asc'
                  ^
0 Likes

(Jay Pfaffman) #180

Try changing the t.* to just *

0 Likes

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

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

#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