Using PostgreSQL to calculate how often and how many days a store's inventory is out of stock

Recently a Reddit user posted in r/SQL wanting to know how to calculate how often and how many days an item was out of stock.

I enjoyed coming up with a solution and so I've decided to explore my answer in more detail on this website.

Note that I'll be using PostgreSQL.

The data

First, the dataset we'll be operating on:

The original poster explained that a record is created in the database (1st Jan 2024) before inventory is recieved for an item.

The other records in the dataset are when a count of inventory is conducted.

It's worth noting that each row in our dataset is when an inventory check was made so it is possible that bicycles could have come in and out of stock between inventory checks. However that's a data entry problem, not ours. We can only work with the data we've been given!

If you want to follow along in Postgres here is the code to create the table and insert the data:

CREATE TABLE stock (
    item VARCHAR(50),
    transaction_date DATE,
    inventory_level INT
);

INSERT INTO stock (item, transaction_date, inventory_level) VALUES
('Bicycle', '2024-01-01', 0),
('Bicycle', '2024-01-05', 0),
('Bicycle', '2024-01-10', 10),
('Bicycle', '2024-01-15', 5),
('Bicycle', '2024-01-16', 0),
('Bicycle', '2024-01-20', 0),
('Bicycle', '2024-01-23', 0),
('Bicycle', '2024-01-25', 0),
('Bicycle', '2024-01-28', 20),
('Bicycle', '2024-01-29', 12),
('Bicycle', '2024-01-31', 0)
;

The challenge

We want to find out how often our store was out of stock of bicycles and for how may days.

Counting from the right start date

We only want to start counting from when inventory is first recieved, NOT when the item is first entered into the system or when an inventory count is conducted before stock has ever been recieved.

We can do this by using a correlated subquery:

SELECT A.*
FROM STOCK AS A 
WHERE A.TRANSACTION_DATE >= 
    (
    SELECT MIN(B.TRANSACTION_DATE)
    FROM STOCK AS B
    WHERE B.ITEM = A.ITEM 
    AND B.INVENTORY_LEVEL != 0
    )

Result set:

Calculating how many times bicycles were sold out

Next, let's identify when the bicycle goes out of stock.

How do we do this?

It's simple enough to assert by verifying how much inventory is on hand:

CASE WHEN INVENTORY_LEVEL = 0 THEN 'OUT OF STOCK' ELSE 'IN STOCK' END

However we want to calculate how often an item goes out of stock so to do this what we'll want to do is identify when an item goes out of stock after having previously had stock.

In other words, we want to capture every instance in which on the last inventory count day there was stock and on the current counting day there was no stock.

We can achieve this using the LAG() window function to see how much inventory there was on the previous transcation date:

SELECT
A.*
, CASE 
    WHEN A.INVENTORY_LEVEL = 0 -- No stock!
    and LAG(INVENTORY_LEVEL, 1) OVER (PARTITION BY ITEM ORDER BY TRANSACTION_DATE) != 0 -- The previous day there WAS stock.
    THEN 'OUT_OF_STOCK' 
    WHEN A.INVENTORY_LEVEL != 0 THEN 'IN STOCK'
END AS STOCK_STATUS
FROM STOCK AS A 
WHERE A.TRANSACTION_DATE >= 
    (
    SELECT MIN(B.TRANSACTION_DATE)
    FROM STOCK AS B
    WHERE B.ITEM = A.ITEM 
    AND B.INVENTORY_LEVEL != 0
    )

The Result set:

Perfect! We can see that twice bicycles have gone out of stock.

Calculating how long bicycles were sold out

Next, let's calculate how long bicycles were sold out for.

All we need to do is calculate the difference in days between when an item was flagged as having gone out of stock and when it next came into stock (or more accurately, when an inventory check was recorded that revealed there was stock).

This is simple enough using the LEAD() window function, which we can use to return the next transcation date.

As we've filtered out rows with NULL for the stock_status column, what will be returned is, if it exists, the next transcation date in which an inventory count is conducted that reveals bicycles are back in stock.

I've also wrapped COALESCE() around LEAD() so if there is no next row (meaning bicycles are still out of stock) what's returned is the current date so the date calculation will work:

SELECT 
*
, COALESCE(LAG(TRANSACTION_DATE,1) OVER (PARTITION BY ITEM ORDER BY TRANSACTION_DATE DESC), CURRENT_DATE)
- TRANSACTION_DATE AS DAYS_OUT_OF_STOCK
FROM 
(
    SELECT
    A.*
    , CASE 
        WHEN A.INVENTORY_LEVEL = 0 
        AND LAG(INVENTORY_LEVEL, 1) OVER (PARTITION BY ITEM ORDER BY TRANSACTION_DATE) != 0 
        THEN 'OUT_OF_STOCK' 
        WHEN A.INVENTORY_LEVEL != 0 THEN 'IN STOCK'
    END AS STOCK_STATUS
    FROM STOCK AS A 
    WHERE 
    A.TRANSACTION_DATE >= 
        (
        SELECT MIN(B.TRANSACTION_DATE)
        FROM STOCK AS B
        WHERE B.ITEM = A.ITEM 
        AND B.INVENTORY_LEVEL != 0
        )
) C
WHERE STOCK_STATUS IS NOT NULL
ORDER BY 2

Result set:

Perfect!

We have our answer.

The Redditor who I helped asked for a single row per item which broke out how many times the item was out of stock and for how long (in a comma-seperated string) so let's do that:

SELECT   
ITEM
, COUNT(STOCK_STATUS) AS TIMES_OUT_OF_STOCK
, STRING_AGG(DAYS_OUT_OF_STOCK::VARCHAR, ',') AS DAYS_OUT_OF_STOCK
FROM 
    (
    SELECT 
    *
    , COALESCE(LAG(TRANSACTION_DATE,1) OVER (PARTITION BY ITEM ORDER BY TRANSACTION_DATE DESC), CURRENT_DATE)
    - TRANSACTION_DATE AS DAYS_OUT_OF_STOCK
    FROM 
    (
        SELECT
        A.*
        , CASE 
            WHEN A.INVENTORY_LEVEL = 0 
            AND LAG(INVENTORY_LEVEL, 1) OVER (PARTITION BY ITEM ORDER BY TRANSACTION_DATE) != 0 
            THEN 'OUT_OF_STOCK' 
            WHEN A.INVENTORY_LEVEL != 0 THEN 'IN STOCK'
        END AS STOCK_STATUS
        FROM STOCK AS A 
        WHERE 
        A.TRANSACTION_DATE >= 
            (
            SELECT MIN(B.TRANSACTION_DATE)
            FROM STOCK AS B
            WHERE B.ITEM = A.ITEM 
            AND B.INVENTORY_LEVEL != 0
            )
    ) C
    WHERE STOCK_STATUS IS NOT NULL
    ORDER BY 2
    ) AS D
WHERE STOCK_STATUS = 'OUT_OF_STOCK'
GROUP BY 1

Our final result set:

You can also test the code used in this post here.