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

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',
  case -- Was it tweeted?
      when twit.CreationDate is null then ''
      else 'Tweeted'
      end as 'Tweeted?',
  tbl.AAS as 'Accepted score', 
  tbl.HS as 'Highest score',
  tbl.HSAAS as 'Average Higher Score',
  tbl.HSAC as 'Count of Higher Scoring Answers',
  ##ArrayPrependString:string? ## + tbl.HSAS + ##ArrayAppendString:string? ## as 'Higher Scoring Answers'' Scores',
  ##ArrayPrependString:string? ## + tbl.HSASD + ##ArrayAppendString:string? ## as 'Higher Scoring Answers'' Score Differences',
  tbl.SD as '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(minute, q.CreationDate, aa.CreationDate) as 'Minutes after question AA Created',
    DateDiff(hour, q.CreationDate, aa.CreationDate) as 'Hours after question AA Created',
    DateDiff(day, q.CreationDate, aa.CreationDate) as 'Days after question AA Created',
    tbl.HB as 'HSA Before',
    tbl.HA as 'HSA After',
    ##ArrayPrependString:string? ## + tbl.AAHSAMD + ##ArrayAppendString:string? ## as 'AA vs HSA Minute difference',
    ##ArrayPrependString:string? ## + tbl.AAHSAHD + ##ArrayAppendString:string? ## as 'AA vs HSA Hour difference',
    ##ArrayPrependString:string? ## + tbl.AAHSADD + ##ArrayAppendString:string? ## as 'AA vs HSA Day difference',
    ##ArrayPrependString:string? ## + tbl.HSAQMD + ##ArrayAppendString:string? ## as 'Q vs HSA Minute difference',
    ##ArrayPrependString:string? ## + tbl.HSAQHD + ##ArrayAppendString:string? ## as 'Q vs HSA Hour difference',
    ##ArrayPrependString:string? ## + tbl.HSAQDD + ##ArrayAppendString:string? ## as 'Q 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 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,
      -- Get the Higher Scoring Answers' Score Difference
      STUFF((
        SELECT ', ' + CAST(h.Score-a.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 HSASD,
      -- 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 minutes
      STUFF((
        SELECT ', ' + CAST(DateDiff(minute, h.CreationDate, a.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 AAHSAMD,
      -- Get the date difference between AA and HSAs in hours
      STUFF((
        SELECT ', ' + CAST(DateDiff(hour, h.CreationDate, a.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, h.CreationDate, a.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,
      -- Get the date difference between HSAs and Q in minutes
      STUFF((
        SELECT ', ' + CAST(DateDiff(minute, p.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 HSAQMD,
      -- Get the date difference between HSAs and Q in hours
      STUFF((
        SELECT ', ' + CAST(DateDiff(hour, p.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 HSAQHD,
      -- Get the date difference between HSAs and Q in days
      STUFF((
        SELECT ', ' + CAST(DateDiff(day, p.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 HSAQDD
      
    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
  -- History of post being tweeted
  left join PostHistory as twit on q.Id = twit.PostId and twit.PostHistoryTypeId=25
  -- 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

fork query

edited oct 5 21

created sep 13 21 willuwontu