SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Data (
    value NUMERIC(10,4),
    created_at DATETIME,
    id INT,
    customer_id INT,
    order_id INT
)
INSERT Data
VALUES
    (23.0000, NULL,  1, 1,  1),
    ( 0.0000, NULL,  2, 1,  2),
    ( 0.0000, NULL,  3, 1,  3),
    ( 0.0000, NULL,  4, 2,  4),
    (10.0000, NULL,  5, 1,  5),
    ( 0.0000, NULL,  6, 1,  6),
    ( 0.0000, NULL,  7, 1,  7),
    ( 0.0000, NULL,  8, 1,  8),
    ( 0.0000, NULL,  9, 2,  9),
    ( 0.0000, NULL, 10, 2, 10),
    (27.0000, NULL, 11, 2, 11),
    ( 0.0000, NULL, 12, 2, 12),
    ( 0.0000, NULL, 13, 2, 13),
    ( 0.0000, NULL, 14, 2, 14)
14 rows affected

-- Show the intermediate CTE results
WITH CTE AS (
    SELECT
        *,
        COUNT(CASE WHEN value > 0 THEN 1 END)
            OVER(PARTITION BY customer_id ORDER BY id)
            AS Grp
    FROM Data
)
SELECT *
FROM CTE
ORDER BY customer_id, id


value
created_at
id
customer_id
order_id
Grp


23.0000
null
1
1
1
1

0.0000
null
2
1
2
1

0.0000
null
3
1
3
1

10.0000
null
5
1
5
2

0.0000
null
6
1
6
2

0.0000
null
7
1
7
2

0.0000
null
8
1
8
2

0.0000
null
4
2
4
0

0.0000
null
9
2
9
0

0.0000
null
10
2
10
0

27.0000
null
11
2
11
1

0.0000
null
12
2
12
1

0.0000
null
13
2
13
1

0.0000
null
14
2
14
1


Warning: Null value is eliminated by an aggregate or other SET operation.


-- Final results
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


value
customer_id
count of 0.00


23.0000
1
2

10.0000
1
3

27.0000
2
3


Warning: Null value is eliminated by an aggregate or other SET operation.

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