Django: how to annotate average of average?

Question

I have a Puzzle model and a Session model

During a session, user will play puzzles and rate them. result is recorded through the following model:

class Play(models.Model):
    puzzle = models.ForeignKey(
            'puzzles.Puzzle', on_delete=models.CASCADE, related_name='plays')
    rating = models.IntegerField(default=-1)
    session = models.ForeignKey(Session, blank=True,
                                null=True, on_delete=models.SET_NULL, related_name='plays')

a same puzzle can be played more than once during the same session.

I'm trying to annotate the Session.objects with the rating_avg_from_session_plays which is averaging the rating_average_by_puzzle. in other terms:

1. for each puzzle played during the session, I want to obtain the average rating (avg_pzl_rating)
2. and I want afterwards, for each session, I want to average these avg_pzl_rating to obtain the average session rating (avg_session_pzl_rating)

Using Subquery and group_by syntaxes, I've not been able so far to achieve the expected result.

Here is the closest I've reached:

rating_average_by_puzzle_from_session_plays = Subquery(Play.objects.filter(session_id=OuterRef(
                'id')).values('puzzle_id').order_by('puzzle_id').annotate(
                puzzle_rating_average=Avg('rating')).values('puzzle_rating_average'))
    
Session.objects.annotate(rating_avg_from_session_plays=Avg(rating_average_by_puzzle_from_session_plays))

What Am I doing wrong ? from the result it appears like only the first rating is taken into account, while the following on a Session object is working very well:

session_object.plays.values('puzzle_id').order_by('puzzle_id').annotate(
           puzzle_rating_average=Avg('rating')).aggregate(
           rating_average=Avg('puzzle_rating_average'))['rating_average'] or 0

here is the corresponding sql request sent to the database when I'm trying to annotate the Session queryset:

    SELECT "games_session"."id", "games_session"."datetime", "games_session"."rating_average", 
    "games_session"."user_id", AVG((SELECT AVG(U0."rating") AS "puzzle_rating_average"             
    FROM "games_play" U0 WHERE U0."session_id" = "games_session"."id" 
    GROUP BY U0."puzzle_id")) AS "rating_avg_from_session_plays" FROM "games_session" 
    GROUP BY "games_session"."id", "games_session"."datetime", "games_session"."rating_average", 
    "games_session"."user_id"

As requested by willeM_ Van Onsem, here is a fixture (json) to illustrate an example of data to be averaged:

[
{
    "model": "games.play",
    "pk": 207,
    "fields": {
        "puzzle": 104,
        "rating": 1,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 208,
    "fields": {
        "puzzle": 104,
        "rating": 1,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 209,
    "fields": {
        "puzzle": 104,
        "rating": 1,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 210,
    "fields": {
        "puzzle": 104,
        "rating": 1,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 211,
    "fields": {
        "puzzle": 104,
        "rating": 1,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 212,
    "fields": {
        "puzzle": 104,
        "rating": 1,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 213,
    "fields": {
        "puzzle": 105,
        "rating": 1,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 214,
    "fields": {
        "puzzle": 105,
        "rating": 1,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 215,
    "fields": {
        "puzzle": 106,
        "rating": 2,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 218,
    "fields": {
        "puzzle": 107,
        "rating": 3,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 220,
    "fields": {
        "puzzle": 109,
        "rating": 3,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 224,
    "fields": {
        "puzzle": 113,
        "rating": 1,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 225,
    "fields": {
        "puzzle": 114,
        "rating": 1,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 232,
    "fields": {
        "puzzle": 108,
        "rating": 2,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 233,
    "fields": {
        "puzzle": 110,
        "rating": 3,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 246,
    "fields": {
        "puzzle": 111,
        "rating": 3,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 247,
    "fields": {
        "puzzle": 112,
        "rating": 2,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 248,
    "fields": {
        "puzzle": 115,
        "rating": 2,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 510,
    "fields": {
        "puzzle": 104,
        "rating": 1,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 511,
    "fields": {
        "puzzle": 105,
        "rating": 2,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 512,
    "fields": {
        "puzzle": 106,
        "rating": 2,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 513,
    "fields": {
        "puzzle": 107,
        "rating": 3,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 514,
    "fields": {
        "puzzle": 108,
        "rating": 3,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 515,
    "fields": {
        "puzzle": 109,
        "rating": 3,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 516,
    "fields": {
        "puzzle": 110,
        "rating": 3,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 517,
    "fields": {
        "puzzle": 111,
        "rating": 3,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 518,
    "fields": {
        "puzzle": 112,
        "rating": 3,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 519,
    "fields": {
        "puzzle": 113,
        "rating": 2,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 520,
    "fields": {
        "puzzle": 114,
        "rating": 1,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 521,
    "fields": {
        "puzzle": 115,
        "rating": 2,
        "session": 2
    }
},
{
    "model": "games.play",
    "pk": 522,
    "fields": {
        "puzzle": 115,
        "rating": 2,
        "session": 2
    }
},
{
    "model": "games.session",
    "pk": 2,
    "fields": {
        "datetime": "2020-10-28T20:26:52.023Z",
        "rating_average": "2.2"
    }
}
]

For this specific session (31 plays over 12 puzzles), running the queryset annotation gives an average of 1.0 (same result with limitating the avg_pzl_rating to be averaged with [:1], [:3], [:8]), while the direct aggregation on the idividual sessio object gives 2.1527777777777777