Usernames used in possible comment replies to comments by a given user

0

Please login or register to vote for this query.

(click on this box to dismiss)

MathOverflow

Q&A for professional mathematicians

; with texts_cte as
(
SELECT DISTINCT c2.Text AS Text
FROM Comments c1 JOIN Comments c2 ON (c1.PostId=c2.PostId)
  AND (c1.UserId=##UserId##) AND (c1.CreationDate<c2.CreationDate)
WHERE (c2.Text LIKE '%@%')
)
, substrings_cte AS
(
SELECT substring(Text,charindex('@',Text), len(Text)) AS TextFrom
FROM texts_cte
),
names AS
(
SELECT substring(TextFrom,1,patindex('%[ :,.]%',TextFrom+' ')-1) AS Reply
FROM substrings_cte
)

SELECT Reply, Count(*) AS Count
FROM Names
GROUP BY Reply
ORDER BY Count(*) DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results