What cool data explorer queries have you come up with?

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.

2 Likes

Users who don’t have a particular badge

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

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

Members of group who do not have a particular badge

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

Final version:

4 Likes

Stats for group members

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

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

Lurkers who become members

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!

11 Likes

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

Latest uploads

SELECT 
    id, 
    user_id, 
    original_filename,
    created_at,
    url,
    extension
FROM uploads
order by created_at desc
LIMIT 50

Though it would be nice if they show in the results rather than just the urls…

3 Likes

Another one I can’t take any credit for - ripped directly out of DC’s dashboard :smiley:

Top 10 referrers over the last month

SELECT  COUNT(*) AS count_all, incoming_domains.name 
AS incoming_domains_name 
FROM "incoming_links" 
INNER JOIN "posts" ON "posts"."id" = "incoming_links"."post_id" 
AND ("posts"."deleted_at" IS NULL) 
INNER JOIN "topics" ON "topics"."id" = "posts"."topic_id" 
AND ("topics"."deleted_at" IS NULL) 
INNER JOIN "incoming_referers" ON "incoming_referers"."id" = "incoming_links"."incoming_referer_id" 
INNER JOIN "incoming_domains" ON "incoming_domains"."id" = "incoming_referers"."incoming_domain_id" 
WHERE (topics.archetype = 'regular') 
AND ("topics"."deleted_at" IS NULL) 
AND (incoming_links.created_at > date_trunc('month', CURRENT_DATE) - INTERVAL '30 days' 
AND incoming_links.created_at < date_trunc('month', CURRENT_DATE)) 
GROUP BY incoming_domains.name 
ORDER BY count_all DESC LIMIT 10
5 Likes

Established users with trust-level locked to 0 or 1:

-- [params]
-- int :min_visited_days = 30
-- int :min_posts = 1

SELECT 
    id AS user_id,
    manual_locked_trust_level,
    days_visited,
    post_count
FROM 
    users u JOIN
        user_stats us ON u.id = us.user_id
WHERE 
    manual_locked_trust_level IN (0,1) AND
    days_visited >= :min_visited_days AND
    post_count >= :min_posts AND
    (silenced_till IS NULL OR silenced_till < NOW()) AND
    (suspended_till IS NULL OR suspended_till < NOW())
ORDER BY 
    days_visited DESC
5 Likes

Could anyone help me get started toward writing a query that would return the users who have added a specific tag to topics, and number of times they’ve done so during a given date range?

@nixie were you able to write that query you described?

Users who have used a specific tag

@Southpaw it should return the correct result

-- [params]
-- text :tag_name

SELECT tp.user_id, COUNT(tt.tag_id)
FROM topic_tags tt
INNER JOIN tags t ON t.id = tt.tag_id
INNER JOIN topics tp ON tp.id = tt.topic_id
WHERE t.name = :tag_name
GROUP BY tp.user_id, tt.tag_id
4 Likes

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:

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