What cool data explorer queries have you come up with?

Hi @vinothkannans,

Thank you! That appears to return a list of usernames and the number of topics each of those usernames has created that is now tagged “outdated.”

I’m hoping to be able to see who is doing the tagging. I want to challenge my TL3s to a tagging contest to get some outdated topics cleaned up, but I need to be able to keep a score of who does the most tagging.

We’ve been able to do a similar contest in the past “solving” topics, because marking a topic solved is an action_type in user_actions, but I’m not seeing the same kind of data available for adding a tag to a topic.

I was thinking there might be a way to get creative somehow with “last edit” (does tagging count as an edit?) figuring out which user took that action, and maybe matching that timestamp to the “updated” timestamp in topic_tags… but I’m in over my head. :dizzy:

1 Like

Hi All,
Can someone please help me with another active users query please

What I am looking for is a query of which will return the MMM-YYYY and number of active users in that month - going back as far as it can if possible.

I am trying to build up some historic data and whilst I know there is a query in which will return it based on the time frame you give - I am finding it difficult to tweak it in order to return the value per month for every month.

I have put together one for New Users per month - and now want to corrolate that with the number of active users in the same months.

I am using the following for the new users:

SELECT date_trunc('month', created_at) AS Month, count(id)
from users
group by 1
order by 1

Thanks :slight_smile:

2 Likes

Here’s a fun one.

Total number of post reads:

SELECT sum(posts_read_count) FROM user_stats

If you have a busy forum the number will increase almost constantly :smiley:

I’m not sure what happens when someone goes to the end of a long unread topic. Do those unread posts get added to the posts_read_count?

3 Likes

Hi all,

I just wrote a post here on how our users mis-use (abuse?) the poll-feature to create their own pub quiz.

I created a small query that gets the results of a post with N polls, including which user voted for which poll option.

This is that query, perhaps it’s useful for others as well. Please note that the link contains more information on how we set up the quiz and how we get the results.

-- [params]
-- int :topic_id
-- int :post_number

SELECT polls.name AS "Poll name", poll_options.html AS "Answer", poll_votes.user_id AS "User ID", users.username AS "Username"
	FROM poll_options
	INNER JOIN poll_votes ON poll_options.id=poll_votes.poll_option_id
	INNER JOIN polls ON polls.id=poll_votes.poll_id
	INNER JOIN users ON users.id=poll_votes.user_id
	WHERE poll_options.id IN (
		SELECT id FROM poll_options WHERE poll_options.poll_id IN (
			SELECT id FROM polls WHERE post_id IN (
				SELECT id FROM posts WHERE topic_id=:topic_id AND post_number=:post_number ) 
			)
		)
	ORDER BY polls.name, html
4 Likes

Has anyone come up with some SQL to display a list of users (top 10 maybe), ordered by the total number of Badges they have?

I’ve had a poke around in the Data Explorer and looked at the “user_badges” table and can see there is a “user_id” column and “badge_id” column but I’m not skilled enough in SQL to be able to run some kind of count or join query to produce a top ten list.

Has anyone done something like this already?

Users ordered by badge count

Test this:

-- [params]
-- int :posts = 100
-- int :top = 10
SELECT u.username, count(ub.id) as "Badges"
FROM user_badges ub, users u, user_stats us
WHERE u.id = ub.user_id
AND u.id = us.user_id
AND us.post_count > :posts
AND (u.admin = 'f' AND u.moderator = 'f')
GROUP BY u.username
ORDER BY count(ub.id) desc
LIMIT :top
6 Likes

Hi @SidV :wave:t2:

This works quite well, thanks!

However it’s not quite accurate…

Here’s what I get:

37

Yet hovering over a few of those users shows different numbers.

Memento has 44 (not 48):

40

Brian has 33 (not 35):

55

Yet Paul does actually have 34!

21

If I remove the line that’s excluding admins, it goes a little haywire:

42

Ping has 52 (not 179):

53

But Ozone does indeed have 47:

30

Any idea what might be causing these inaccuracies @SidV ?

Thanks once again for your help on this one :smiley:

Yes. A lot of badges have the option to win more that one time. :thinking:

Check this if it more accurate:

-- [params]
-- int :posts = 100
-- int :top = 10
SELECT u.username, count(ub.id) as "Badges"
FROM user_badges ub, users u, user_stats us, badges b
WHERE u.id = ub.user_id
AND u.id = us.user_id
AND b.id = ub.badge_id
AND us.post_count > :posts
AND (u.admin = 'f' AND u.moderator = 'f')
AND b.multiple_grant = 'f'
GROUP BY u.username
ORDER BY count(ub.id) desc
LIMIT :top
4 Likes

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.

Try removing the “DESC” from this line:

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

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

A post was split to a new topic: Number of active users per login method

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

Using a CASE expression to order results

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

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

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

Users who have accessed a topic in a protected category

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

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

topics where user mentioned and hasn’t responded after being mentioned

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