Data discrepancy in user post counts

Data & reporting
1

I’m trying to figure out a potential data discrepancy I’m seeing. I’ve been doing some queries with the Data Explorer plugin to get stats on our most-frequently posting users. One query I used was the following:

select u.username, us.post_count
from users u
join user_stats us
on u.id = us.user_id
order by us.post_count desc

This was returning, say, 100 posts for a particular user. Then I decided to redo the query a little differently:

select u.username, count(distinct p.id)
from users u
join posts p
on u.id = p.user_id
group by u.username
order by count(distinct p.id) desc

This query for that particular user returned 135 posts. And this seems to be the correct number, since it’s actually counting every single post from the posts table, as opposed to relying on the post_count field from user_stats.

So I’m trying to understand why there would be a discrepancy here. This is for a non-admin, non-mod user, so they don’t have a bunch of admin actions that are appearing as posts. Any ideas why this might be happening?

2

Hi Holly, the table posts will also include private messages and deleted posts. Maybe user_stats.post_count only includes undeleted posts, and not private messages and deleted posts? Someone with more knowledge can hopefully answer.

4 Likes
3

Yup, that’s exactly right @JusticeUK

2 Likes
4

Thanks for the info. However, this user has no deleted posts (I double checked their profile and added a p.deleted_at is null to my above query and am still seeing the same numbers. Our forum also has PM’s disabled, so I don’t think those would be messing with the numbers.

When looking at this user’s posts, they are all post_type = 1. I don’t know what exactly a post_type of 1 means yet, but all of his just seem to be normal posts.