How to make a query from table that counts how many days is a SKU without stock

I have a MySQL database with a table called stock_log with this data:

sku	date	quantity stock
111	2021-11-18	0
123	2021-11-18	0
146	2021-11-18	0
111	2021-11-19	5
123	2021-11-19	4
146	2021-11-19	0
111	2021-11-20	3
123	2021-11-20	4
146	2021-11-20	0
111	2021-11-21	3
123	2021-11-21	0
146	2021-11-21	0
111	2021-11-22	2
123	2021-11-22	0
146	2021-11-22	0
111	2021-11-23	0
123	2021-11-23	0
146	2021-11-23	2

So, I need to make a query that counts how many days a SKU is without stock to today (2021-11-23), only if today is without stock, grouped by SKU. Also it must count the days without stock from the last date when it has no stock (because a SKU could have stock 3 consecutive days, then again 0, then again it has stock and then again 0).

So the query should shows:

sku	days without stock
111	0
123	2

NOTES:

SKU 111: 0 days without stock from today. 123
SKU 123: 2 days without stock from 2021-11-21 (the last date without stock) to today.
SKU 146: Don't show in this result because today it has stock again from today.

I hope to explain it well.

Thanks for your help! :)

Solution

You can solve the problem using next query:

select
    sku, 
    DATEDIFF( -- date diff in days between
        CURRENT_DATE,  -- current date
        MAX(if(quantity > 0, `date`, '')) -- last date with stock
    ) days_without_stock
from sku
group by sku
having days_without_stock > 0;

MySQL group by fiddle