User_stats with a proper time stamp

Data & reporting
1

Hey,

I have a discourse sql database. I need to find user stats for a particular time stamp.
For example - I need to find how many post did user made ?
If i extract that data from user_stats. It will give me a number from he joined forum till now but i need to find out for a particular time period.
Any idea?

2

You will need to generate the statistics yourself using a new database query. For example, to get a post count for the last 30 days

SELECT count(*) from posts 
WHERE user_id=1
AND created_at > CURRENT_TIMESTAMP - INTERVAL '30 days'

This topic has a lot of example queries you can use as a starting point

2 Likes
3

Thanks @david :slight_smile:

1 Like
4

But what if i want to give a proper time instead of days like if i want to find between 2020-2-2 00:00:00 and 2020-3-1 00:00:00 ?

5

If i do SELECT count(*) from posts WHERE user_id=16 AND created_at > CURRENT_TIMESTAMP - INTERVAL '30 days'

I got a number but when i look into my stats its less than that number.
If you see this. I got 430 from the database but its actually 308 ?

OnPaste.20200302-164643
OnPaste.20200302-1646431134×88 9.36 KB

6

The count shown in the UI is only for public, undeleted, posts. The database count is every post, including deleted posts, PMs, and private categories.

7

But what if i want to get that UI stat with in a time period?

8

You will need to apply similar filters. Something like this:

SELECT count(*) FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE p.user_id=23968
AND p.deleted_at IS NULL
AND NOT COALESCE(p.hidden, 't')
AND p.post_type = 1
AND t.deleted_at IS NULL
AND COALESCE(t.visible, 't')
AND t.archetype <> 'private_message'
AND p.user_id > 0
AND p.post_number > 1
3 Likes