How to set a window in a table to a value of 0 in a field?

Question

SQL

There is a card_info table:

Клиент Дата Баланс
1   01.03.2010 150
1   02.03.2010 100
1   03.03.2010 200
1   04.03.2010 0
1   05.03.2010 50
1   06.03.2010 0
2   16.03.2010 1000
2   17.03.2010 0

For each client, you need to display all periods where the balance is different from zero and the average balance . You

should get the following table:

Клиент Дата1 дата2 Баланс
1   01.03.2010 03.03.2010 150
1   05.03.2010 05.03.2010 50
2   16.03.2010 16.03.2010 1000

I don’t understand how to stop in the grouping at 0, I know about the lead function for partition by which shows the next element, but it’s still unclear how it will help to recognize where to group to. I will be glad for any hint)

Answer 1

I probably overcomplicated, but so far it turned out like this (DBMS - Postgres):

select client_id, st, en, avg(bal)
  from (
    select *,
           max(day) filter (where l = 1) over (partition by client_id order by day) st,
           min(day) filter (where r = 1) over (partition by client_id order by day range between current row and unbounded following) en
      from (
         select *,
           case when (lag(bal) over w) > 0 then 0 else 1 end as l,
           case when (lead(bal) over w) > 0 then 0 else 1 end as r
          from balance
        window w as (partition by client_id order by day)
      ) t
      where bal > 0
) t1 group by client_id, st, en
  order by 1, 2

The innermost query defines the boundary rows (beginning/end of the non-zero balance period). Intermediate - calculates the dates of the boundaries, external - groups and calculates the average
https://www.db-fiddle.com/f/495cu1zp5BSmZDPwqXJ9Ev/0

Answer 2

SELECT Client, AVG(Balance) AS AverageBalance WHERE Balance>0 GROUP BY Client