Visualize SQL | LIFO-FIFO INVENTORY

# Beginning
This article is based on 『SQL Puzzle and Answers』
　⇒ #59 LIFO-FIFO INVENTORY
It is better with this book to read this article.

you have heard of『FIFO & LIFO』at school or somewhere
・・ I think
> LIFO(Last In, First Out)
> ⇒ (e.g)
> ⇒ the most recently purchased goods are sold first
> 
> FIFO(First In, First Out)
> ⇒ (e.g)
> ⇒ older inventory is the first to be sold

▼ image of LIFO & FIFO
![re_0.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/fa7f39c5-e9f1-fee8-de78-cbc918ae35d0.png)



what you are going to do is ・・
**to calculate the purchase cost of sold units**

Let's say
you sold 100 units

There are two ways to caluculate the purchase cost
`from older purchase cost`(LIFO)
or
`from latest purchase cost`(FIFO)

but you need to be careful
　⇒ **Not purchasing 100 units on the same day**
　⇒ so you can say
　　**A total of 100 purchase price is**
　　**sum of『different purchase unit price』**


Let's get started !!

# Table & Data
```SQL:SQL
-- Inventory Table
CREATE TABLE WidgetInventory(
  receipt_nbr INTEGER NOT NULL PRIMARY KEY,
  purchase_date DATE NOT NULL,
  qty_on_hand INTEGER NOT NULL CHECK (qty_on_hand >= 0),
  unit_price DECIMAL (5,2) NOT NULL
);

 -- receipt_nbr   : receipt number
 -- purchase_date : purchase date
 -- qty_on_hand   : purchase quantity on that day
 -- unit_price    : purchase price

INSERT INTO WidgetInventory VALUES(1, '2005-08-01', 15, 10.00);
INSERT INTO WidgetInventory VALUES(2, '2005-08-02', 25, 12.00);
INSERT INTO WidgetInventory VALUES(3, '2005-08-03', 40, 13.00);
INSERT INTO WidgetInventory VALUES(4, '2005-08-04', 35, 12.00);
INSERT INTO WidgetInventory VALUES(5, '2005-08-05', 45, 10.00);

-- show table
SELECT * FROM WidgetInventory;

```
▼ Output
|receipt_nbr|purchase_date|qty_on_hand|unit_price|
|---|---|---|---|
|1|2005-08-01|15|10.00|
|2|2005-08-02|25|12.00|
|3|2005-08-03|40|13.00|
|4|2005-08-04|35|12.00|
|5|2005-08-05|45|10.00|

from this table ・・
calculate purchase price of the sold 100 units
　① from the top of the purchase date(blue)
　② from the bottom of the purchase date(orange)

**FIFO**
　⇒ extract the purchase date from the top
　⇒ chronological order(blue)
　⇒ exceed 100 on 2005-08-04

**LIFO**
　⇒ extract the purchase date from the bottom
　⇒ reverse chronological order(orange)
　⇒ exceed 100 on 2005-08-03


> Calculate the purchase price 
> by identifying the day when the total unit hits to 100
> is the interesting part of this puzzle


▼ **image of 『total unit』 hits to 100**
![re_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/3bfcde9f-3199-fb69-498e-f4536e806484.png)



# Creating Source Data①(LIFO)
```SQL:SQL
SELECT W1.*, W2.*									
FROM WidgetInventory AS W1, WidgetInventory AS W2	
```

First of all・・・
making all combinations of 
the WidgetInventory table using CROSS JOIN

since the WidgetInventory table has 5 rows
25(5 x 5) rows will be created


edit this output and make it fit to LIFO format
　⇒ **extract rows that are clear numbers below image**

▼ Output 
![re_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/91f70302-a29b-5bf3-fda4-6a49a611fc9d.png)



# Creating Source Data②(LIFO)
```SQL:SQL
SELECT W1.*, W2.*									
FROM WidgetInventory AS W1, WidgetInventory AS W2	
WHERE W1.purchase_date <= W2.purchase_date
```

filter the output using `two purchase_date`
　⇒ W1.purchase_date <= W2.purchase_date
　⇒ W2.purchase_date is the same as 
　　or later than W1.purchase_date
　⇒ 15 rows left


～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-01
　⇒ All W2.purchase_date match the condition

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-02
　⇒ four W2.purchase_date match the condition
　⇒ check image below

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-04
　⇒ two W2.purchase_date match the condition
　⇒ check image below

> you might think what is this doing(・_・?) 
>
> you will see later


▼ Output(left side of image)
![re_3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/176b1b00-0954-9304-71c2-61657b7cc748.png)



> making combinations is one of the techniques 
> often seen in this puzzle
> 
> check the article below

https://qiita.com/hidesroom/items/c8033d077350d9f8a3ff


