How would you adjust this with parameters so that previous months can also be checked? Eg: read between 62 and 31 days ago (or similar)?
I haven’t figured out a “between” way of doing this, but using “params” can provide a selectable “back to”
-- [params] -- int :interval = 30 select username from users where last_posted_at > current_timestamp - interval ':interval' day
should be able to do something like this (starting where @Mittineague left off):
-- [params] -- int :until_days_ago = 30 -- int :since_days_ago = 60 select username from users where last_posted_at > current_timestamp - interval ':since_days_ago' day and last_posted_at < current_timestamp - interval ':until_days_ago' day
Thanks, the call of Labor Day burgers was distracting me and all I could think of was finding a way to put current_timestamp into the params and getting to the burgers. At times my stomach overpowers my brain.
Ok, so what about ‘posted during that period’ – not last posted during that period.
i.e. a count of active (made at least one post) members during that period?
Got what I need (thanks @meglio) so updating this for future posterity.
-- [params] -- date :date_from -- date :date_to -- int :min_posts = 1 WITH user_activity AS ( SELECT p.user_id, count (p.id) as posts_count FROM posts p LEFT JOIN topics t ON t.id = p.topic_id WHERE p.created_at::date BETWEEN :date_from::date AND :date_to::date AND t.deleted_at IS NULL AND t.visible = TRUE AND t.closed = FALSE AND t.archived = FALSE AND t.archetype = 'regular' AND p.deleted_at IS NULL GROUP BY p.user_id ) SELECT COUNT(user_id) FROM user_activity WHERE posts_count >= :min_posts
Is there a Data Explorer for Dummies article somewhere?
What's the difference between "User Visits" and "Active Users"
How can I count posts in last month by a specific group of users?
Strange problem with Data Explorer
Strange problem with Data Explorer
List of all Members of a Group with Custom Field
We use this query to get a list of the name and matriculation number of all team members:
SELECT users.name, user_custom_fields.value as matriculation FROM users JOIN group_users ON users.id = group_users.user_id JOIN groups ON groups.id = group_users.group_id JOIN user_custom_fields ON users.id = user_custom_fields.user_id WHERE groups.name = 'Team' AND user_custom_fields.name = 'user_field_2'
Simply insert the group name and the custom field id in the last two names.
Anyone able to provide the query for all users with a custom title (not a badge granted title)?
I don’t think this is strictly possible, because a custom title could be character-by-character identical to a badge granted one (possible of a badge the user doesn’t have).
Here’s a query that might do what you want:
SELECT username FROM users WHERE title IS NOT NULL AND ( SELECT count(*) FROM badges WHERE allow_title = true AND name = title ) = 0 AND ( SELECT count(*) FROM groups WHERE groups.title = users.title ) = 0
It selects every title any user has, minus every title where a corresponding badge exists, minus every title that could come from a group membership. It does not test whether the user has the badge or the group membership.
Maybe it does help you, at least as a starting point
Thanks @fefrei, that seems to work, but as you mentioned it might not cover all the edge cases. I did have to change the first line
SELECT title FROM users to
SELECT username FROM users as I was looking for the usernames, not the custom titles.
SELECT users.username , users.title FROM users JOIN user_profiles ON user_profiles.user_id = users.id WHERE users.title IS NOT NULL AND users.title NOT LIKE '' AND user_profiles.badge_granted_title IS NOT TRUE
Oops, I forgot to copy the query again after fixing that
That query looks better – I didn’t find the
badge_granted_title flag. I’m not sure how this query handles titles granted by group membership, though.
Nor am I. However, this accomplishes our goal. For some background: we currently don’t have any badge_granted_titles available to users, as we have no custom badges, the only titles are regular and leader, and we’ve “disabled” TL3. We have granted titles in the past to exceptional community members. The community started asking about titles, so the mod team started discussing options. One thing that was agreed upon is that we wanted the custom titles to stand out from the “everyday” badge_granted_titles. To apply CSS to them, we needed to add all the users to a group, hence the request for a query to select them.
Top quality users in last six months
Top 20 users by average post score.
Post scores are calculated based on reply count, likes, incoming links, bookmarks, average time (reading?) and read count.
SELECT sum(p.score) / count(p) as "average score per post", count(p.id) as post_count, p.user_id FROM posts p JOIN users u ON u.id = p.user_id WHERE p.created_at >= CURRENT_DATE - INTERVAL '6 month' AND NOT u.admin AND NOT u.silenced AND u.active GROUP by user_id, u.views HAVING count(p.id) > 50 ORDER BY sum(p.score) / count(p) DESC LIMIT 20
Who has been sending the most messages in the last week?
SELECT user_id, count(*) AS message_count FROM topics WHERE archetype = 'private_message' AND subtype = 'user_to_user' AND age(created_at) < interval '7 days' GROUP BY user_id ORDER BY message_count DESC
I’m sure this is a trivial one.
How do I get a list of similar usernames?
but not Bill_Fellows
(i.e. how to I get the list of users who would be candidates for a user merge?)
I think it isn’t, because the
fuzzystrmatch module doesn’t seem to be included in the Discourse Docker base image.
Ok, another one:
User has uploaded an avatar
User has filled in custom field “X”
Custom field “X” includes a category name as a substring
User has Posted in that category at least every 3rd day since they joined.
Group by Category name, sort by post frequency.
(I’m looking for “category moderator” candidates)
Can someone help me with a query to find topmost email domains, users use as their email? perhaps it would be very good if it sorts the domains like:
gmail … 1234
yahoo … 543
You could just export your user list and find that out in excel. Would be an interesting query to have handy nontheless.
I’ve got a quick Data Explorer Query request. Does anyone know what table stores user login date information? We’re trying to determine which users logged into the forums before a certain date. Ideally the query would give usernames, emails, and the date they signed in before by closest to a chosen date.