Finding answers where happy customers commented "thanks"

To find answers where users expressed their gratitude (by mentioning "thank" in comments),
I put together this SEDE query (latest):

DECLARE @username AS NVARCHAR(60) = ##DisplayName:string? ##;
DECLARE @userId AS INT = ##UserId:int?-1##;

-- SpecialThanks: Special thanks only "Enter 1 for YES, 0 for NO"
DECLARE @SpecialThanks AS INT = ##SpecialThanks:int?0##;

WITH Answers AS (
  SELECT * FROM Posts WHERE PostTypeId = 2
)

SELECT TOP ##Limit:int?100##
  a.Id AS [Post Link],
  c.Id AS [Comment Link],
  c.Text,
  c.CreationDate
FROM Answers a
  INNER JOIN Users u ON a.OwnerUserId = u.Id
  INNER JOIN Comments c ON a.Id = c.PostId AND c.UserId != u.Id
WHERE
  (@username = '' OR u.DisplayName = @username)
  AND (@userId = -1 OR u.Id = @userId)
  AND LOWER(c.Text) LIKE '%thank%'
  AND (
    @SpecialThanks <= 0 OR (
      LOWER(c.Text) LIKE '%excellent%'
      OR LOWER(c.Text) LIKE '%awesome%'
      OR LOWER(c.Text) LIKE '%awesum%'
      OR LOWER(c.Text) LIKE '%awsum%'
      OR LOWER(c.Text) LIKE '%wonderful%'
      OR LOWER(c.Text) LIKE '%fantastic%'
      OR LOWER(c.Text) LIKE '%amazing%'
      OR LOWER(c.Text) LIKE '%great%'
      OR LOWER(c.Text) LIKE '%best%'
      OR LOWER(c.Text) LIKE '%impressive%'
      OR LOWER(c.Text) LIKE '%master%'
      OR LOWER(c.Text) LIKE '%guru%'
      OR LOWER(c.Text) LIKE '%wizard%'
      OR LOWER(c.Text) LIKE '%ninja%'
      OR LOWER(c.Text) LIKE '%grateful%'
      OR LOWER(c.Text) LIKE '%appreciat%'
      OR LOWER(c.Text) LIKE '%by far%'
      OR LOWER(c.Text) LIKE '%!!!%'
    )
  )
ORDER BY c.CreationDate DESC

What do you think? Any improvement ideas?

Mainly I'm wondering if there's a way to eliminate all those duplicated LOWER(c.Text)

Answers

Your Answers CTE is convenient, but it should be doing a lot more of the heavy lifting for you, and it is also over-generous with the selection.

Your intention is to report on answers for the specified user (UserID or Display Name). You should be filtering those answers out immediately. Also, the inner join to the Users table is just for the display name is a problem too. Also, there's a little trick with CommunityWiki or a couple of other posts types, where the DisplayName is actually populated on the Post table.

So, your Answers CTE is:

Answers AS (
  SELECT * FROM Posts WHERE PostTypeId = 2
)

I would make it do all the heavy lifting, as:

Answers AS (
  SELECT Id,
         OwnerUserId
  FROM Posts
  WHERE PostTypeId = 2
    AND (@userId < 0 OR OwnerUserId = @userId)
    AND (@username = '' OR OwnerUserId in (
        SELECT Id
        FROM Users
        WHERE DisplayName = @username))
)

With that CTE, the only answers returned are ones that are valid for the input conditions on who to report for.

Note that it only selects the two columns needed for the remainder of the query.

Selection

Your column selection returns the Comment text twice, once as a link, and again for the text. In some conditions this is useful (like getting the data as a CSV report) but I don't believe this is the intention here. Additionally, you do not report the actual User who created the answer either. I would find that useful.

SEDE allows you to return the Id for the user, but display the name (as a link). Your regular join to the Users table is not necessary for that.

So, with the previous Answers CTE, I would have the much simpler final selection as:

SELECT TOP ##Limit:int?100##
  a.Id AS [Post Link],
  c.Id AS [Comment Link],
  a.OwnerUserId as [User Link],
  c.CreationDate as [Comment Date]
FROM Answers a
  INNER JOIN Comments c ON a.Id = c.PostId AND c.UserId != a.OwnerUserId
WHERE LOWER(c.Text) LIKE '%thank%'
  AND (
    @SpecialThanks <= 0 OR (
      LOWER(c.Text) LIKE '%excellent%'
      OR LOWER(c.Text) LIKE '%awesome%'
      OR LOWER(c.Text) LIKE '%awesum%'
      OR LOWER(c.Text) LIKE '%awsum%'
      OR LOWER(c.Text) LIKE '%wonderful%'
      OR LOWER(c.Text) LIKE '%fantastic%'
      OR LOWER(c.Text) LIKE '%amazing%'
      OR LOWER(c.Text) LIKE '%great%'
      OR LOWER(c.Text) LIKE '%best%'
      OR LOWER(c.Text) LIKE '%impressive%'
      OR LOWER(c.Text) LIKE '%master%'
      OR LOWER(c.Text) LIKE '%guru%'
      OR LOWER(c.Text) LIKE '%wizard%'
      OR LOWER(c.Text) LIKE '%ninja%'
      OR LOWER(c.Text) LIKE '%grateful%'
      OR LOWER(c.Text) LIKE '%appreciat%'
      OR LOWER(c.Text) LIKE '%by far%'
      OR LOWER(c.Text) LIKE '%!!!%'
    )
  )
ORDER BY c.CreationDate DESC

Observations

Note that your query (and my suggestion above) will return the answer multiple times if there are multiple 'thank you' comments.

I would consider adding a join back to posts to only consider comments made by the owner of the question that was answered too. At the moment, anyone can add a 'thanks' comment to any post, and make that answer appear.

Joining back to the questions would also allow you to remove closed questions as well.

In general, the code style is consistent, and that's always good.

I considered recommending an in-memory table containing the values to search for in the comments, something like:

create table @words (
    word NVarchar(50)
)

insert into @words values
    ('awesome'),
    ('amazing'),
    .....

and then using that in a join like:

AND INDEXOF(LOWER(text), word) >= 0

but that will produce even more duplicates if the comment is something like:


Amazing answer, Thanks!!! That was awesome.

It could be cured with a 'distinct', but I am not sure it's worth it.

StackExchange Code Review Q#79120, answer score: 4