Inconsistent stats from the "User Participation Statistics" query

Community Building Data & reporting
7

I’ve been having a explore of it this morning to see if I can find out more. Mine times out on me when I try and go back 498 days (to my start date :slight_smile:), but I have managed to get one for a year so I can roughly compare it to the User Stats. :+1: (give or take a few hours worth of data)

image
image1378×235 26.5 KB

image
image1118×161 4.74 KB

There is a difference for posts_created, topics_created, likes_given and likes_received between the two - and taking a closer look at the SQL it seems the User Participation Statistics query doesn’t exclude deleted posts, whispers, or PMs, which may account for it.

The topics_viewed and posts_viewed seems pretty accurate for both though. :+1:

I’m not sure why some of your users aren’t showing up? The only exclusionary criteria I think I see is that they must have read more than 0 posts in the time window - which should include pretty much everybody, apart from staged users.


pr AS (
    SELECT user_id, COUNT(1) AS visits,
        SUM(posts_read) AS posts_read
    FROM user_visits, t
    WHERE posts_read > 0
        AND visited_at > t.START
        AND visited_at < t.END
    GROUP BY
        user_id

I’ll keep digging and see if I can come up with more. :slight_smile: :+1: