Interview Questions/SQL/FIFO Stock Cost Basis for Sales

FIFO Stock Cost Basis for Sales

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

Medium

FIFO Stock Cost Basis for Sales

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

Examples

Example 1

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.

query.sql