I've these tables:

class ContestQuestions(models.Model):
    contest = models.ForeignKey(Contest, on_delete=models.CASCADE, 
                      related_name='contest_question_contest')
    quetions = models.ForeignKey(Question, on_delete=models.CASCADE, 
                      related_name='contest_question_questions')


class UserResponse(models.Model):
     user = models.ForeignKey(User, on_deleted=models.CASCADE, related_name='user_response')
     response = models.ForeignKey(Answer, on_delete=models.CASCADE, 
                    related_name='user_answer')

Other related tables I've:

class Contest(models.Model):
    name = charfield
    date = charfield
    is_active = bool

class Question(models.Model):
    title = charfield
    created_at = datetimefield

class Answer(models.Model):
     question = FK(Question)
     answer = charfield         #4 options of 1 question
     is_true = bool

I need to get some stat about every quiz. So from every quiz, I want to know the top 5 most correctky answered questions and most incorrectly answered questions and total number of people who attempted that question.

How can I write a query for it?

CodePudding user response：

I would add a method to your Contest model so that you can access this data like so:

class Contest(models.Model):
    ...
    def top_answered_questions(self, correct=True):
        return Questions.objects.filter(
            contest_question_questions__contest=self
        ).annotate(
            correct_answers=Count('answer', filter=Q(answer__is_true=correct))
        ).order_by(correct_answers)

You can then call contest.top_answered_questions()[:5] and change the correct parameter to get correctly and incorrectly answered questions.