How to Rank Students Based on Total Points in Django QuerySet When Filtering Results

I'm working on a Django application where I need to rank students based on their total points and then filter the results. I want to rank all students based on their total points but only show students who are children of a particular user (supervisor).

class StudentQuerySet(SetFieldQuerySetMixin, models.QuerySet):
    """QuerySet for Student model"""

    def with_points(self) -> Self:
        """Adds a field indicating total points of a student."""
        return self.annotate(
            total_points=Coalesce(
                models.Sum("attendances__feedback__overall"), Value(0)
            )
        )

    def with_rank(self) -> Self:
        """Adds a field indicating rank of a student."""
        self = self.with_points()

        return self.annotate(
            rank=models.Window(
                expression=models.functions.Rank(),
                order_by=models.F("total_points").desc(),
            )
        )

class StudentLeaderboardMeListView(generics.ListAPIView):
    serializer_class = StudentLeaderboardSerializer
    permission_classes = [IsSupervisor]
    filter_backends = (DjangoFilterBackend,)
    filterset_class = StudentFilter

    def get_queryset(self):
        all_students = Student.objects.all().with_rank()
        top_3_ids = all_students.order_by("rank")[:3].values_list(
            "id", flat=True
        )
        queryset = all_students.exclude(id__in=top_3_ids).filter(
            parent=self.request.user
        )
        queryset = self.filter_queryset(queryset)
        return queryset

    def list(self, request, *args, **kwargs):
        queryset = self.get_queryset()

        page = self.paginate_queryset(queryset)
        if page is not None:
            serializer = self.get_serializer(page, many=True)
            return self.get_paginated_response(serializer.data)

        serializer = self.get_serializer(queryset, many=True)
        return Response(serializer.data)

The code correctly ranks all students based on their total points, but the ranking is affected by the subsequent filtering (exclude(id__in=top_3_ids).filter(parent=self.request.user)). I need to ensure that the ranking is computed based on all students before applying the filter. Essentially, the ranking should not change based on the filtered results.

How can I ensure that the ranking is computed over the entire dataset of students, regardless of the subsequent filtering? I need the rank to be calculated based on all students, but still be able to filter and display students who are children of the logged-in supervisor.

I found the sollution. Just need to use django-cte library.

from django_cte import CTEManager, CTEQuerySet

class StudentQuerySet(SetFieldQuerySetMixin, CTEQuerySet, models.QuerySet):
    ...

class StudentManager(CTEManager, models.Manager):
    ...

and also in view get_queryset() like this

def get_queryset(self):
    all_students = Student.objects.all().with_rank()
    student_ranks = With(all_students)
    result = (
    student_ranks.queryset()
        .with_cte(student_ranks)
        .filter(parent=self.request.user, rank__gt=3)
    )
    queryset = self.filter_queryset(result)
    return queryset