FIFO/LIFO Matching/Joining
alex@theparticle.com

Occasionally we need to join events based on the order they occur, such as 
when managing an inventory:

  suppose we buy a widget for $95,
  and then another widget for $105,
  and then another widget for $100.

Our inventory (cost=300) now has three seemingly identical widgets, with 
average cost of $100 (300/3).

  [ {id=1, tim=1, cost=95}, {id=2, tim=2, cost=105}, {id=3, tim=3, cost=100} ]

Then we sell 1 widget for $120.

How would that be recorded? Or more specifically, which widget did we sell?

Option1: FIFO, First-in-First-out, we sell {id=1, tim=1, cost=95}. 
    We record a $25 profit (pay tax on that), and inventory value is now 205.

Option2: LIFO, Last-in-First-out, we sell {id=3, tim=3, cost=100}.
    We record a $20 profit (pay tax on that), and inventory value is now 200.

Option3: Yes, there is option 3. We pick a specific widget to sell, and then
    explain to the IRS why we did it that way. 

Option4: We treat our inventory, 3 items at avg cost 100 (300/3).
    Selling 1 just changes that average to 2 items at avg price of 100, (200/3).
    Profit is $20 (avg priced item sold). 

IRS and many businesses often prefer/use FIFO, unless there's historic or business
reason to use something else.

Lets do a more concete example: 

-- these add to our inventory.
widget_purchases(purchase_id,purchase_date,quantity,cost_per_unit)

e.g.:
purchase_id  purchase_date  quantity  cost_per_unit
-----------  -------------  --------  -------------
1            2026-01-02     1000      $3.00
2            2026-01-10      500       $3.20
3            2026-01-20      800       $3.50


-- these remove from our inventory.
widget_sales(sale_id,sale_date,quantity,sale_price_per_unit)

e.g.:
sale_id  sale_date   quantity  sale_price_per_unit
-------  ----------  --------  --------------------
1        2026-01-15  700       $4.00
2        2026-01-25  600       $4.20

Under FIFO: 

Sale 1: 700 units
    700 from purchase 1 @ $3.00

Sale 2: 600 units
    300 from purchase 1 @ $3.00
    300 from purchase 2 @ $3.20

revenue=700(4.00)+600(4.20)=$5,320
FIFO cost=700(3.00)+300(3.00)+300(3.20)=$3,960

Profit: $1,360

One way to approach this problem is to imagine inventory as slabs:

purchases: [       1000   ][  500     ][  800     ]
sales:     [   700    ][    600     ]

Each of these slabs has a start qty and an end qty, (start)-qty-(endqty)

purchases: (0)-1000-(1000), (1000)-500-(1500), (1500)-800-(2300)
sales: (0)-700-(700), (700)-600-(1300)

The ONLY purchases/sales that overlap, is when:
  start of purchases is less than end of sales AND
  start of slaes is less than end of purchases.

That's it. 

In SQL, we can calculate start/end by doing a cumulative sum, and then
we just do a join looking for overlaps:

with purchases as (
    select a.*,
        sum(quantity) over (
            order by purchase_date, purchase_id
            rows unbounded preceding and current row
        ) as end_qty    --this is the cumulative sum.
        -- start qty is just end_qty - quantity.
    from widget_purchases a
),
sales as (
    -- we do the same cumulative sum for sales too.
    select a.*,
        sum(quantity) over (
            order by sale_date, sale_id
            rows unbounded preceding
        ) as end_qty
    from widget_sales a
)
select
    s.sale_id,
    p.purchase_id,
    least(s.end_qty,p.end_qty) - greatest(s.end_qty-s.quantity, p.end_qty-p.quantity) as mtch_qty,
    s.sale_price_per_unit,
    p.cost_per_unit
from sales s
  inner join purchases p
    on p.end_qty - p.quantity < s.end_qty   -- start of purchase < end of sales.
   and p.end_qty > s.end_qty - s.quantity   -- satart of sales < end of purchases

This query will give us a record for every match, along with match quantity.

FIFO cost is: mtch_qty * cost_per_unit
revenue is: mtch_qty * sale_price_per_unit

Profit (or loss) is: mtch_qty * (sale_price_per_unit - cost_per_unit) 


Obviously if we have multiple products (e.g. thousands of widgets, like in a big business),
then we'd want to add a product_id in there somewhere, and join on that too.

Occasionally we need to track profit/loss by account, so we could use that too.

If there are (hundreds of) thousands of sales and purchases (such as a busy 
brokerage account, for example), then this join is O(N^2), which does not
scale well. e.g. 100k buys and 100k sells in a broker account, and this join 
will iterate through all 10000000000 potential matches. Refer to bucket-join 
class notes to make this join super fast even when we have millions of 
purchases and sales.