can we solve basic transaction query using lead and lag window function in sql

Question

I have this i/p table

balance	trs_date
27000	2020-01-01
27000	2020-01-02
27000	2020-01-03
31000	2020-01-04
31000	2020-01-05
27000	2020-01-06
27000	2020-01-07
32000	2020-01-08
31000	2020-01-09

i want this o/p

balance	s_date	e_date
27000	2020-01-01	2020-01-03
31000	2020-01-04	2020-01-05
27000	2020-01-06	2020-01-07
32000	2020-01-08	2020-01-08
31000	2020-01-09	2020-01-09

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

Answer 1

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.