What cool data explorer queries have you come up with?

data-explorer

#113

I like your idea.

I have your situation, and I control the PMs with this query:

That should help you :slight_smile:


#114

Oh that’s awesome mate, I can export those results then draw against them with random.org and check those users pms for being cheeky. I think random actually lets your save the draw list for reference as well if you pay them a small fee so there’s proof of no foul play on the admin side.

Nice one @SidV


#115

I have a new idea @DNSTARS !

What if you can “search” in the PMs terms like “buy” or “drugs:lol:

Check this out:

-- [params]
-- int :limit = 10
-- string :term = %term%
SELECT p.user_id, p.topic_id, p.post_number, p.raw, p.created_at::date
FROM posts p
LEFT JOIN topics t on t.id = p.topic_id
WHERE t.archetype = 'private_message'
  AND t.title <> 'WELCOME-TITLE-from-discobot'
  AND p.created_at::date > now()::date - 8
  AND p.raw ILIKE :term
ORDER BY p.created_at DESC
LIMIT :limit

Replace WELCOME-TITLE-from-discobot by the exact title of your welcome message, so that all welcome messages generated automatically are excluded.

The query’s idea source is from guru’s @meglio posted here.

I only add some little changes :wink:

Query list updated :rocket:


#116

It stumbled over how to handle the emoji, our welcome is :robot: Greeetings! so just left it as greetings and let it run and it largely works - we have about 20+ catch alls phrases though on the watched words log/flag section.

Because someone for example could be just chatting about the best LSD experience they ever had to a friend which is fine, what is not fine is “Where do I get” “How do I make” “Who has the best?” so we use the baked in flagging system to catch those and for that purpose it works well.

Legally though since there is “more” we could be doing to be doing everything “Reasonably” expected of us if it ever came to it, I can now say the community flags inappropriate content (as they are good and know the rules), we moderate, we have auto system flags, watched phrases and terms and we randomly spot check PMs for untoward behaviour.

So - yes your second one works, but is (without sounding mean) redundant for us specifically, since talking about drugs isn’t illegal - at least where we are, but trying to score or manufacture or whatever else is which we want no part of.

Thanks again mate, that first draw is minimal manual work and can be given to a member of staff to deal with and it’s systematic. :peace_symbol:


(John Waltrip) #117

I’m trying to put together a query that will show users of a group that don’t have a particular badge, like Certified.

Something along the lines of "give me all the users from group VIP where badge Certified does not exist. Thank you.


#118

Well, let’s try this:

WITH exclude_badge AS (
SELECT gu.user_id
FROM badges b, user_badges ub, users u, group_users gu
WHERE u.id = ub.user_id
AND ub.badge_id = b.id
AND u.id = gu.user_id
AND b.name = 'Certified'
AND gu.id = 10 
)

SELECT
u.id AS user_id
FROM users u
WHERE u.id NOT IN (SELECT * FROM exclude_badge)
ORDER BY user_id
LIMIT 10

(John Waltrip) #119

Thanks for your help Sid, I made a couple of tweaks and got it to work with the following:

SELECT gu.user_id
FROM badges b, user_badges ub, users u, group_users gu
WHERE u.id = ub.user_id
AND ub.badge_id = b.id
AND u.id = gu.user_id
AND b.name = 'Certified'
AND gu.group_id = 42
)

SELECT
u.id AS user_id
FROM users u, group_users gu
WHERE u.id = gu.user_id
and gu.group_id = 42
and u.id NOT IN (SELECT * FROM exclude_badge)
ORDER BY user_id
LIMIT 100

#120

Great! Let’s add params and update my query list :wink:

Final version:


(Charles) #121

I’m looking for a query that would give me usage/participation stats for all users in a specific group. We have volunteers who help the community and have them all in primary group “ninjas.” I’m looking to get a table with data that includes things like: visits, topics read, posts read, topics created, topics replied to, private message count, likes given, likes received, etc. I’d like to be able to input the time period and the group name as a param. I started with this query which I found in this topic, but not sure how to limit it to a specific group.

-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 30

