I have this i/p table

i want this o/p

i have solved it with window function like row_number and partition

SELECT MIN(trdate) AS s_date,
      MAX(trdate)  AS e_date,
      t.balance
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (ORDER BY trdate)
          - ROW_NUMBER() OVER (PARTITION BY balance ORDER BY trdate) AS grp
  FROM   bank t
) t
GROUP BY grp,t.balance order by s_date;

i got the answer correct but

question was to solve this using lead and lag functions if we can solve this using lead and lag can you explain solution not familiar with lead and lag function

CodePudding user response：

You can do:

select
  min(balance) as balance,
  min(trs_date) as s_date,
  max(trs_date) as e_date
from (
  select x.*, sum(inc) over(order by trs_date) as g
  from (
    select t.*, 
      case when balance <> lag(balance) over(order by trs_date)
           then 1 else 0 end as inc
    from t
  ) x
) y
group by g

Explanation:

• The inner query marks rows with 1 or 0 depending if they start a new group or not.

• Then the middle query sums up those increments to produce group numbers.

• Finally, the external query just uses the group numbers and aggregates values.