I have two tables :

1. QuizInfo:

• QuestionID
• FirstAnswerID
• SecondAnswerID
• CorrectAnswerID

2. PlayerAnswers:

• QuestionID
• PlayerID
• PlayerAnswerID

PlayerAnswers records answer players have given. Player can opt not to answer any given question. I want to give all the players a rank i.e. 1, 2, 3, based on number of correct answers. Is that possible with these two tables only? If so, how?

CodePudding user response：

Count the correct answers by id, then use the rank function

with t as (
    select b.playerid, count('dracula') cnt  
    from QuizInfo a
    inner join PlayerAnswers b
        on a.QuestionID = b.QuestionId
        and a.CorrectAnswerID = b.PlayerAnswerID
)    
select playerid
    , rank() over (order by cnt desc) as rnk
from t;