with
t as (
  select 
    CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) as start,
    CURRENT_TIMESTAMP - (:from_days_ago * (INTERVAL '1 days')) as end
),
pr as (
    select user_id, 
        count(1) as visits,
        sum(posts_read) as posts_read
    from user_visits, t
    where posts_read > 0
    and visited_at > t.start
    and visited_at < t.end
    group by user_id
),
pc as (
  select user_id, 
      count(1) as posts_created
  from posts, t
  where created_at > t.start
  and created_at < t.end
  group by user_id
),
ttopics as (
 select user_id, posts_count
  from topics, t
  where created_at > t.start
  and created_at < t.end
),
tc as (
  select user_id, 
      count(1) as topics_created
  from ttopics
  group by user_id
),
twr as (
  select user_id, 
      count(1) as topics_with_replies
  from ttopics
  where posts_count > 1
  group by user_id
),
tv as (
 select user_id, 
        count(distinct(topic_id)) as topics_viewed
  from topic_views, t
  where viewed_at > t.start
  and viewed_at < t.end
  group by user_id
),
likes as (
  select 
      post_actions.user_id as given_by_user_id, 
      posts.user_id as received_by_user_id
  from t, post_actions
  left join posts
  on post_actions.post_id = posts.id
  where post_actions.created_at > t.start
  and post_actions.created_at < t.end
  and post_action_type_id = 2

),
lg as (
  select given_by_user_id as user_id, 
      count(1) as likes_given
  from likes
  group by user_id
),
lr as (
  select received_by_user_id as user_id, 
      count(1) as likes_received
  from likes
  group by user_id
),
e as (
  select email, user_id
  from user_emails u
  where u.primary = true
)
select pr.user_id,
       username,
       name,
       email,
       visits, 
       coalesce(topics_viewed,0) as topics_viewed,
       coalesce(posts_read,0) as posts_read, 
       coalesce(posts_created,0) as posts_created,
       coalesce(topics_created,0) as topics_created,
       coalesce(topics_with_replies,0) as topics_with_replies,
       coalesce(likes_given,0) as likes_given,
       coalesce(likes_received,0) as likes_received
from pr
left join tv using (user_id)
left join pc using (user_id)
left join tc using (user_id)
left join twr using (user_id)
left join lg using (user_id)
left join lr using (user_id)
left join e using (user_id)
left join users on pr.user_id = users.id
order by 
  visits desc, 
  posts_read desc, 
  posts_created desc

This query has pretty much everything in it. I’d like to see if time_read could be added as a column.


#122

Lets try this:

  1. Define params:
-- int :limit = 10
-- int :group_id = 3

I always use 10 (ten) as a limit, but you can change that.
And the “group_id” here is 3 (for staff group). Change as you need.

  1. Lets see the query:
-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 30
-- int :limit = 10
-- int :group_id = 3

with
t as (
  select 
    CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) as start,
    CURRENT_TIMESTAMP - (:from_days_ago * (INTERVAL '1 days')) as end
),
pr as (
    select user_id, 
        count(1) as visits,
        sum(posts_read) as posts_read
    from user_visits, t
    where posts_read > 0
    and visited_at > t.start
    and visited_at < t.end
    group by user_id
),
pc as (
  select user_id, 
      count(1) as posts_created
  from posts, t
  where created_at > t.start
  and created_at < t.end
  group by user_id
),
ttopics as (
 select user_id, posts_count
  from topics, t
  where created_at > t.start
  and created_at < t.end
),
tc as (
  select user_id, 
      count(1) as topics_created
  from ttopics
  group by user_id
),
twr as (
  select user_id, 
      count(1) as topics_with_replies
  from ttopics
  where posts_count > 1
  group by user_id
),
tv as (
 select user_id, 
        count(distinct(topic_id)) as topics_viewed
  from topic_views, t
  where viewed_at > t.start
  and viewed_at < t.end
  group by user_id
),
likes as (
  select 
      post_actions.user_id as given_by_user_id, 
      posts.user_id as received_by_user_id
  from t, post_actions
  left join posts
  on post_actions.post_id = posts.id
  where post_actions.created_at > t.start
  and post_actions.created_at < t.end
  and post_action_type_id = 2

),
lg as (
  select given_by_user_id as user_id, 
      count(1) as likes_given
  from likes
  group by user_id
),
lr as (
  select received_by_user_id as user_id, 
      count(1) as likes_received
  from likes
  group by user_id
),
e as (
  select email, user_id
  from user_emails u
  where u.primary = true
)
select pr.user_id,
       username,
       name,
       email,
       visits, 
       coalesce(topics_viewed,0) as topics_viewed,
       coalesce(posts_read,0) as posts_read, 
       coalesce(posts_created,0) as posts_created,
       coalesce(topics_created,0) as topics_created,
       coalesce(topics_with_replies,0) as topics_with_replies,
       coalesce(likes_given,0) as likes_given,
       coalesce(likes_received,0) as likes_received
