User_stats with a proper time stamp

Community Building Data & reporting

AI-generated summary

User_stats with a proper time stamp

Gaurav_Sardana is seeking to find user statistics for a specific time period, rather than the overall statistics provided by the user_stats database.

david suggests generating the statistics using a custom database query, providing an example to get a post count for the last 30 days. He also shares a link to a topic with more example queries for reference.

Gaurav_Sardana asks how to modify the query to specify a custom date range instead of days, and notes a discrepancy between the database count and the UI statistics.

david explains that the UI count only includes public, undeleted posts, whereas the database count includes all posts, including deleted posts, PMs, and private categories.

Gaurav_Sardana asks how to obtain the UI statistics for a specific time period, and david provides a query that applies similar filters to get the desired statistics.

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

3

Thanks @david :slight_smile:

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

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