Number of posts on topics where poster is not the author?

Community Building Data & reporting

AI-generated summary

The discussion revolves around finding the number of posts a user has made on topics they did not author. SidV requested an SQL query to achieve this, referencing the Data Explorer. david provided the solution with a query that joins the posts and topics tables, filtering by user ID and topic authorship. The query is as follows:

-- [params]
-- string :user_id = 3
SELECT count(posts.id) FROM posts
JOIN topics ON topics.id = posts.topic_id
WHERE posts.user_id = :user_id 
AND NOT topics.user_id = :user_id
AND posts.deleted_at IS NULL
AND topics.deleted_at IS NULL
AND topics.archetype = 'regular'

SidV expressed gratitude and confirmed the issue is solved.

1

Let’s suppose the following:
A user creates a new discussion and responds 2 (two) times to his topic. At the same time, the same user in other topics that other people published, responds with five messages.

The user’s “posts” counter shows his messages (which would be two plus five).
How do I know that their messages are five with data explorer?
I mean I need an SQL query that returns the number of user messages on topics where he is not the author.

I was looking for the query, I checked the list of queries’s Data Explorer, and I also tried to create a new query to get the result, however I can not find how many posts a user has.

My head has burned, excuse me.

I hope someone can help me

2

The number of posts by a user on topics where the user is not the OP:

-- [params]
-- string :user_id = 3
SELECT count(posts.id) FROM posts
JOIN topics ON topics.id = posts.topic_id
WHERE posts.user_id = :user_id 
AND NOT topics.user_id = :user_id
AND posts.deleted_at IS NULL
AND topics.deleted_at IS NULL
AND topics.archetype = 'regular'
3

:clap: :clap: :clap: :clap:
Just GREAT !

Thanks @david !

Solved! :rocket: (list updated)