Hola,
Tengo una base de datos SQL de Discourse. Necesito encontrar estadísticas de usuarios para una marca de tiempo específica.
Por ejemplo, necesito saber cuántos publicaciones hizo un usuario.
Si extraigo esos datos de user_stats, obtendré un número desde que se unió al foro hasta ahora, pero necesito encontrarlo para un período de tiempo específico.
¿Alguna idea?
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
Thanks @david 
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 ?
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 ?
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.
But what if i want to get that UI stat with in a time period?
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