[Django]-Calculate the Average delivery time (days) Django ORM

by

2👍

✅

A basic solution is to annotate each Product with the minimum created time for related events that have the status "in-transit and select the maximum time for events with the delivered status then annotate the diff and aggregate the average of the diffs

from django.db.models import Min, Max, Q, F, Avg

Product.objects.annotate(
    start=Min('productevents__created', filter=Q(productevents__status=ProductEvents.Status.IN_TRANSIT)),
    end=Max('productevents__created', filter=Q(productevents__status=ProductEvents.Status.DELIVERED))
).annotate(
    diff=F('end') - F('start')
).aggregate(
    Avg('diff')
)

Returns a dictionary that should look like

{'diff__avg': datetime.timedelta(days=x, seconds=x)}
👤Iain Shelvington

Leave a comment