I am struggling with a pretty complex annotation of a QuerySet and I would really appreciate some help.

Here are my models:-

class Player(models.Model):
    group = models.ForeignKey(Group)

class Transaction(models.Model):
    created = models.DateTimeField()
    amount = models.DecimalField(decimal_places=2, max_digits=10)
    player = models.ForeignKey(Player)

Given a particular Group, I can get all the transactions for that Group using:-

Transaction.objects.filter(player__group=group)

But what I need is for each of those transactions to be annotated with the overall balance of the group at the time the transaction was created. So for each transaction, I need to sum all the transactions of the group whose created time was earlier than the transaction’s created date.

To do this, without ending up with tons of database calls, (I think) requires a complex queryset using things like Subquery and OuterRef but I can’t quite figure out the exact logic.

I tried something like this:-

balance = queryset.annotate(
            balance=Sum(
                Case(
                    When(
                        date__lte=F("date"),
                        then=F("amount"),
                    ),
                    default=0,
                    output_field=DecimalField(),
                )
            )
        ).filter(pk=OuterRef("pk"))

queryset.annotate(
    group_balance=Subquery(balance.values("balance"), output_field=DecimalField())
)

but I know that’s not quite right. I feel like I’m close but it’s driving me mad.