7
\$\begingroup\$

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)

\$\endgroup\$
  • \$\begingroup\$ One danger is that the query might lead to misinterpretation of a statement such as "Thank heavens I didn't listen to your foolish advice!" as gratitude, but that will probably have to wait for rev 2. :) \$\endgroup\$ – Edward Jan 30 '15 at 19:10
  • \$\begingroup\$ Text matches are case-insensitive by default, so there's no need to pass everything through LOWER(...). \$\endgroup\$ – squeamish ossifrage Jan 30 '15 at 19:25
  • 1
    \$\begingroup\$ Similar query: Answers that wowed \$\endgroup\$ – 200_success Jan 30 '15 at 21:09
4
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Amazing answer, thanks a lot Rolfl! The reason for having the comment both as a link as a text is because the link is displayed too short, only the first couple of letters are visible. About the duplicate lines per answer (for each comment), I should have worded my question a bit differently, my intention was more about the comments than the answers: they are very much relevant to me, my ultimate goal was finding the most grateful enthusiastic comments. \$\endgroup\$ – Stop ongoing harm to Monica Jan 30 '15 at 21:37
  • \$\begingroup\$ You do know you can slide the column wider, right? \$\endgroup\$ – rolfl Jan 30 '15 at 21:38
  • 1
    \$\begingroup\$ I do, but I have to move the mouse for that, click and drag... Not so great. \$\endgroup\$ – Stop ongoing harm to Monica Jan 30 '15 at 21:44
  • 2
    \$\begingroup\$ An EXISTS subselect would be better than DISTINCT. \$\endgroup\$ – 200_success Jan 30 '15 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.