complex sub-queries in MySQL using FIFO Dynamic CTE

Question

I have 3 tables as:

First Table: (Purchase)

date (mm-dd)    quantity   p_id
    05-05          3         1
    05-06          2         1

Second Table: (Sales)

date (mm-dd)    quantity   p_id
    05-07          1         1

Third Table: (Expired)

date (mm-dd)    quantity   p_id
    05-08          4         1

Now what I want is get details of products that have expired as:

• When the product that has expired was purchased (FIFO)
• Product that was purchased first, will expire/sell first.

The Output shall be:

purchase_date      expired_date      quantity    p_id
  05-05               05-08             2         1
  05-06               05-08             2         1

Explanation,

• Store have total of 5 products by 05-06 with p_id: 1,
• then on 05-07 1 quantity was sold of p_id: 1 i.e. product that was received on 05-05 was sold first as per FIFO

so now product we have are: (only for visualization)

date (mm-dd)    quantity   p_id
    05-05          2         1   -its 1 quantity is sold
    05-06          2         1

Then Expiry is made on 05-08, expired products are: (by FIFO)

purchase_date      expired_date      quantity   p_id
  05-05               05-08             2         1
  05-06               05-08             2         1

i.e. 2 products of 05-05 have expired and 2 of 05-06

By now logic I am implementing is:

All Appended transactions:

date (mm-dd)    quantity   p_id    expired
    05-05          3         1      False
    05-06          2         1      False
    05-07         -1         1      False
    05-08         -4         1      True

Append all transactions as: (as shown above)
Set quantity of sell and expiry as negative, and an expired variable, True only if its expiry transaction

Algorithm: (using deque push pop concept, when expiry, log it)

1. for each group of p_id transactions:
2. initialize a deque (empty)
3. for each transaction (all appended transactions)
4. if quantity is positive, push in deque
5. else pop,
6. if no pop up skip (as sell can be more than purchase)
7. else if expired is false i.e. is a sell transactions,
8. pop until difference of sell and pop is greater than equals to zero
9. else, it is a wastage entry
10. pop until difference is greater than equal to zero, also log each pop up with difference

*I stop when difference is greater than or equals zero signifies more quantity was bought than sold, as sold quantity is negative, consider:

bought = 2
sold = -4
diff = 2-4 = -2
therefore we need to loop for next purchase until diff >= 0

Actually currently how I am doing all this is loading all this data from my RDBMS into pandas dataframe and then applying some stuff, obviously this isn't maintainable, I want to do it within database itself as database is optimized, I want to do a complex MySQL query that does some sub-queries to get me the desired result.

When sell is made, I have different stuff going on so I want minimum load that time.

Basically what happen is:

• A store buy product, purchase entry is made,
• A store sell product, sell entry is made,
• A store tells this product has expired, an expiry entry is made

*There is no relations in between them.

Also I am using Django Querysets, to get data so if I could use that, it would be great help!

Also I am open to make changes or use a totally different tool to achieve such.