What cool data explorer queries have you come up with?

data-explorer
#18

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

(Mittineague) #19

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
1 Like
(Dave McClure) #20

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
4 Likes
(Mittineague) #21

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.

6 Likes
#22

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?

#23

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
7 Likes
Active users for specific months
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
(Felix Freiberger) #24

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.

3 Likes
(Joshua Rosenfeld) #25

Anyone able to provide the query for all users with a custom title (not a badge granted title)?

1 Like
(Felix Freiberger) #26

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 :slight_smile:

1 Like
(Joshua Rosenfeld) #27

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.

@Mittineague was able to create a query that seems to work as well in this topic.

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
1 Like
(Felix Freiberger) #28

Oops, I forgot to copy the query again after fixing that :blush:

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.

1 Like
(Joshua Rosenfeld) #29

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.

1 Like
(Chris Beach) #30

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
8 Likes
Sorting all users by total posts?
(Felix Freiberger) #31

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

Useful for tracking down suspicious PM activity.

4 Likes
(Andrew Waugh) #32

I’m sure this is a trivial one.

How do I get a list of similar usernames?

i.e.
Bill_F
Bill_F1
Bill_F2
Bill_F3

but not Bill_Fellows

(i.e. how to I get the list of users who would be candidates for a user merge?)

1 Like
(Felix Freiberger) #33

I think it isn’t, because the fuzzystrmatch module doesn’t seem to be included in the Discourse Docker base image.

(Andrew Waugh) #34

Ok, another one:

Users where:

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)

(Hosein Naseri) #35

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
etc

1 Like
(Tobias Eigen) #36

You could just export your user list and find that out in excel. Would be an interesting query to have handy nontheless.

1 Like
(Joshua Rosenfeld) #37

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.

2 Likes