Questions with Accepted Answers not Highest Voted

1

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

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

Select Distinct
  q.Id as [Post Link],
  CONCAT('https://rpg.stackexchange.com/questions/', q.Id) as URL,
  q.Title as Title,
  q.CreationDate as Created,
  case 
    when q.ClosedDate is null then ''
    else (case when pl.LinkTypeId = 3 then 'dupe' else 'closed' end)
    end as 'Status',
  tbl.AAS as 'Accepted score', 
  tbl.HS as 'Highest score',
  tbl.HSAAS as 'Average Higher Score',
  tbl.HSAC as 'Count of Higher Scoring Answers',
  '{' + tbl.HSAS + '}' as 'Higher Scoring Answers'' Scores',
  tbl.SD as 'Score difference',
  tbl.HSAASD as 'Average Higher Scoring Answer Score Difference',
  case -- Was it a HNQ?
    when hnq.CreationDate is null then ''
    else (
      case -- If HNQ, when was the AA made?
        when (hnq.CreationDate > aa.CreationDate)
        then 'Created Before' 
        else (
          case -- If not removed manually, we assume 1 day instead of the maximum of 3 for it to leave.
            when ((lhnq.CreationDate < aa.CreationDate) or (DateAdd(day, 1, hnq.CreationDate) < aa.CreationDate))
            then 'Created After'
            else 'Created During'
            end)
        end)
    end as 'HNQ Effect',
    DateDiff(day, q.CreationDate, aa.CreationDate) as 'Days after question AA Created',
    '{' + tbl.AAHSAHD + '}' as 'AA vs HSA Hour difference',
    '{' + tbl.AAHSADD + '}' as 'AA vs HSA Day difference'
From
  Posts as q
  Join (
    Select 
      -- Our question ID is used to group, and therefore the same for the group
      avg(q.Id) as qId, 
      -- Count of Higher Scoring Answers
      count(hs.Id) as HSAC, 
      -- Accepted Answer Score, it's constant, and therefore the same for the group
      avg(aa.Score) as AAS, 
      -- Get the highest higher scoring answer's score
      max(hs.Score) as HS, 
      -- The score difference between accepted and highest scoring answers
      max(hs.Score) - avg(aa.Score) as SD,
      -- Get the average of the higher scoring answers' scores
      avg(cast(hs.Score as float)) as HSAAS,
      -- Get the average Score Difference with higher scoring answers' scores
      avg(cast(hs.Score-aa.Score as float)) as HSAASD,
      -- Get the Higher Scoring Answers' Scores
      STUFF((
        SELECT ', ' + CAST(h.Score AS VARCHAR(MAX)) 
        FROM 
          Posts p
          join Posts a on a.Id = p.AcceptedAnswerId
          left join Posts h on h.ParentId = p.Id and h.Score > a.Score
        WHERE
          (p.Id = q.Id) and
          (a.OwnerUserId != p.OwnerUserId or a.OwnerUserId is null)
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
      ,1,2,'') AS HSAS,
      -- If the AA's date is greater than the HSA's date then it was created after them, and vice versa.
      count(case when aa.CreationDate >= hs.CreationDate then 1 end) as HB,
      count(case when aa.CreationDate <= hs.CreationDate then 1 end) as HA,
      -- Get the date difference between AA and HSAs in hours
      STUFF((
        SELECT ', ' + CAST(DateDiff(hour, a.CreationDate, h.CreationDate) AS VARCHAR(MAX)) 
        FROM 
          Posts p
          join Posts a on a.Id = p.AcceptedAnswerId
          left join Posts h on h.ParentId = p.Id and h.Score > a.Score
        WHERE
          (p.Id = q.Id) and
          (a.OwnerUserId != p.OwnerUserId or a.OwnerUserId is null)
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
      ,1,2,'') AS AAHSAHD,
      -- Get the date difference between AA and HSAs in days
      STUFF((
        SELECT ', ' + CAST(DateDiff(day, a.CreationDate, h.CreationDate) AS VARCHAR(MAX)) 
        FROM 
          Posts p
          join Posts a on a.Id = p.AcceptedAnswerId
          left join Posts h on h.ParentId = p.Id and h.Score > a.Score
        WHERE
          (p.Id = q.Id) and
          (a.OwnerUserId != p.OwnerUserId or a.OwnerUserId is null)
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
      ,1,2,'') AS AAHSADD
      
    From 
      -- Get our default table of posts
      Posts as q
      -- Concentrate it to questions with accepted answers
      join Posts as aa on q.Id = aa.ParentId and aa.Id = q.AcceptedAnswerId
      -- We only want questions with other answers scoring higher than the accepted answer 
      join Posts as hs on q.Id = hs.ParentId and aa.Id != hs.Id and aa.Score < hs.Score
    Where
      q.AnswerCount > 1 and
      (aa.OwnerUserId != q.OwnerUserId or aa.OwnerUserId is null)
    Group by 
      q.Id
  ) as tbl on q.Id = tbl.qId
  -- History of when it joined the HNQ, if ever
  left join PostHistory as hnq on q.Id = hnq.PostId and hnq.PostHistoryTypeId=52
  -- History of it being manually removed from the HNQ, if ever
  left join PostHistory as lhnq on q.id = lhnq.PostId and lhnq.PostHistoryTypeId=53
  -- Grab our AA column for things
  left join Posts as aa on aa.Id = q.AcceptedAnswerId
  -- Get our PostLinks to see if it's a dupe or just closed
  left join PostLinks as pl on q.Id = pl.PostId
Where
  (aa.OwnerUserId != q.OwnerUserId or aa.OwnerUserId is null) and
  ((pl.LinkTypeId = (Select max(LinkTypeId) from PostLinks cpl where cpl.PostId = q.id))or pl.LinkTypeId is null)
Order by
  tbl.SD Desc

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results