# Inconsistent stats from the "User Participation Statistics" query

**URL:** <https://meta.discourse.org/t/inconsistent-stats-from-the-user-participation-statistics-query/245569>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [November 14, 2022, 9:01pm UTC](https://meta.discourse.org/t/inconsistent-stats-from-the-user-participation-statistics-query/245569 "2022-11-14T21:01:30Z")\
**Posts on this page:** 1\
**Showing post:** 7

<div class="post-metadata">

**Author:** ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\
**Post date:** [November 16, 2022, 12:11pm UTC](https://meta.discourse.org/t/inconsistent-stats-from-the-user-participation-statistics-query/245569/7 "2022-11-16T12:11:25Z")

</div>

> [@michellefs](#):
>
> One thing that would be helpful to clarify, is how each column is calculated. There are discrepancies between the CSV and what’s on a member’s Profile Summary page (note: we’re only comparing those two different sets of data where the query dates match the lifecycle of the member’s enrollment).

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 🙂), but I have managed to get one for a year so I can roughly compare it to the User Stats. 👍 (give or take a few hours worth of data)

 ![image](https://global.discourse-cdn.com/meta/original/4X/5/b/b/5bb6e96211240047ee3aefa263a722f5b529515f.png)

 ![image](https://global.discourse-cdn.com/meta/original/4X/d/2/d/d2d9b4d537952da152807079ee11111e05e20c91.png)

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. 👍

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.

```sql

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. 🙂 👍

---

_[View the full topic](https://meta.discourse.org/t/inconsistent-stats-from-the-user-participation-statistics-query/245569)._