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
Thank you so much @david! That’s a tremendous help!
(And thank you @zogstrip for moving the thread to the right category. I’ll get my bearings eventually.)
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.
A post was split to a new topic: 24 user max in topic statistics
