Topics where user mentioned and hasn’t responded after being mentioned

Community Building Data & reporting
,
1

I don’t know even a bit of SQL… but would it be possible to have a query that shows me topics where I’ve been mentioned… but haven’t responded after being mentioned (I imagine this part is tricky, if it’s even possible)?

2

topics where user mentioned and hasn’t responded after being mentioned

-- [params]
-- user_id :user

WITH mentions AS (
    SELECT target_topic_id, target_post_id, created_at
    FROM user_actions
    WHERE action_type = 7 -- mentions
    AND user_id = :user
), replies AS (
    SELECT topic_id, MAX(created_at) created_at
    FROM posts
    WHERE user_id = :user
    AND deleted_at IS NULL
    AND post_type IN (1, 4) -- regular OR whisper
    GROUP BY topic_id
)
SELECT DATE(m.created_at) mentionned_at, target_post_id post_id
FROM mentions m
LEFT JOIN replies r ON r.topic_id = m.target_topic_id
JOIN topics t ON t.id = m.target_topic_id
WHERE m.created_at > COALESCE(r.created_at, '1900-01-01')
AND t.deleted_at IS NULL
AND NOT t.archived
AND NOT t.closed
ORDER BY m.created_at DESC