Привет, подписчики!😎

Выкладываю решения по пятничной задачки.

Данные:
with rest_history (id_dog, dt, balance) as (
select 111, to_date('10.07.2022', 'dd.mm.yyyy'), 10 union all
select 111, to_date('15.08.2022', 'dd.mm.yyyy'), 0 union all
select 111, to_date('20.09.2022', 'dd.mm.yyyy'), 15 union all
select 111, to_date('15.07.2022', 'dd.mm.yyyy'), 25 union all
select 111, to_date('15.07.2022', 'dd.mm.yyyy'), 35 union all
select 222, to_date('15.07.2022', 'dd.mm.yyyy'), 5 union all
select 222, to_date('15.08.2022', 'dd.mm.yyyy'), 7 union all
select 222, to_date('15.09.2022', 'dd.mm.yyyy'), 0 union all
select 222, to_date('03.03.2023', 'dd.mm.yyyy'), 33 union all
select 222, to_date('04.04.2023', 'dd.mm.yyyy'), 44 union all
select 222, to_date('10.05.2023', 'dd.mm.yyyy'), 0
) select * from rest_history


1)

SELECT
id_dog, dt, new_dt
FROM
(
SELECT
id_dog, dt, balance,
COALESCE((LEAD(dt) OVER(PARTITION BY id_dog ORDER BY dt) - INTERVAL '1 day'),
to_date('31.12.2999', 'DD.MM.YYYY')) AS new_dt
FROM
(
SELECT
id_dog, dt, balance,
CASE WHEN COALESCE(LAG(balance) OVER(PARTITION BY id_dog ORDER BY dt), 0) = 0
THEN 1
ELSE 0
END AS bal_flg
FROM rest_history
) t1
WHERE bal_flg = 1 OR balance = 0
) t2
WHERE balance != 0
ORDER BY id_dog, dt

2)
,rest_history_l AS (
SELECT
ID_DOG,
DT,
BALANCE,
lag(BALANCE, 1, 0) OVER (PARTITION BY ID_DOG ORDER BY DT) AS BALANCE_L
FROM rest_history
)

SELECT
rl.ID_DOG,
to_char(rl.DT, 'DD.MM.YYYY') AS DATE_BEG,
COALESCE(
to_char(
(SELECT MIN(rl1.DT) - INTERVAL '1 day'
FROM rest_history_l rl1
WHERE rl1.BALANCE = 0 AND rl1.BALANCE_L 0
AND rl1.DT > rl.DT
AND rl1.ID_DOG = rl.ID_DOG),
'DD.MM.YYYY'),
'31.12.2999'
) AS DATE_END
FROM rest_history_l rl
WHERE BALANCE > 0 AND BALANCE_L = 0
ORDER BY ID_DOG, DT

3)
SELECT id_dog,
MIN(dt_start) AS dt_start,
COALESCE(dt_end, TO_DATE('31.12.2999', 'DD.MM.YYYY')) AS dt_end
FROM (
SELECT id_dog,
dt AS dt_start,
MIN(CASE WHEN balance = 0 THEN dt - INTERVAL '1 day' END)
OVER(PARTITION BY id_dog ORDER BY dt
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS dt_end
FROM rest_history
) t
GROUP BY id_dog, dt_end
ORDER BY id_dog, dt_end

Позапускайте решения, сравните разные реализации 👍

it пингвин | data engineer 🐧