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

1 comment:

  1. Oracle 12c and upwards supports MATCH_RECOGNIZE a truly magical extension to sql for complex analytical calculations.


    Part 1) Solution to problem statement

    Note: I broke out contractMonth into Year and Month components to cover for use cases where the contractMonth group of rows to be merged spans across years.. i.e from Oct2013 to May2014 (say).


    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
    ),
    Q1 as (
    select
    deal_id, contract_month,
    to_number(to_char(to_date(contract_month, 'MonYYYY'), 'YYYY')) as YearId, to_number(to_char(to_date(contract_month, 'MonYYYY'), 'MM')) as MonthId,
    price, quantity, total_quantity
    from t
    )
    select
    MR1.deal_id,
    MR1.contract_month_strt as start_date,
    nvl(MR1.contract_month_end_mm, MR1.contract_month_strt) as end_date,
    MR1.price, MR1.quantity,
    MR1.total_quantity_strt + nvl(MR1.total_quantity_mm,0) as total_quantity
    from Q1
    MATCH_RECOGNIZE (
    partition by deal_id, price, quantity
    order by yearid, monthid
    measures
    strt.contract_month as contract_month_strt,
    strt.total_quantity as total_quantity_strt,
    last(mergemonth.contract_month) as contract_month_end_mm,
    sum(mergemonth.total_quantity) as total_quantity_mm
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (strt mergemonth*)
    DEFINE
    mergemonth as (yearid*12) + monthid = (prev(yearid)*12) + prev(monthid) + 1
    ) MR1
    order by to_date(MR1.contract_month_strt, 'MonYYYY')
    ;

    gives

    DEAL_ID START_D END_DAT PRICE QUANTITY TOTAL_QUANTITY
    ---------- ------- ------- ---------- ---------- --------------
    2 Jan2013 Feb2013 4 10000 590000
    2 Mar2013 Mar2013 4.5 10000 310000
    2 Apr2013 Apr2013 4 10000 310000
    2 May2013 Jun2013 4 15000 590000


    Part 2) Modified requirements ... try changing 4.5 of Mar2013 to 4.

    Same SQL, output becomes:

    DEAL_ID START_D END_DAT PRICE QUANTITY TOTAL_QUANTITY
    ---------- ------- ------- ---------- ---------- --------------
    2 Jan2013 Apr2013 4 10000 1210000
    2 May2013 Jun2013 4 15000 590000


    cheers
    Shankar

    ReplyDelete