What cool data explorer queries have you come up with?

data-explorer

(AstonJ) #148

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…


(AstonJ) #149

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

#150

Hi,

I’m looking to query some data just for 1 particular date.
I want to specify date in format: dd-mm-yyyy

Here are the fields I want:

username, likes-received, post-count (excluding PM posts), solution_count, date

Using this data, I’ll be able to find out likes-received, post-count, solution_count by users for a particular date.

Please help me.

Thanks in advance.


(Chris Beach) #151

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

(Southpaw) #152

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?


(Vinoth Kannan) #153

@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

(Southpaw) #154

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:


#155

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:


(DiscourseMetrics.com) #156

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?


#157

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

(Richie Rich) #158

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?


#159

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

(Richie Rich) #160

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:


#161

Yes. A lot of badges have the option to win more that one time. :thinking:

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

(Richie Rich) #162

D’oh! :man_facepalming:t2:

Yes, of course :blush:

However, your updated reply is actually exactly what I need :clap:t2:

Thanks ever so much @SidV :bowing_man:


#163

I’m glad to help !

Query’s list updated :rocket:

Have a great xmas! :christmas_tree:


(Sam Saffron) #164

:blush:

Can you pick the 5 most popular queries from your carefully curated list that we are not shipping out-of-the-box yet and do a PR to add them?

Adding queries now is super easy thanks to @rishabhn


#165

@SidV you’re a real lad, thanks for the work you put in.


(Carson) #166

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.


(Chris Beach) #167

Try removing the “DESC” from this line:

ORDER BY sum(p.score) / count(p) DESC