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.
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
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
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.
-- [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
Yes. A lot of badges have the option to win more that one time.
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
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.
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.
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
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
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
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
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
-- [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
