FIFO Stock Cost Basis for Sales

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given Purchases and Sales tables for a single stock. Purchases arrive in date order; sales must consume inventory using FIFO (oldest lots first). For each sale, compute: total quantity sold, total cost basis (sum of units × their purchase price), and average cost per unit. Return (sale_id, sale_date, total_qty_sold, total_cost_basis, avg_cost_per_unit) ordered by sale_id. Table: Purchases

Column Name	Type	Description
purchase_id	INT	Primary key
purchase_date	DATE	Date of purchase
quantity	INT	Units purchased
unit_cost	REAL	Cost per unit

Table: Sales

Column Name	Type	Description
sale_id	INT	Primary key
sale_date	DATE	Date of sale
quantity	INT	Units sold

Database Schema (Inferred)

Purchases

Column Name	Example Value
purchase_id	1
purchase_date	2024-01-05
quantity	100
unit_cost	10

Sales

Column Name	Example Value
sale_id	1
sale_date	2024-02-20
quantity	120

Example

Purchases

purchase_id	purchase_date	quantity	unit_cost
1	2024-01-05	100	10
2	2024-02-10	150	12
3	2024-03-15	200	15

Sales

sale_id	sale_date	quantity
1	2024-02-20	120
2	2024-04-01	180

Output

sale_id	sale_date	total_qty_sold	total_cost_basis	avg_cost_per_unit
1	2024-02-20	120	1240	10.3333
2	2024-04-01	180	2310	12.8333

Explanation:

Sale 1 (qty=120): exhausts lot 1 (100 × $10 = $1000) then takes 20 from lot 2 (20 × $12 = $240). Total = $1240. Sale 2 (qty=180): takes remaining 130 from lot 2 (130 × $12 = $1560) then 50 from lot 3 (50 × $15 = $750). Total = $2310. Use cumulative sums to define lot ranges and overlap-match them to sale ranges.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.