1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

/*
This will find questions where the question outscores the top-scored
(not accepted!) answer by a minimum threshold.
*/

select distinct
  Questions.Id as [Post Link],
  Questions.Score as "Question score",
  Answers.TopScore as "Top answer score",
  (Questions.Score - Answers.TopScore) as "Score diff",
  (Answers.TopScore / Cast(Questions.Score as numeric(5, 2))) * 100.0 as "Qual%",
  Questions.LastActivityDate as "Last active",
  Questions.CreationDate as "Creation date",
  Questions.Tags
from Posts as Questions
inner join (
  select -- the top scoring answer's score for each question
    Max(Score) as TopScore,
    ParentId
  from Posts
  where PostTypeId = '2' -- answer posts
  group by ParentId
  ) as Answers
on Questions.Id = Answers.ParentId
inner join
PostTags on PostTags.PostId = Questions.Id
inner join
Tags on PostTags.TagId = Tags.Id
where PostTypeId = '1' -- question posts
and Questions.AnswerCount > 0
and Questions.Score >= ##MinimumQuestionScore:int?10##
and Questions.ClosedDate is null -- exclude closed questions
and Questions.DeletionDate is null -- exclude deleted questions
and Questions.Score >= (Answers.TopScore + ##MinimumDifferenceInPoints:int?3##)
and Tags.TagName like '%' + ##Tag:string?%## + '%'
and Questions.Id not in ( -- exclude locked questions
  -- This is jonrsharpe's query for finding all locked questions:
  -- http://data.stackexchange.com/rpg/query/433957/locked-questions
  SELECT DISTINCT PostHistory.PostId AS [Post Link]
  FROM PostHistory
   LEFT JOIN (SELECT DISTINCT PostId, CreationDate 
               FROM PostHistory 
               WHERE PostHistoryTypeId = 15) AS Unlocked
   ON PostHistory.PostId = Unlocked.PostId 
     AND PostHistory.CreationDate < Unlocked.CreationDate
   WHERE PostHistory.PostHistoryTypeId = 14
     AND Unlocked.PostId IS NULL
)
order by
  "Qual%" Asc,
  "Score diff" Desc,
  Questions.Score Desc,
  Answers.TopScore Desc,
  Questions.LastActivityDate Desc

fork query

edited apr 4 16 doppelgreener

created mar 29 16 anonymous