The discussion revolves around creating a Data Explorer query to understand unique individual participants in topics. Southpaw initially asked for help in building a query to determine the average number of unique individuals per topic and the top-ten topics based on unique participants.
david provided a query to solve the problem, utilizing the “participant_count” column in the database. The query can be used to find the average number of unique participants and top topics by number of unique participants over a specified interval.
However, Southpaw noticed discrepancies between the query results and the actual participant count in some topics. david suspected that the issue might be due to the inclusion of likes or other activities in the participant count.
vinothkannans later explained that the discrepancies are likely due to the ignoring of hidden post users when calculating the participant count. When staff members view a topic, they can see hidden posts, which are included in the participant count.
vinothkannans also pointed out that the “Frequent Posters” list only displays the top 24 users who frequently posted, which might cause discrepancies if a topic has more than 24 participants.
When I look at an individual topic, I can unfold a list of individuals who have posted on the topic, and the number of times they have done so:
Could someone please help me build a query in Data Explorer to determine:
Thanks in advance.
Southpaw
I thought this would be really complicated, but turns out there’s a handy “participant_count” column in the database 
-- [params]
-- string :interval = 1 year
SELECT id as topic_id, participant_count
FROM topics
WHERE bumped_at >= CURRENT_DATE - INTERVAL :interval
ORDER BY participant_count DESC
-- [params]
-- string :interval = 1 year
SELECT avg(participant_count)
FROM topics
WHERE bumped_at >= CURRENT_DATE - INTERVAL :interval
Is there documentation anywhere of what the different variables, like participant_count actually represent?
The top topics query is returning numbers that don’t match the “Frequent posters” image in the thread. I’m wondering if it includes likes or some other activity.
Unfortunately the only documentation is the code itself. I’m pretty sure participant_count should be the number of unique posters…
Do you have an example public topic where it doesn’t match up?
Thanks for following up. I want to explore it further, and will post some examples if I can’t sort things out on my own.
Hi @david,
Sorry for the very long delay. I do have some examples now. Sometimes the query is right, sometimes the count displayed in the thread is right. Sometimes they report the same number and are both right!
I noticed that when the counts differ, the threads have had posts split off to new threads.
Kudo & Win shows 17 participants in the query results, but when I unfold the info in the thread, I see 21 “users”. Actual count: 17.
No connection to cellular network… has 26 participants in the query results. The thread shows 24. Actual count: 26.
Moto X4 Coming Soon to Republic Wireless! returns 21 participants in the query results, thread indicates 22, actual count: 21.
Google Pixel2 and Pixel2 XL BYOP Support has 21 participants in the query results. The thread claims 19. Actual count: 19.
System Update for Moto X Pure (GSM Only) query results return 11 participants, thread count indicates 10. actual count: 10.
I believe this is mostly expected. Important to note.
While calculating participant_count we are ignoring hidden post users.
In above example (both images are from same topic) the first image includes hidden post users since logged in as admin. So it have 21 Frequent Posters. In second image while looking at the topic as anonymous user it correctly shows 17 Frequent users by excluding hidden posts.
For staffs we will render hidden (deleted) posts. That’s why it included them too in “Frequent Posters” list. Should we hide them?
I tested this functionality. I am unable to reproduce it. As I described in previous post the counts are differ (for staffs) only if the topic had deleted posts.
Like it named as “Frequent Posters” currently we only displaying top 24 users who frequently posted. So if a topic had more than 24 users it will filter and display only top 24 participants.