from pr
left join tv using (user_id)
left join pc using (user_id)
left join tc using (user_id)
left join twr using (user_id)
left join lg using (user_id)
left join lr using (user_id)
left join e using (user_id)
left join users on pr.user_id = users.id
left join group_users on pr.user_id = group_users.user_id
WHERE group_users.group_id = :group_id
order by 
  visits desc, 
  posts_read desc, 
  posts_created desc
LIMIT :limit
  1. Please try it., and tell me your opinion :blush:

(Charles) #123

Thanks! This is getting a lot closer to what I was looking for. Is there a way to go by primary group? We have some users who are in multiple groups and I would want to narrow it down by their primary group. Also, where can I see group_Id? In /groups I can see the names of all of our groups, but not their number.


#124

I don’t know your forum URL, but you can check the group’s id here:
https://your-forum/groups.json

You will see a JSON file with all the groups, just search the group, and check the “id”. :+1:


(Charles) #125

That worked perfectly! What would it take to add the following values to that query?

  • post score
  • count of their posts that have been marked as solved by someone else
  • count of their topics or posts that have been bookmarked by someone else
  • count of flags given to others
  • count of flags received against them
  • total read time

I gave adding read time a shot myself…

-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 30
-- int :limit = 30
-- int :group_id = 18

with
t as (
  select 
    CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) as start,
    CURRENT_TIMESTAMP - (:from_days_ago * (INTERVAL '1 days')) as end
),
pr as (
    select user_id, 
        count(1) as visits,
        sum(posts_read) as posts_read
    from user_visits, t
    where posts_read > 0
    and visited_at > t.start
    and visited_at < t.end
    group by user_id
),
tr as (
    select user_id, 
        count(1) as time_read
    from user_visits, t
    where visited_at > t.start
    and visited_at < t.end
    group by user_id
),
pc as (
  select user_id, 
      count(1) as posts_created
  from posts, t
  where created_at > t.start
  and created_at < t.end
  group by user_id
),
ttopics as (
 select user_id, posts_count
  from topics, t
  where created_at > t.start
  and created_at < t.end
),
tc as (
  select user_id, 
      count(1) as topics_created
  from ttopics
  group by user_id
),
twr as (
  select user_id, 
      count(1) as topics_with_replies
  from ttopics
  where posts_count > 1
  group by user_id
),
tv as (
 select user_id, 
        count(distinct(topic_id)) as topics_viewed
  from topic_views, t
  where viewed_at > t.start
  and viewed_at < t.end
  group by user_id
),
likes as (
  select 
      post_actions.user_id as given_by_user_id, 
      posts.user_id as received_by_user_id
  from t, post_actions
  left join posts
  on post_actions.post_id = posts.id
  where post_actions.created_at > t.start
  and post_actions.created_at < t.end
  and post_action_type_id = 2

),
lg as (
  select given_by_user_id as user_id, 
      count(1) as likes_given
  from likes
  group by user_id
),
lr as (
  select received_by_user_id as user_id, 
      count(1) as likes_received
  from likes
  group by user_id
),
e as (
  select email, user_id
  from user_emails u
  where u.primary = true
)
select pr.user_id,
       visits, 
       coalesce(time_read,0) as time_read,
       coalesce(topics_viewed,0) as topics_viewed,
       coalesce(posts_read,0) as posts_read, 
       coalesce(posts_created,0) as posts_created,
       coalesce(topics_created,0) as topics_created,
       coalesce(topics_with_replies,0) as topics_with_replies,
       coalesce(likes_given,0) as likes_given,
       coalesce(likes_received,0) as likes_received