# Edit Source Data①(LIFO)
```SQL:SQL
SELECT
  W1.purchase_date AS stock_date, 
  W1.unit_price AS unit_price,
  SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
  SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
FROM WidgetInventory AS W1, WidgetInventory AS W2
WHERE W1.purchase_date <= W2.purchase_date
GROUP BY W1.purchase_date, W1.unit_price

ORDER BY W1.purchase_date
```

▼ Output
|purchase_date|unit_price|tot_qty_on_hand|tot_cost|
|---|---|---|---|
|2005-08-01|10|160|1840.00|
|2005-08-02|12|145|1690.00|
|2005-08-03|13|120|1390.00|
|2005-08-04|12|80|870.00|
|2005-08-05|10|45|450.00|

Let's see this query in detil
check 『Creating Source Data②(LIFO)』again

`GROUP BY` (purchase_date & unit_price) on the left side
then, you can calculate 『purchase price and quantity』
using `SUM` on the right side columns
　⇒ check the blue part of the below image


> what you have done in『Creating Source Data②(LIFO)』was
> 
> preparing to `SUM`(purchase price and quantity)
> by `JOIN` W2.purchase_date later than W1.purchase_date



▼ **preparing to SUM(purchase price and quantity)**
![re_4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d060f604-6f77-cc74-8250-a4ebb08d64dc.png)


**Calculate past purchase price**
　⇒ check below image

purchase_date：2005-08-05
　⇒ unit price：10
　⇒ 45 pieces in stock
　　`purchase price：450(10 x 45)`
～～～～～～～～～～～～～～～～～～～～～～
purchase_date：2005-08-04
　⇒ unit price：12
　⇒ 35 pieces in stock
　　`purchase price：420(12 x 35)`

　⇒ added up with 2005-08-05
　　80 pieces in stock
　　`purchase price：870`
～～～～～～～～～～～～～～～～～～～～～～
purchase_date：2005-08-03
　⇒ unit price：13
　⇒ 40 pieces in stock
　　`purchase price：520(13 x 40)`

　⇒ added up with 2005-08-04 and 2005-08-05
　　120 pieces in stock
　　`purchase price：1390`

> ・・・ do the same way ~

▼ **Calculate past purchase price**
![re_5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ac277886-0cab-68e9-b9bf-1a05a4b43c08.png)


Can you see the whole picture of this puzzle・・ ？？
but what you want is `the purchase price for 100 units`

> purchase_date：2005-08-04
> ⇒ 80 pieces in stock
> 
> purchase_date：2005-08-03
> ⇒ 120 pieces in stock

It takes a little more work to take out just 100 units
the next job is ・・
**taking out the row of purchase_date(2005-08-03) and minus 20**



# Edit Source Data②(LIFO)
```SQL:SQL
WITH LIFO AS (
  SELECT 
    W1.purchase_date AS stock_date, 
    W1.unit_price AS unit_price,
    SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
    SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
  FROM WidgetInventory AS W1, WidgetInventory AS W2
  WHERE W1.purchase_date <= W2.purchase_date
  GROUP BY W1.purchase_date, W1.unit_price
)

SELECT 
 L1.*,
 (SELECT MAX(stock_date) FROM LIFO AS L2 
  WHERE tot_qty_on_hand >= 100) AS max_date
FROM LIFO AS L1
```
▼ Output (add：max_date)
|purchase_date|unit_price|tot_qty_on_hand|tot_cost|max_date|
|---|---|---|---|---|
|2005-08-01|10|160|1840.00|2005-08-03|
|2005-08-02|12|145|1690.00|2005-08-03|
|2005-08-03|13|120|1390.00|2005-08-03|
|2005-08-04|12|80|870.00|2005-08-03|
|2005-08-05|10|45|450.00|2005-08-03|


Rewrite 『Edit Source Data①(LIFO)』
　⇒ write using CTE, its name is LIFO

`max_date` column is added in the SELECT statement for checking
　⇒ **stock_date** when total inventory is over 100 units ・・
　⇒ there are a few days
　　 2005-08-01,2005-08-02 and 2005-08-03
　⇒ choose the latest date
　　 MAX(stock_date)
　　 2005-08-03

> use MAX() function to choose the date
> because you are handling LIFO data here
> 
> In the next FIFO, function will be MIN()

▼ **taking out purchase_date(2005-08-03)**
![re_5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/90fb1bf7-46c1-bf9f-47e9-9d65404d427d.png)

> the last jos is ・・・
> 
> by subtracting 20 units 
> from the total inventory held on 2005-08-03
> then, you can calculate the purchase price for 100 units


▼ **purchase price for 100 units**
![re_6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f42324a5-3729-cf01-0ca9-d7c14b13a60a.png)


