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',
    tbl.HB as 'HSA Before',
    tbl.HA as 'HSA After',
    tbl.HBMin as 'Minute',
    tbl.HBHour as 'Hour',
    tbl.HBDay as 'Day',
    tbl.HB2Day as '2 Days',
    tbl.HB3Day as '3 Days',
    tbl.HBWeek as 'Week',
    tbl.HBMonth as 'Month',
    tbl.HBYear as 'Year',
    tbl.HBYearP as 'Year+'
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, 
      -- 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 answer scores
      avg(cast(hs.Score-aa.Score as float)) as HSAASD,
      -- Get the Median score of higher scoring answer
      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,
      -- The score difference between accepted and highest scoring answers
      max(hs.Score) - avg(aa.Score) as SD,
      -- 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,
      -- It was created within a minute of the HSA
      count(
        case 
          when 
            (
              (
                (aa.CreationDate < DATEADD(minute, 1, hs.CreationDate))
                AND 
                (aa.CreationDate >= hs.CreationDate)
              )
            ) 
          then 1 
          end) as HBMin,
      -- It was created within an hour of the HSA
      count(
        case 
          when 
            (
              (
                aa.CreationDate < DATEADD(hour, 1, hs.CreationDate)
                AND 
                aa.CreationDate >= DATEADD(minute, 1, hs.CreationDate)
              )
            ) 
          then 1 
          end) as HBHour,
      -- It was created within a day of the HSA
      count(
        case 
          when 
            (
              (
                aa.CreationDate < DATEADD(day, 1, hs.CreationDate)
                AND 
                aa.CreationDate >= DATEADD(hour, 1, hs.CreationDate)
              )
            ) 
          then 1 
          end) as HBDay,
      -- It was created within 2 days of the HSA
      count(
        case 
          when 
            (
              (
                aa.CreationDate < DATEADD(day, 2, hs.CreationDate)
                AND 
                aa.CreationDate >= DATEADD(day, 1, hs.CreationDate)
              )
            ) 
          then 1 
          end) as HB2Day,
      -- It was created within 3 days of the HSA
      count(
        case 
          when 
            (
              (
                aa.CreationDate < DATEADD(day, 3, hs.CreationDate)
                AND 
                aa.CreationDate >= DATEADD(day, 2, hs.CreationDate)
              )
            ) 
          then 1 
          end) as HB3Day,
      -- It was created within a week of the HSA
      count(
        case 
          when 
            (
              (
                aa.CreationDate < DATEADD(week, 1, hs.CreationDate)
                AND 
                aa.CreationDate >= DATEADD(day, 3, hs.CreationDate)
              )
            ) 
          then 1 
          end) as HBWeek,
      -- It was created within a month of the HSA
      count(
        case 
          when 
            (
              (
                aa.CreationDate < DATEADD(month, 1, hs.CreationDate)
                AND 
                aa.CreationDate >= DATEADD(week, 1, hs.CreationDate)
              )
            )  
          then 1 
          end) as HBMonth,
      -- It was created within a year of the HSA
      count(
        case 
          when 
            (
              (
                aa.CreationDate < DATEADD(year, 1, hs.CreationDate)
                AND 
                aa.CreationDate >= DATEADD(month, 1, hs.CreationDate)
              )
            )  
          then 1 
          end) as HBYear,
      -- It was created over a year after the HSA
      count(
        case 
          when 
            (
              (
                aa.CreationDate >= DATEADD(year, 1, hs.CreationDate)
              )
            )  
          then 1 
          end) as HBYearP
    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