SQL Puzzle - Grouping Deals Solution

Here is the query which I wrote to solve the SQL Puzzle - Grouping Deals.

Also here I want to mention that this solution here is just a query to solve the problem, and there could be many other ways to solve it in a better way. So please try yourself too :)

Just rephrasing the problem statement again to give you context:

If the deal key, price and quantity combination is same for successive months then club those rows into single row and show start_date as minimum contract_month, End_Date as maximum contract_month of that group, and total quantity as the sum of total_quantity column as below:

with t as
(
select 2 deal_id, 'Jan2013' contract_month, 4    price, 10000 quantity, 310000 total_quantity from dual
union all
select 2 deal_id, 'Feb2013' contract_month, 4    price, 10000 quantity, 280000 total_quantity from dual
union all
select 2 deal_id, 'Mar2013' contract_month, 4.5  price, 10000 quantity, 310000 total_quantity from dual
union all
select 2 deal_id, 'Apr2013' contract_month, 4    price, 10000 quantity, 310000 total_quantity from dual
union all
select 2 deal_id, 'May2013' contract_month, 4    price, 15000 quantity, 280000 total_quantity from dual
union all
select 2 deal_id, 'Jun2013' contract_month, 4    price, 15000 quantity, 310000 total_quantity from dual
)
select
  min(deal_id) deal_id,
  to_char(min(contract_month),'MonYYYY') Start_Date,
  to_char(max(contract_month),'MonYYYY') End_Date,
  min(Price) Price,
  min(Quantity) Quantity,
  sum(Total_Quantity) total_quantity
from
(
  select
    rn,
    deal_id,
    contract_month,
    price,
    QUANTITY,
    total_quantity,
    sum(flg) over(order by rn) Orderedrnk
  from
  (
    select
      rn,
      deal_id,
      contract_month,
      price,
      quantity,
      total_quantity,
      case when 
        deal_id = lag(deal_id) over(order by rn) and
        price = lag(price) over(order by rn) and
        quantity = lag(quantity) over(order by rn)
      then 0 else 1 end flg  
    from
    (
      SELECT
        row_number() over (order by TO_DATE(contract_month,'MonYYYY')) rn,
        deal_id,
        TO_DATE(contract_month,'MonYYYY') contract_month,
        price,
        quantity,
        total_quantity
      from
        t
    )
  )
)
group by Orderedrnk order by Orderedrnk ;

More SQL Puzzles:
- SQL Puzzle - Transpose Rows and Shift Values among columns
- Graph Shortest Path Solution by SQL
- Diamond Shaped Star Pattern by SQL
- Sorting Versions stored in Varchar2 Column
- SQL Puzzle - Grouping Deals
- SQL Puzzle - Consecutive Wins
- SQL Puzzle - Issue Tracker
- SQL Interview Question Answers