query to django model to compare daily sale over previous day (compare two row of database model)

Question

I have a django model that is "DailyReport" of the companies sale I want to find out company sale change over the previous day. the model that i define is like that:

class DailyReport(models.Model):
    company = models.CharField(max_length=50)
    sale = models.IntegerField()
    date = models.DateField()

How can i figure out this issue to add new column for every report that represent change rate over the previous day

Answer 1

Use the Lag window function to annotate each row with the previous sale amount for that company.

Then use another annotation to calculate the difference between the current and previous sale

from django.db.models import Window, F
from django.db.models.functions import Lag

DailyReport.objects.annotate(
    prev_val=Window(
        expression=Lag('sale', default=0),
        partition_by=['company'],
        order_by=F('date').asc(),
    )
).annotate(
    diff=F('sale') - F('prev_val')
)