# Final Query(LIFO)
```SQL:SQL
WITH LIFO AS (
  SELECT 
    W1.purchase_date AS stock_date, 
    W1.unit_price AS unit_price,
    SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
    SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
  FROM WidgetInventory AS W1, WidgetInventory AS W2
  WHERE W1.purchase_date <= W2.purchase_date
  GROUP BY W1.purchase_date, W1.unit_price
)

SELECT
  stock_date,
  tot_cost,
  tot_qty_on_hand,
  (tot_qty_on_hand - 100),
  unit_price,
  (tot_cost - ((tot_qty_on_hand - 100) * unit_price)) AS cost
FROM 
  LIFO AS L1
WHERE 
  stock_date = (SELECT MAX(stock_date) FROM LIFO AS L2 
                WHERE tot_qty_on_hand >= 100)
```


write the condition to choose purchase_date(2005-08-03)
in the WHERE clause
　⇒ stock_date = (SELECT MAX(stock_date) ・・・
　⇒ get the row of 2005-08-03


▼ Output(LIFO)
|stock_date|tot_cost|tot_qty_on_hand|tot_qty_on_hand - 100|unit_price|cost|
|---|---|---|---|---|---|
|2005-08-03|1390|120|20|13|1130|

**purchase price for 100 units is 1130 by LIFO**

`tot_cost`
　⇒ the total purchase price on 2005-08-03
　⇒ 1390
`tot_qty_on_hand`
　⇒ the total inventory units on 2005-08-03
　⇒ 120 units
`tot_qty_on_hand - 100`
　⇒ 120 - 100 = 20
　　 20 extra units
`unit_price`
　⇒ unit price on 2005-08-03
　⇒ 13
`cost`
　⇒ tot_cost - (purchase price for 20 units)
　⇒ 1390 - (20 x 13) = 1130




 　
LIFO calculation is done, next is FIFO　୧(๑›◡‹ ๑)୨


# Creating Source Data①(FIFO)
```SQL:SQL
SELECT W1.*, W2.*									
FROM WidgetInventory AS W1, WidgetInventory AS W2	
```

like you have done before(LIFO)
making all combinations of
the WidgetInventory table using CROSS JOIN

edit this output and make it fit to FIFO format
　⇒ **use rows that are clear numbers below image**

▼ Output
![re_7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/204d77c7-3253-d7ac-fa67-b88def796d6f.png)

▼ **Look at the difference of W2.WidgetInventory** (´◉ω◉`)
![re_8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/001e54fc-bb5c-a956-4504-4de076aa8f7a.png)




# Creating Source Data②(FIFO)
```SQL:SQL
SELECT W1.*, W2.*
FROM WidgetInventory AS W1, WidgetInventory AS W2
WHERE W1.purchase_date >= W2.purchase_date 
                    -- ↑ check operator
```
> 【memo】
> compared to LIFO
> the direction of the comparison operator is opposite

filter the output using `two purchase_date`
　⇒ W1.purchase_date >= W2.purchase_date
　⇒ W2.purchase_date is the same as
　　or earlier than W1.purchase_date
　⇒ 15 rows left



～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-01
　⇒ one W2.purchase_date matches the condition

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-02
　⇒ two W2.purchase_date match the condition
　⇒ check image below

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-04
　⇒ four W2.purchase_date match the condition
　⇒ check image below

▼ Output(left side of image)
![re_9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/26e87cbd-2303-9c16-b463-1bd2463c9d15.png)





# Edit Source Data①(FIFO)
```SQL:SQL
SELECT
  W1.purchase_date AS stock_date, 
  W1.unit_price AS unit_price,
  SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
  SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
FROM WidgetInventory AS W1, WidgetInventory AS W2
WHERE W1.purchase_date >= W2.purchase_date
GROUP BY W1.purchase_date, W1.unit_price

ORDER BY W1.purchase_date
```

▼ Output
|purchase_date|unit_price|tot_qty_on_hand|tot_cost|
|---|---|---|---|
|2005-08-01|10|15|150.00|
|2005-08-02|12|40|450.00|
|2005-08-03|13|80|970.00|
|2005-08-04|12|115|1390.00|
|2005-08-05|10|160|1840.00|

let's take a look this query

`GROUP BY` (purchase_date & unit_price) on the left side
then, you can calculate 『purchase price and quantity』
using `SUM` on the right side columns
　⇒ check the blue part of the below image

▼ **preparing to SUM(purchase price and quantity)**
![re_10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5d43a67c-4756-e116-aacb-b7636d5f479d.png)


Calculate past purchase price
　⇒ check below image

purchase_date：2005-08-01
　⇒ unit price：10
　⇒ 15 pieces in stock
　　`purchase price：150(10 x 15)`

～～～～～～～～～～～～～～～～～～～～～～
purchase_date：2005-08-03
　⇒ unit price：13
　⇒ 40 pieces in stock
　　`purchase price：520(13 x 40)`

　⇒ added up with 2005-08-01 and 2005-08-02
　　80 pieces in stock
　　`purchase price：970`

>・・・ do the same way ~


▼ **Calculate past purchase price**
![re_11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/142b735c-1940-e087-e07f-c3c20492ac46.png)


what you want is the purchase price for 100 units

> purchase_date：2005-08-03
> ⇒ 80 pieces in stock
> 
> purchase_date：2005-08-04
> ⇒ 115 pieces in stock

the next job is ・・
**taking out the row of purchase_date(2005-08-04) and minus 15**

# Edit Source Data②(FIFO)
```SQL:SQL
WITH FIFO AS (
  SELECT 
    W1.purchase_date AS stock_date, 
    W1.unit_price AS unit_price,
    SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
    SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
  FROM WidgetInventory AS W1, WidgetInventory AS W2
  WHERE W1.purchase_date >= W2.purchase_date
  GROUP BY W1.purchase_date, W1.unit_price
)

SELECT 
 L1.*,
 (SELECT MIN(stock_date) FROM FIFO AS L2 
  WHERE tot_qty_on_hand >= 100) AS min_date
FROM FIFO AS L1
```

▼ Output (add：min_date)
|purchase_date|unit_price|tot_qty_on_hand|tot_cost|min_date|
|---|---|---|---|---|
|2005-08-01|10|15|150.00|2005-08-04|
|2005-08-02|12|40|450.00|2005-08-04|
|2005-08-03|13|80|970.00|2005-08-04|
|2005-08-04|12|115|1390.00|2005-08-04|
|2005-08-05|10|160|1840.00|2005-08-04|


 Rewrite 『Edit Source Data①(FIFO)』
　⇒ write using CTE, its name is FIFO

 `min_date` column is added in the SELECT statement for checking
　⇒ stock_date when total inventory is over 100 units
　⇒ there are two days
　　 2005-08-04 and 2005-08-05
　⇒ select the latest date
　　 MIN(stock_date)
　　 2005-08-04

> you used MAX() function for FIFO calculation
> but here
> you use MIN() function instead


▼ **taking out purchase_date(2005-08-04)**
![re_12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/fb9d71a6-9d2a-2c31-6ab6-46ff420805e0.png)


> the last jos is ・・・
> 
> by subtracting 15 units
> from the total inventory held on 2005-08-04
> then, you can calculate the purchase price for 100 units

▼ **purchase price for 100 units**
![re_13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2d59d738-7783-8406-39e8-a88aab428d38.png)



# Final Query(FIFO)
```SQL:SQL
WITH FIFO AS (
  SELECT 
    W1.purchase_date AS stock_date, 
    W1.unit_price AS unit_price,
    SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
    SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
  FROM WidgetInventory AS W1, WidgetInventory AS W2
  WHERE W1.purchase_date >= W2.purchase_date
  GROUP BY W1.purchase_date, W1.unit_price
)

SELECT
  stock_date,
  tot_cost,
  tot_qty_on_hand,
  (tot_qty_on_hand - 100),
  unit_price,
  (tot_cost - ((tot_qty_on_hand - 100) * unit_price)) AS cost
FROM 
  FIFO AS L1
WHERE 
  stock_date = (SELECT MIN(stock_date) FROM FIFO AS L2 
                WHERE tot_qty_on_hand >= 100)
```

write the condition to choose purchase_date(2005-08-04)
in the WHERE clause
　⇒ stock_date = (SELECT MIN(stock_date) ・・・
　⇒ get the row of 2005-08-04
 

▼ Output(FIFO)
|stock_date|tot_cost|tot_qty_on_hand|tot_qty_on_hand - 100|unit_price|cost|
|---|---|---|---|---|---|
|2005-08-04|1390|115|15|12|1210|


**purchase price for 100 units is 1210 by FIFO**

`tot_cost`
　⇒ the total purchase price on 2005-08-04
　⇒ 1390
`tot_qty_on_hand`
　⇒ the total inventory units on 2005-08-04
　⇒ 115 units
`tot_qty_on_hand - 100`
　⇒ 115 - 100 = 15
　　 15 extra units
`unit_price`
　⇒ unit price on 2005-08-03
　⇒ 13
`cost`
　⇒ tot_cost - (purchase price for 20 units)
　⇒ 1390 - (15 x 13) = 1210

that's all

> personally ・・
> 
> The technique of this puzzle is 
> to create a combination of purchase dates
> and use the "MAX & MIN" functions 
> to choose a specific day

# References 
[Joe Celko's SQL Puzzles and Answers](https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)