from pr
left join tv using (user_id)
left join tr using (user_id)
left join pc using (user_id)
left join tc using (user_id)
left join twr using (user_id)
left join lg using (user_id)
left join lr using (user_id)
left join e using (user_id)
left join users on pr.user_id = users.id
left join group_users on pr.user_id = group_users.user_id
WHERE group_users.group_id = :group_id
order by 
  visits desc, 
  posts_read desc, 
  posts_created desc
LIMIT :limit

The results for read time seem to be off, but I’m sure its because of:

tr as (
    select user_id, 
        count(1) as time_read
    from user_visits, t
    where visited_at > t.start
    and visited_at < t.end
    group by user_id
),

So I took out this and got something more realistic. I’m not sure if by removing this I am also not including read time based on the time frame.

    where visited_at > t.start
    and visited_at < t.end


(Simon Cossar) #131

2 posts were merged into an existing topic: Data Explorer queries for user voting statistics


(Jeremy M) #132

I’m trying to build out some queries based on the new dashboard metrics, like active users vs passive in a month but I’m struggling in how to get there exactly. For instance, I want to take the Daily Engaged Users report from the dashboard and mix it with the number of users who visit but don’t engage. Another one would be Net New users vs Returning visits each month.


#133

Hi all,

I’m rather new to this, but boy - what a cool tool this is!

Here’s a (very simple!) qeuery I created which allows you to easily identify lurkers who became member in the last so many months.

-- [params]
-- int :last_months = 2
-- int :posts_read = 1000
-- int :posts_written = 0

SELECT users.id , users.username, 
       users.created_at, users.last_seen_at,
       user_stats.posts_read_count, user_stats.post_count
    from users inner join user_stats
        on users.id = user_stats.user_id
    where users.created_at < now() -  interval ':last_months months' 
    and user_stats.posts_read_count > :posts_read
    and user_stats.post_count <= : posts_written
    ORDER BY user_stats.posts_read_count DESC

Three parameters (fairly self-explanatory):

  • last_months
    How many months do we want to go back for our new users to determine if indeed they are lurker (default: new accounts from the last 2 months)

  • posts_read
    How many posts must they have read before being considered a lurker (default: 1.000)

  • posts_written
    What is the maximum number of posts people can have written to be still considered a lurker (default: ))

I added the last parameter to be able to weed out people who only post ‘Hi! Nice here!’ or something along those lines, and then nothing else any more.

please note I’ve updated the script slightly to fix an issue that was spotted by @jerdog (see the post below) - thanks to J. for spotting this!


(Jeremy M) #134

This is great, though you have an invalid input syntax for type interval: ": last_months months" as there’s an extra space there on Line 17

PG::InvalidDatetimeFormat: ERROR:  invalid input syntax for type interval: ": last_months months"
LINE 17: where users.created_at < now() -  interval ': last_months mo...
                                                    ^

Edit: It would be interesting to have a variation of this that would identify how many moved from lurker since they joined X months ago to being a participant


#135

Ah, that’ll teach me to quickly translate some Dutch terms to English for the sake of raking in those likes here…

Thanks for spotting this! I’ll update my original post so that others won’t stumble into problems!


(Carson) #136

Would there be a good data explorer query for showing if more searches are taking place from week to week, month to month, or quarter to quarter? And secondarily, if more searches are ‘successful’ (click-through to a result)?


(AstonJ) #147

This is a quick variation of the one posted by Sarah.

List of users by who’s solved the most topics/threads

Change within_number_of_months to how many months you want to query for.
So 3 will show you results from the last three months, etc.

-- [params]
-- int :within_number_of_months = 1

WITH query_period AS (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL ':within_number_of_months months' as period_start,
date_trunc('month', CURRENT_DATE) as period_end
)

    
SELECT
ua.user_id,
count(1) AS solved_count
FROM user_actions ua
INNER JOIN query_period qp
ON ua.created_at >= qp.period_start
AND ua.created_at <= qp.period_end
INNER JOIN users u
ON u.id = ua.user_id
WHERE ua.action_type = 15
-- AND u.admin = 'f'
-- AND u.moderator = 'f'
GROUP BY ua.user_id
ORDER BY solved_count DESC