Django query to fetch top performers for each month

Question

I need to fetch the top performer for each month, here is the below MySql query which gives me the correct output.

select id,Name,totalPoints, createdDateTime 
from userdetail
where app=4 and totalPoints in ( select 
max(totalPoints) 
FROM userdetail
where app=4 
group by  month(createdDateTime), year(createdDateTime))
order by totalPoints desc

I am new to Django ORM. I am not able to write an equivalent Django query which does the task. I have been struggling with this logic for 2 days. Any help would be highly appreciated.

Answer 1

While the GROUP BY clause in a subquery is slightly difficult to express with the ORM because aggregate() operations don't emit querysets, a similar effect can be achieved with a Window function:

UserDetail.objects.filter(total_points__in=UserDetail.objects.annotate(max_points=Window(
        expression=Max('total_points'),
        partition_by=[Trunc('created_datetime', 'month')]
    )).values('max_points')
)

In general, this sort of pattern is implemented with Subquery expressions. In this case, I've implicitly used a subquery by passing a queryset to an __in predicate.

The Django documentation's notes on using aggregates within subqueries is are also relevant to this sort of query, since you want to use the results of an aggregate in a subquery (which I've avoided by using a window function).

---

However, I believe your query may not correctly capture what you want to do: as written it could return rows for users who weren't the best in a given month but did have the same score as another user who was the best in any month.