Kata

You work for a company that manages a large inventory of products, each identified by a unique SKU (Stock Keeping Unit). The company meticulously tracks all purchases and sales of these products over time, with detailed records of dates, prices, and quantities. Accurate financial reporting is crucial for the business, especially when it comes to understanding profitability. One of the key metrics used in this process is the FIFO Gross Margin.

(the easiest explanation that I have found on youtube is here)

The FIFO (First-In, First-Out) inventory method is used for inventory valuation, ensuring that the oldest inventory items are recorded as sold first. This method is vital for accurately reflecting the cost of goods sold (COGS) and calculating the gross margin. The company needs a SQL query to calculate the FIFO Gross Margin for each SKU, which matches sales against purchases in the order they were made. This ensures that the financial analysis is precise and that decisions are based on reliable data.

purchase:

• po_id (Purchase Order ID, int): Unique identifier for each purchase.
• sku (Stock Keeping Unit, varchar): Unique identifier for the product.
• purchase_date (timestamp): Date and time when the purchase was made.
• price (float): Price per unit for the purchase.
• qty (int): Quantity purchased.

sales:

• sale_id (Sales Order ID, int): Unique identifier for each sale.
• sku (Stock Keeping Unit, int): Unique identifier for the product.
• sale_date (timestamp): Date and time when the sale was made.
• price (float): Price per unit for the sale.
• qty (integer): Quantity sold.

Calculate the sales margin for each SKU by matching sales against purchases in the order they were made. The margin for each SKU is calculated as:

Margin = (Total Sales Revenue) − (Total Purchase Cost)

We need to return the following columns:

1. sku: The unique identifier for the product (Stock Keeping Unit) for which the margin is being calculated.
2. margin: The calculated FIFO Gross Margin for the SKU, representing the difference between the total sales revenue and the total purchase cost for the matched quantities.

The result must be ordered by SKU in ascending order.

Notes:

1. Only products with records in both the purchase and sales tables are taken into account when calculating the margin. If there are no corresponding purchases or sales for a SKU, the margin cannot be calculated, and the SKU will not appear in the results.
2. It is not possible for the same SKU to have multiple records in the table with exactly the same purchase/sale date.
3. Of course it is possible for the margin to be negative, which means there are losses on sales.
4. The calculation must be ordered by the dates of purchases and sales to maintain the FIFO method integrity.
5. It is not possible for more units of a certain product to be sold than purchased at any given moment in time because the FIFO Gross Margin calculation assumes that sales are matched to prior purchases. This means that for any sale to occur, there must have been a prior purchase to cover the quantity being sold.

For the following sample data:

purchase: 

po_id | sku | purchase_date    | price | qty
----------------------------------------------
    1 | 123 | 2023-01-01 12:25 | 20.15 |   5
    2 | 123 | 2023-05-01 15:45 | 17.50 |   3
    3 | 123 | 2023-05-02 12:00 | 15.00 |   1
    4 | 456 | 2023-06-10 16:00 | 60.00 |   7

sales: 

sale_id | sku | sale_date        | price | qty
------------------------------------------------
      1 | 123 | 2023-01-15 11:00 | 30.00 |   1
      2 | 123 | 2023-01-20 14:00 | 28.00 |   3
      3 | 123 | 2023-05-10 15:00 | 25.00 |   2
      4 | 456 | 2023-06-11 12:00 | 80.00 |   1

the expected result is the following:

sku	margin
123	45.75
456	20

Let's break down the calculation of the margin for SKU = 123 step by step:

Matching Sales to Purchases Chronologically:

1. First Sale (1 unit at $30.00):

   • Matched to the first purchase (5 units at $20.15).
   • Remaining in first purchase: 4 units.

2. Second Sale (3 units at $28.00):

   • Matched to the first purchase (4 units at $20.15).
   • Remaining in first purchase: 1 unit.
   • Remaining quantity for second sale: 0 units.

3. Third Sale (2 units at $25.00):

   • Matched to the remaining 1 unit from the first purchase (1 unit at $20.15).
   • Remaining in first purchase: 0 units.
   • Remaining quantity for third sale: 1 unit.
   • Matched to the second purchase (3 units at $17.50).
   • Remaining in second purchase: 2 units.

Total Sales Revenue

• First Sale: 30.00 × 1 = 30.00
• Second Sale: 28.00 × 3 = 84.00
• Third Sale: 25.00 × 2 = 50.00

Total Sales Revenue = 30.00 + 84.00 + 50.00 = 164.00

Total Purchase Cost

• First Purchase (5 units at $20.15):

  • Used 5 units in total (1 unit for first sale, 3 units for second sale, 1 unit for third sale). 20.15 * 5 = 100.75

• Second Purchase (3 units at $17.50):

  • Used 1 unit in total (1 unit for third sale). 17.50 * 1 = 17.50

Total Purchase Cost = 100.75 + 17.50 = 118.25

Margin Calculation

Margin = Total Sales Revenue − Total Purchase Cost

Margin = 164.00 − 118.25 = 45.75

GLHF!