clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1811884 fiddles created (25308 in the last week).

CREATE TABLE test (d DATE, k CHAR(2), v INT)
 hidden batch(es)


INSERT INTO test VALUES ( '2019-01-02', 'S1', 20 ), ( '2019-02-04', 'S1', 30 ), ( '2019-03-10', 'S2', 15 ), ( '2019-04-07', 'S1', 0 ), ( '2019-04-13', 'S2', 35 ), ( '2019-04-19', 'S1', 10 ), ( '2019-05-01', 'S1', 30 ), ( '2019-05-15', 'S1', 40 ), ( '2019-06-21', 'S1', 0 )
9 rows affected
 hidden batch(es)


WITH cte AS ( SELECT *, COUNT(CASE WHEN v=0 THEN 0 END) OVER (PARTITION BY k ORDER BY d DESC) r FROM test ) SELECT k AS key, MIN(d) AS "date", MAX(d) AS date_of_zero FROM cte WHERE r > 0 GROUP BY k, r ORDER BY 1, 2
key date date_of_zero
S1 2019-01-02 2019-04-07
S1 2019-04-19 2019-06-21
 hidden batch(es)