1

Please login or register to vote for this query.

(click on this box to dismiss)

Returns questions that are old (+6 months), have low views and score, have no upvoted or accept answer and where the user hasn't been active in the last 6 months. Modified from the query build out of this conversation http://chat.stackexchange.com/rooms/118/conversation/querying-for-abandoned-questions

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language

-- Potentially abandoned questions with inactive owners
-- Returns questions that are old (+6 months), have low views and score,
-- have no upvoted or accept answer and where the user hasn't been active
-- in the last 6 months.
-- 
-- Modified from the query build out of this conversation
-- http://chat.stackexchange.com/rooms/118/conversation/querying-for-abandoned-questions

SELECT TOP 1000
  p.Id AS [Post Link],
  p.Score,
  p.ViewCount AS Views,
  CAST( 1.0 * p.ViewCount / DateDiff(day,p.CreationDate,getdate()) AS DEC(5,3)) AS ViewsPerDay,
  p.AnswerCount AS Answers,
  CASE WHEN p.ClosedDate IS NULL THEN NULL ELSE 'Y' END AS Closed,
  DateDiff(month,p.LastActivityDate,getdate()) AS MnthsQstAct,
  DateDiff(month,u.LastAccessDate  ,getdate()) AS MnthsUsrAct,
  u.Id AS [User Link]

FROM Posts p
LEFT JOIN Users u on p.OwnerUserId = u.Id

WHERE p.PostTypeId = 1
  AND p.ViewCount < 0.5 *  DateDiff(day,p.CreationDate,getdate())
  AND p.Score < 4
  AND p.AcceptedAnswerId IS NULL
  AND NOT EXISTS (SELECT a.Id
                  FROM Posts a 
                  WHERE a.Score > 0 
                    AND a.ParentId = p.Id)
  AND p.LastActivityDate < dateadd(month,-6,getdate())
  AND u.LastAccessDate   < dateadd(month,-6,getdate())

ORDER BY
  p.AnswerCount,
  p.Score,
  ViewsPerDay

fork query

created jan 25 11 DMA57361