How to use group by, max of a column and also getting other columns of the model from a Django queryset?

I have a model that looks like this

class Documents(models.Model):
    id = models.AutoField(primary_key=True, editable=False)
    uid = models.CharField(max_length=64)
    version = models.IntegerField()
    reviewed_dtm = models.DateTimeField(null=True)
    timestamp = models.DateTimeField(auto_add_now=True)
    document = models.FileField()

I want the average time difference between the timestamps for the maximum version and the minimum number for every uid. I basically want to know the average time it takes for a document to be reviewed by a user since its creation. Being reviewed is optional, if a user finds the document to be good then marks it as reviewed, or else sends it for the new version. Then another record is made for the uid with an updated version.

it looks like you are looking for Window function If you are not familiar with it, first of all, read how to use it in pure sql to understand this concept

I wrote the ORM query for it. Here it is:

Documents.objects.values('uid').annotate(difference = Max('reviewed_dtm')-Min('timestamp')).aggregate(Avg('difference'))

Back to Top