Skip to Main Content
  • Questions

  • Solving a logical problem using analytical functions

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ST.

Asked: April 18, 2025 - 3:22 pm UTC

Last updated: April 28, 2025 - 8:13 am UTC

Version: Oracle 19

Viewed 1000+ times

You Asked

( https://livesql.oracle.com/ords/livesql/s/da34i74lkmxt2mqrtp0k4xsk6 )

(I was able to format better with screenshots on the Oracle forums - https://forums.oracle.com/ords/apexds/post/help-solving-a-logical-problem-using-analytical-query-3778 )


Hi,

I am trying to solve a problem using analytical functions but I am stuck.

1. I have a list of coupons that I can use. The usage sequence is in alphabetical order of the coupon name.

+---------+-------+
| Coupons | Value |
+---------+-------+
| A | 100 |
+---------+-------+
| B | 40 |
+---------+-------+
| C | 120 |
+---------+-------+
| D | 10 |
+---------+-------+
| E | 200 |
+---------+-------+

2. There is a limit (cap) on the total value across all coupons that can be used in a day
+----------+-----------+
| Cap Name | Cap Limit |
+----------+-----------+
| Cap 1 | 150 |
+----------+-----------+
| Cap 2 | 70 |
+----------+-----------+

3. Each coupon is subject to 1 or 2 caps. If it is subject to 2 caps, there is a specified sequence to apply caps.
+--------+--------------+----------+
| Coupon | Cap Sequence | Cap Name |
+--------+--------------+----------+
| A | 1 | Cap 1 |
+--------+--------------+----------+
| A | 2 | Cap 2 |
+--------+--------------+----------+
| B | 1 | Cap 2 |
+--------+--------------+----------+
| C | 1 | Cap 2 |
+--------+--------------+----------+
| C | 2 | Cap 1 |
+--------+--------------+----------+
| D | 1 | Cap 1 |
+--------+--------------+----------+
| E | 1 | Cap 1 |
+--------+--------------+----------+
| E | 2 | Cap 2 |
+--------+--------------+----------+

4. I have to now find how much coupon value could be utilized before my daily cap was reached. i.e. find "coupon usage" and “Cap Remaining” below.
So, If I join the tables above

+---+--------+-------+----------+--------------+-----------+--------------+---------------+
| # | Coupon | Value | Cap Name | Cap Sequence | Cap Limit | Coupon Usage | Cap Remaining |
+---+--------+-------+----------+--------------+-----------+--------------+---------------+
| 1 | A | 100 | Cap 1 | 1 | 150 | 100 | 50 |
+---+--------+-------+----------+--------------+-----------+--------------+---------------+
| 2 | A | 100 | Cap 2 | 2 | 70 | 0 | 70 |
+---+--------+-------+----------+--------------+-----------+--------------+---------------+
| 3 | B | 40 | Cap 2 | 1 | 70 | 40 | 30 |
+---+--------+-------+----------+--------------+-----------+--------------+---------------+
| 4 | C | 120 | Cap 2 | 1 | 70 | 30 | 0 |
+---+--------+-------+----------+--------------+-----------+--------------+---------------+
| 5 | C | 120 | Cap 1 | 2 | 150 | 50 | 0 |
+---+--------+-------+----------+--------------+-----------+--------------+---------------+
| 6 | D | 10 | Cap 1 | 1 | 150 | 0 | 0 |
+---+--------+-------+----------+--------------+-----------+--------------+---------------+
| 7 | E | 200 | Cap 1 | 1 | 150 | 0 | 0 |
+---+--------+-------+----------+--------------+-----------+--------------+---------------+
| 8 | E | 200 | Cap 2 | 2 | 70 | 0 | 0 |
+---+--------+-------+----------+--------------+-----------+--------------+---------------+

Explanation:
Row #1 : Coupon A has a value of 100 and that is less than the first cap i.e. Cap 1 with a limit of 150. So we use all of Coupon A. Cap 1 has a limit of 50 remaning.
Row #2: Coupon A was all used against Cap 1 and we did not have to consume Cap 2.
Row #3: Coupon B of value 40 and it is less than Cap 2 with a limit of 70. So we use all of Coupon B. Cap 2 has now 30 remaining.
Row #4: Coupon C has a value of 120. It is subject to Cap 2 first that has 30 remaining. So we use 30 and Cap 2 now has 0 remaining. Coupon C has (120 - 30) i.e. 90 residual value remaining.
Row #5: Coupon C has a 90 value remaning that is now capped against Cap 1 with a 50 limit. So we use 50 and Cap 1 becomes 0
Row #6 - #8: Caps have become 0. Nothing can be used.

I am able to make this work using analytical function with a single cap. The 2 caps with a possibly different usage sequence has me stuck.

with coupon_data(coupon, value)as 
(
select 'A',100 from dual union all
select 'B',40 from dual union all
select 'C',120 from dual union all
select 'D',10 from dual union all
select 'E',200 from dual 
)
, cap_data(cap_name, cap_limit) as
(
select 'Cap1', 150 from dual union all
select 'Cap2', 70 from dual 
)
, coupon_cap_mapping(coupon, cap_sequence, cap_name) as
(
select 'A',1,'Cap1' from dual union all
select 'A',2,'Cap2' from dual union all
select 'B',1,'Cap2' from dual union all
select 'C',1,'Cap2' from dual union all
select 'C',2,'Cap1' from dual union all
select 'D',1,'Cap1' from dual union all
select 'E',1,'Cap1' from dual union all
select 'E',2,'Cap2' from dual 
)
SELECT cd.coupon,
       cd.value,
       cap_d.cap_name,
       ccm.cap_sequence,
       cap_d.cap_limit
       --, coupon_usage, cap_remaining 
FROM coupon_data cd
JOIN coupon_cap_mapping ccm ON ( cd.coupon = ccm.coupon )
JOIN cap_data           cap_d ON ( cap_d.cap_name = ccm.cap_name )
ORDER BY cd.coupon,
         ccm.cap_sequence;



( https://livesql.oracle.com/ords/livesql/s/da34i74lkmxt2mqrtp0k4xsk6 )

When I try providing the link to livesql, I get the "LiveSQL link must point to a valid shared script link" error.

and Chris said...

I see you already have a solution on the forum. As discussed in this month's SQL Office Hours, a (pure) MATCH_RECOGNIZE solution performs much better than recursive WITH.

I can't find a way to calculate the used/remaining totals with pattern matching alone though. So this uses MODEL to calculate these values after using pattern matching to determine whether a coupon can be used. Perhaps someone in the community can find a way to simplify this into one pattern matching solution.

The basics of the pattern variables to see if there's remaining capacity is:

and sum ( limit1.value ) - value < limit1.cap_limit  -- 1
and ( 
          ( coupon = prev ( coupon ) and sum ( limit2.value ) > limit2.cap_limit ) -- 2
          or coupon <> nvl ( prev ( coupon ), 'x' ) -- 3
        )


1 - Check the value running total minus the value for the current row is less than the limit (i.e. there's capacity remaining). When this is the case, use the spare capacity if either:
2 - The coupon is the same as the previous, and the total used for the other coupon is greater than it's limit (i.e. this coupon isn't completely used)
3 - It's a new coupon

Have two separate pattern variables for this, one for each cap. The pattern checks whether each row matches either of these or any row, zero or more times:

pattern ( ( limit1 | limit2 | other )* )


Follow this by a MODEL clause, which calculates the amount used and remaining for each capacity. The key here is the CV() function. This identifies the current row. In this case, these are increasing integers assigned by ROW_NUMBER. So:

least ( value [cv()], c1_remaining[cv()-1] )


Means return the smallest of the current value, or the amount remaining for the previous row.

A final case expression at the end combines these separate usage and remaining columns into one.

Complete solution below. I'm not sure how MATCH_RECOGNIZE + MODEL will perform compared to recursive WITH alone, but it's worth testing. Hopefully someone will be able to find a way to simplify this further!

create table coupon_data ( coupon, value ) as ( 
select 'A',100 from dual union all 
select 'B',40 from dual union all 
select 'C',120 from dual union all 
select 'D',10 from dual union all 
select 'E',200 from dual  
);

create table cap_data ( cap_name, cap_limit ) as ( 
select 'Cap1', 150 from dual union all 
select 'Cap2', 70 from dual  
);

create table coupon_cap_mapping ( coupon, cap_sequence, cap_name ) as ( 
select 'A',1,'Cap1' from dual union all 
select 'A',2,'Cap2' from dual union all 
select 'B',1,'Cap2' from dual union all 
select 'C',1,'Cap2' from dual union all 
select 'C',2,'Cap1' from dual union all 
select 'D',1,'Cap1' from dual union all 
select 'E',1,'Cap1' from dual union all 
select 'E',2,'Cap2' from dual  
);

with coupon_caps as (
  select cd.coupon, 
         cd.value, 
         cap_d.cap_name, 
         ccm.cap_sequence, 
         cap_d.cap_limit 
  from   coupon_data cd 
  join   coupon_cap_mapping ccm on ( cd.coupon = ccm.coupon ) 
  join   cap_data           cap_d on ( cap_d.cap_name = ccm.cap_name ) 
), matches as ( 
  select mr.*, 
        row_number () over ( order by coupon, cap_sequence ) as rn 
  from   coupon_caps match_recognize (
    order by coupon, cap_sequence
    all rows per match 
    pattern ( ( limit1 | limit2 | other )* )
    define 
      limit1 as 
            cap_name = 'Cap1' 
        and sum ( limit1.value ) - value < limit1.cap_limit 
        and ( 
          ( coupon = prev ( coupon ) and sum ( limit2.value ) > limit2.cap_limit ) 
          or coupon <> nvl ( prev ( coupon ), 'x' )
        ),
      limit2 as 
            cap_name = 'Cap2' 
        and sum ( limit2.value ) - value < limit2.cap_limit 
        and (
          ( coupon = prev ( coupon ) and sum ( limit1.value ) > limit1.cap_limit )
          or coupon <> nvl ( prev ( coupon ) , 'x' )
        )
  ) mr
), calcs as ( 
  select *
  from   matches model 
    dimension by ( rn ) 
    measures  ( 
      0 as c1_usage, 0 as c1_remaining, 
      0 as c2_usage, 0 as c2_remaining, 
      cap_limit, cap_name, coupon, cap_sequence, value
    )
    rules automatic order (
      c1_usage [any] = case 
        when c1_remaining[cv()-1] = 0 then 0
        when cv(rn) = 1 and cap_name[cv()] = 'Cap1' then least ( value[cv()], cap_limit[cv()] )
        when cap_name[cv()] = 'Cap2' then c1_usage [cv()-1] 
        when coupon[cv()] = coupon[cv()-1] then least ( value[cv()] - c2_usage[cv()-1], c1_remaining[cv()-1] )
        else least ( value [cv()], c1_remaining[cv()-1] )
      end,
      c1_remaining [any] = case 
        when c1_remaining[cv()-1] = 0 then 0
        when cv(rn) = 1 and cap_name[cv()] = 'Cap1' then greatest ( cap_limit[cv()] - value[cv()], 0 )
        when cap_name[cv()] = 'Cap2' then c1_remaining [cv()-1] 
        when coupon[cv()] = coupon[cv()-1] then greatest ( nvl ( c1_remaining[cv()-1], cap_limit[cv()] ) - least ( value[cv()] - c2_usage[cv()-1], value[cv()-1] ), 0 )
        else c1_remaining[cv()-1] - c1_usage[cv()]
      end,
      c2_usage [any] = case 
        when c2_remaining[cv()-1] = 0 then 0
        when cv(rn) = 1 and cap_name[cv()] = 'Cap2' then least ( value[cv()], cap_limit[cv()] )
        when cap_name[cv()] = 'Cap1' then c2_usage [cv()-1] 
        when coupon[cv()] = coupon[cv()-1] then value[cv()] - c1_usage[cv()-1]
        else least ( value [cv()], c2_remaining[cv()-1] )
      end,
      c2_remaining [any] = case 
        when c2_remaining[cv()-1] = 0 then 0
        when cv(rn) = 1 and cap_name[cv()] = 'Cap2' then greatest ( cap_limit[cv()] - value[cv()], 0 )
        when cap_name[cv()] = 'Cap1' then c2_remaining [cv()-1] 
        when coupon[cv()] = coupon[cv()-1] then greatest ( nvl ( c2_remaining[cv()-1], cap_limit[cv()] ) - least ( value[cv()] - c1_usage[cv()-1], value[cv()-1] ), 0 )
        else c2_remaining[cv()-1] - c2_usage[cv()]
      end
    )
)
select coupon, value, cap_name, cap_sequence, cap_limit, 
       case cap_name
         when 'Cap1' then c1_usage 
         when 'Cap2' then c2_usage 
       end usage, case cap_name
         when 'Cap1' then c1_remaining 
         when 'Cap2' then c2_remaining 
       end remaining
from   calcs c
order by coupon, 
         cap_sequence;

C      VALUE CAP_ CAP_SEQUENCE  CAP_LIMIT      USAGE  REMAINING
- ---------- ---- ------------ ---------- ---------- ----------
A        100 Cap1            1        150        100         50
A        100 Cap2            2         70          0         70
B         40 Cap2            1         70         40         30
C        120 Cap2            1         70         30          0
C        120 Cap1            2        150         50          0
D         10 Cap1            1        150          0          0
E        200 Cap1            1        150          0          0
E        200 Cap2            2         70          0          0

Rating

  (2 ratings)

Comments

Simple Model solution with arbitrary caps

Brendan, April 26, 2025 - 12:35 pm UTC

I wanted to solve this for arbitrary caps, and used an iterative Model clause to do so.

The subquery joins the tables and adds a row number ordered by coupon and cap_sequence. In the measures clause, Lag is used twice: to add a row number for the prior record with the cap to decrement from; and also to store the prior coupon. I then iterate over the rows, updating 3 of the measures based on the prior record and prior cap record.

The algorithm could easily be implemented in PL/SQL, perhaps as a pipelined function, using an array to hold the relevant values for a single coupon, and this would likely scale better than the Model solution.

WITH cap_coupon_cross AS (
SELECT Row_Number() OVER (ORDER BY cou.coupon, ccm.cap_sequence) ccc_ind,
       cou.coupon,
       cou.value,
       cap.cap_name,
       ccm.cap_sequence,
       cap.cap_limit
  FROM cap_data           cap
  JOIN coupon_cap_mapping ccm ON ccm.cap_name = cap.cap_name
  JOIN coupon_data        cou ON cou.coupon = ccm.coupon 
)
SELECT *
  FROM cap_coupon_cross
MODEL 
  DIMENSION BY (ccc_ind)
  MEASURES (Lag(ccc_ind, 1, ccc_ind) OVER (PARTITION BY cap_name ORDER BY coupon) pri_ccc_ind, 
            coupon,
            Lag(coupon, 1, 'NA') OVER (ORDER BY ccc_ind) pri_coupon,
            value,
            cap_name,
            cap_sequence, 
            cap_limit,
            0 usage,
            value val_left,
            cap_limit cap_left)
  RULES UPDATE ITERATE (1000000) UNTIL coupon[ITERATION_NUMBER + 1] IS NULL (
    usage[ITERATION_NUMBER + 1]     = CASE WHEN coupon[ITERATION_NUMBER + 1] != pri_coupon[ITERATION_NUMBER + 1] THEN
                                                    Least (val_left[ITERATION_NUMBER + 1], cap_left[pri_ccc_ind[ITERATION_NUMBER + 1]])
                                           ELSE     Least (val_left[ITERATION_NUMBER], cap_left[pri_ccc_ind[ITERATION_NUMBER + 1]])
                                      END,
    val_left[ITERATION_NUMBER + 1]  = CASE WHEN coupon[ITERATION_NUMBER + 1] != pri_coupon[ITERATION_NUMBER + 1] THEN
                                                    val_left[ITERATION_NUMBER + 1] - usage[ITERATION_NUMBER + 1]
                                           ELSE     val_left[ITERATION_NUMBER] - usage[ITERATION_NUMBER + 1]
                                      END,
    cap_left[ITERATION_NUMBER + 1]  = cap_left[pri_ccc_ind[ITERATION_NUMBER + 1]] - usage[ITERATION_NUMBER + 1]
  )
 ORDER BY ccc_ind
/
   CCC_IND PRI_CCC_IND C PR      VALUE CAP_ CAP_SEQUENCE  CAP_LIMIT      USAGE   VAL_LEFT   CAP_LEFT
---------- ----------- - -- ---------- ---- ------------ ---------- ---------- ---------- ----------
         1           1 A NA        100 Cap1            1        150        100          0         50
         2           2 A A         100 Cap2            2         70          0          0         70
         3           2 B A          40 Cap2            1         70         40          0         30
         4           3 C B         120 Cap2            1         70         30         90          0
         5           1 C C         120 Cap1            2        150         50         40          0
         6           5 D C          10 Cap1            1        150          0         10          0
         7           6 E D         200 Cap1            1        150          0        200          0
         8           4 E E         200 Cap2            2         70          0        200          0

8 rows selected.


Connor McDonald
April 28, 2025 - 8:12 am UTC

nice stuff

Pipelined function solution

Brendan, April 27, 2025 - 7:56 am UTC

Here's a pipelined function solution which, as I said, would likely scale better than the Model solution. I had no need for analytic functions in this one.

CREATE OR REPLACE TYPE caps_obj AS OBJECT (
        coupon          VARCHAR2(10), 
        value           INTEGER,
        cap_name        VARCHAR2(10),
        cap_sequence    INTEGER, 
        cap_limit       INTEGER, 
        usage           INTEGER, 
        val_left        INTEGER,
        cap_left        INTEGER
)
/
CREATE OR REPLACE TYPE caps_tab AS TABLE OF caps_obj;
/
CREATE OR REPLACE FUNCTION caps RETURN caps_tab PIPELINED IS
    CURSOR cap_coupon_csr IS
    SELECT cou.coupon,
           cou.value,
           cap.cap_name,
           ccm.cap_sequence,
           cap.cap_limit
      FROM cap_data           cap
      JOIN coupon_cap_mapping ccm ON ccm.cap_name = cap.cap_name
      JOIN coupon_data        cou ON cou.coupon = ccm.coupon
     ORDER BY cou.coupon, ccm.cap_sequence;
    l_pri_coupon            VARCHAR2(10) := 'NA';
    TYPE cap_left_ibt IS    TABLE OF NUMBER INDEX BY VARCHAR2(10);
    l_cap_left              cap_left_ibt;
    l_usage                 PLS_INTEGER;
    l_val_left              PLS_INTEGER;
BEGIN
    FOR rec IN (SELECT cap_name, cap_limit FROM cap_data) LOOP
        l_cap_left(rec.cap_name) := rec.cap_limit;
    END LOOP;
    FOR rec IN cap_coupon_csr LOOP
        IF rec.coupon != l_pri_coupon THEN
            l_val_left := rec.value;
        END IF;
        l_usage                  := Least (l_val_left, l_cap_left(rec.cap_name));
        l_val_left               := l_val_left - l_usage;
        l_cap_left(rec.cap_name) := l_cap_left(rec.cap_name) - l_usage;
        l_pri_coupon             := rec.coupon;
        PIPE ROW(caps_obj(rec.coupon,
                          rec.value,
                          rec.cap_name,
                          rec.cap_sequence,
                          rec.cap_limit,
                          l_usage,
                          l_val_left,
                          l_cap_left(rec.cap_name)));
    END LOOP;
END caps;
/
SELECT * FROM TABLE(caps)
/
Type created.
Type created.
Function created.

COUPON VALUE CAP_NAME CAP_SEQUENCE CAP_LIMIT USAGE VAL_LEFT CAP_LEFT
------ ----- -------- ------------ --------- ----- -------- --------
A        100 Cap1                1       150   100        0       50
A        100 Cap2                2        70     0        0       70
B         40 Cap2                1        70    40        0       30
C        120 Cap2                1        70    30       90        0
C        120 Cap1                2       150    50       40        0
D         10 Cap1                1       150     0       10        0
E        200 Cap1                1       150     0      200        0
E        200 Cap2                2        70     0      200        0

8 rows selected.

Connor McDonald
April 28, 2025 - 8:13 am UTC

Scalability aside, I often recommend pipelined functions to people over complex SQL solutions, because only half the challenge of coding is getting something to work...The other half is having the code maintainable by the next person that comes after you
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.