This is a good example of a couple of Discourse data quirks that can help you get more accurate results when building your queries.
As most posts and topics are ‘soft-deleted’ when you delete them this means they will still appear in the database and can be included in custom report stats, so it can help to filter them out of any results you may want to pull out for user or category statistics, for instance. In a similar fashion, you may also want to exclude Personal Messages from your stats too, as well as the system users.
Here are a couple of tips on how to filter these out:
This SQL query is used to get the top 10 users (excluding system users) who have made the most public posts, excluding any posts, or posts from topics, that have been deleted.
-- Selecting user_id from the posts table and counting the number of posts each user has made SELECT p.user_id, COUNT(p.id) AS "Post Count" FROM posts p -- Left join with the topics table on topic_id LEFT JOIN topics t ON t.id = p.topic_id WHERE -- Exclude private message topics t.archetype <> 'private message' -- Exclude topics that have been deleted AND t.deleted_at ISNULL -- Exclude posts that have been deleted AND p.deleted_at ISNULL -- Exclude whispers, small action posts, and moderator posts AND p.post_type = 1 -- Exclude posts made by system user and discobot (user_id 0 and -1) AND p.user_id > 0 -- Group the results by user_id GROUP BY 1 -- Order the results by the count of posts in descending order ORDER BY 2 DESC -- Limit the results to the top 10 users with the most posts LIMIT 10
And here it is without the inline comments:
SELECT p.user_id, COUNT(p.id) AS "Post Count" FROM posts p LEFT JOIN topics t ON t.id = p.topic_id WHERE t.archetype <> 'private message' AND t.deleted_at ISNULL AND p.deleted_at ISNULL AND p.post_type = 1 AND p.user_id > 0 GROUP BY 1 ORDER BY 2 DESC LIMIT 10
This is just a simple query to demonstrate the examples, but hopefully this helps you get started with the data explorer. If you have any questions please ask them below.