Finding closed questions from new contributors that others believe may be now reopenable

Question

This query is fast, showing about 150 results right now:

WITH Closed_New_Contributors (PID, UID, Created)
AS
(
SELECT
  p.id AS PID,
  u.id AS UID,
  u.CreationDate as Created
  FROM Posts p
    INNER JOIN Users u ON p.OwnerUserId = u.Id
    WHERE p.ClosedDate IS NOT null
      AND PostTypeId = 1
      AND p.DeletionDate IS null
      AND CommunityOwnedDate IS null
      AND u.CreationDate > '2022-12-07'
      AND DATEDIFF(MINUTE,GETDATE(),p.CreationDate) > -10800
)
SELECT
  p.PID AS [Post Link],
  p.UID as [Uder Link],
  FROM Closed_New_Contributors p
    GROUP BY p.PID, p.UID, p.Created
    ORDER BY p.Created ASC;

When I try to also check Reopen count, the query times out. How can I also report Reopen counts?

(select 1 from Votes where VoteTypeId=7 and PostId=p.PID) as Reopens

Answer 1

Running a select statement in a projection is relatively expensive - it would be better if you can use joins. Applying that to your query I made these changes:

• added a LEFT OUTER JOIN on Votes to the CTE
• added a Group By on p.id, u.id, u.creationdate
• added a count on v.id. When v.id is null it won't get counted
• added a having condition to show you could select rows with a certain number of votes
• adapted the final select query to accommodate the new fields.

WITH Closed_New_Contributors (PID, UID, Created, Reopen)
AS
(
SELECT
  p.id AS PID,
  u.id AS UID,
  u.CreationDate as Created,
  count(v.id) AS [Reopen] 
  FROM Posts p
    INNER JOIN Users u ON p.OwnerUserId = u.Id
    LEFT OUTER JOIN Votes v ON v.PostId = p.Id AND v.VoteTypeId = 7
    WHERE p.ClosedDate IS NOT null
      AND PostTypeId = 1
      AND p.DeletionDate IS null
      AND CommunityOwnedDate IS null
      AND u.CreationDate > '2022-12-07'
      AND DATEDIFF(MINUTE,GETDATE(),p.CreationDate) > -10800
  GROUP BY p.ID, u.ID, u.CreationDate
  -- HAVING count(v.id) > 0 -- with at least one re-open vote
)
SELECT
  p.PID AS [Post Link],
  p.UID as [User Link],
  p.Created,
  p.Reopen
  FROM Closed_New_Contributors p
    ORDER BY p.Created ASC;

Now you'll notice that reopen is always 0. That is because after the 25th of June 2013 no more votes of votetypeid = 7 exist in the database. If I remove all date restrictions and bring in the having clause I do get results: https://data.stackexchange.com/stackoverflow/query/1689203

You might be interested in looking in the ReviewTasks table instead. The ReviewRaskTypeid with value 6 and a ReviewTaskStateId of 1. That should give you posts that sit in the reopen review queue. I leave it as an exercise for the reader to make the needed changes.

When you use the magic links, like [Post Link] and [User Link] you'll also have a higher risk of timing out the server. That is because on larger sets the fetching of data for the magic links is too much for SQL Server to handle.

Keep in mind SEDE is updated once a week on Sunday.
Use the fine SEDE Tutorial written by the unforgettable Monica Cellio.
Say "Hi" in SEDE chat.