What are the top voted comments on closed questions, and who made the comments?

Following this linked question and the feedback it generated, I put together a new query which utilizes a JOIN.

WITH UpvotedComments AS (
  SELECT
    PostId,
    Score,
    Text,
    --Comments.UserDisplayName doesn't return users' current display names
    Commenter = (
      SELECT DisplayName
      FROM Users
      WHERE Id = UserId
    )
  FROM Comments
  WHERE Score > 0
), ClosedPosts AS (
  SELECT Id, ClosedDate
  FROM Posts
  WHERE ClosedDate IS NOT NULL
)

SELECT Score, Text, Commenter

FROM UpvotedComments

INNER JOIN ClosedPosts ON UpvotedComments.PostId = ClosedPosts.Id

ORDER BY Score DESC, ClosedDate DESC

It can get a bit slow on sites like Stack Overflow, where the returns are greater than 50 000.

SEDE has two great features that you should incorporate into your code:

• Input parameters

• Magic markup columns

Input parameters

SEDE lets you declare fields to be input towards the query, like so:

##Parameter:type?defaultvalue##

And so, I suggest you use three of them for the following things:

• Threshold (The threshold of comment scores to select)

• Amount per page (The amount per page of comments)

• Page number (To go with the above one)

By using pages, you can simplify your returning dataset and help to reduce the massive call times of the query.

Magic markup columns

SEDE has a great feature where certain columns can be transformed into special markup. The specific columns are:

• Post Link (generated from Post Id)

• User Link (generated from User Id)

• Comment Link (generated from Comment Id)

• Suggested Edit Link (generated from Suggested Edit Id)

• Tags

As it stands...

your code is good, but you are doing a few things you can improve on:

• Your generation of Commenter can be easily replaced with UserId as [User Link] to save extraneous calling of another table.

• SELECT Id, ClosedDate your SELECTion is redundant. To use the field, you don't actually have to select it, simply reference it. Seeing as the resulting table is only used for the Id anyway, it's pointless to have it.

StackExchange Code Review Q#118343, answer score: 2