Rollup values between numbers > zero

Question

I have the following table of which I want to get a count between the numbers > 0. Given the table I would like to see the results of value > 0 in columns and the count of 0.0 records until the next number > 0.

Example

value	customer_id	count of 0.00
23.0000	1	2
10.00	1	3
27.0000	2	3

Table of data

value	created_at	id	customer_id	order_id
23.0000		1	1	1
0.0000		2	1	2
0.0000		3	1	3
0.0000		4	2	4
10.0000		5	1	5
0.0000		6	1	6
0.0000		7	1	7
0.0000		8	1	8
0.0000		9	2	9
0.0000		10	2	10
27.0000		11	2	11
0.0000		12	2	12
0.0000		13	2	13
0.0000		14	2	14

Answer 1

This appears to be a variation of the gaps-and-islands problem. The general solution is to calculate a running count of the special-case rows ("gaps"), group-by that running count value (which groups the data into "islands"), and finally figure the desired results from there.

I believe the following will accomplish your task:

WITH GroupedData AS (
    SELECT
        *,
        COUNT(CASE WHEN value > 0 THEN 1 END)
            OVER(PARTITION BY customer_id ORDER BY id)
            AS Grp
    FROM Data
)
SELECT
    MAX(value) AS value,
    customer_id,
    COUNT(CASE WHEN value = 0 THEN 1 END) AS [count of 0.00] -- or COUNT(*)-1
FROM GroupedData
GROUP BY customer_id, Grp
HAVING MAX(value) > 0 -- Exclude leading zero groups
ORDER BY customer_id, Grp

See this db<>fiddle for a demo.