8

Please login or register to vote for this query.

(click on this box to dismiss)

Questions where your answer has more than twice the score of the accepted answer which in turn must have a score of 11 or higher

Q&A about the site for music historians, critics, and fans

select
    Question.Id as [Post Link],
    AcceptedAnswer.Score as [Accepted Score],
    YourAnswer.Score as [Your Score],
    CONVERT(DECIMAL(4,2), CAST(YourAnswer.Score as float) / AcceptedAnswer.score) as ScoreRatio,
    CASE WHEN AcceptedAnswer.Score > 10 THEN 0 ELSE 11 - AcceptedAnswer.Score END as [Accepted votes needed],
    CASE
        WHEN YourAnswer.Score > AcceptedAnswer.Score * 2 AND YourAnswer.Score > 22
        THEN 0
        ELSE ((CASE WHEN AcceptedAnswer.Score > 10 THEN AcceptedAnswer.Score ELSE 11 END) * 2 - YourAnswer.Score + 1)
    END as [Your votes needed],
    CASE WHEN AcceptedAnswer.Score > 10 THEN 0 ELSE 11 - AcceptedAnswer.Score END
    + CASE
        WHEN YourAnswer.Score > AcceptedAnswer.Score * 2 AND YourAnswer.Score > 22
        THEN 0
        ELSE ((CASE WHEN AcceptedAnswer.Score > 10 THEN AcceptedAnswer.Score ELSE 11 END) * 2 - YourAnswer.Score + 1)
      END as [Total votes needed]
from
    Posts Question,
    Posts AcceptedAnswer,
    Posts YourAnswer,
    Users AcceptedUser
where
    YourAnswer.OwnerUserId = ##UserId##
    and YourAnswer.ParentId = Question.Id
    and Question.AcceptedAnswerId = AcceptedAnswer.Id
    and AcceptedAnswer.OwnerUserId = AcceptedUser.Id
    -- your answer scores higher than the accepted answer
    and YourAnswer.Score > AcceptedAnswer.Score 
    -- and the accepted answer is *approaching* the minimum requirement of
    -- 11 or higher
    and AcceptedAnswer.Score >= ##MinAcceptedScore:int?9##
    and not (
        -- has not already been awarded a Populist badge
        AcceptedAnswer.Score > 10 and
        YourAnswer.Score > (AcceptedAnswer.Score * 2)
    )
order by CAST(YourAnswer.Score as float) / AcceptedAnswer.score DESC

fork query

edited dec 16 16 Martijn Pieters

created apr 19 11 oid