FREE AI-Enhanced Online PostgreSQL Compiler

Untitled*

You must execute the code again to save those values in the database.

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);

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

item	times_out_of_stock	days_out_of_stock
Bicycle	2	12,155

CREATE TABLE
INSERT 0 11