Interesting question! I'm going to use the model clause to calculate this. But I'm sure there's other ways.
Ready? Here goes:
There are three cases to consider:
1. The current transaction is Sales. And there are no other sales transactions within the 40 previous days. This starts the sequence at 1.
2. There is a sales transaction in the previous 40 days. Increment the sequence counter.
3. Neither 1 or 2 apply. And there's a sales transaction in the 10 following days.
If all these fail the sequence is zero.
First up, we need to setup the model. The calculations are per customer. So you need to partition by this. And you want to sort by transactiondate. So let's calculate a row number based on this.
partition by (custid)
dimension by (
row_number() over (partition by custid order by transactiondate ) rn
)
The dimension by enables us to reference the rows.
In the output, as well as the custid, you want to return the sequence, transactiondate and type. So let's add these to the measures clause:
measures (0 seq, transactiondate, trans)
Now we need to supply the calculations for seq. These come from the steps above. We'll put these in a case statement. This will assign values to all the rows. So the rules clause starts with:
seq[any] = case ...
We then place the calculations above in the when clauses.
The first part of 1 is easy. Simply check:
trans[cv()] = 'Sales'
i.e. the current value (cv()) of the transaction type is Sales. But we only want to consider the first sales transaction in a 40 day period. So we need to exclude any after in this timeframe. We can use the first_value analytic to do this:
first_value(
case when trans = 'Sales' then 'Sales' end) ignore nulls over (
order by transactiondate range between 40 preceding and 1 preceding
)
The window clause "range between 40 preceding and 1 preceding" considers transactions in the previous 40 days. Note its range, not rows!
We only care about sales transactions. We can ignore the others by mapping them to null and using the "ignore nulls" clause. This what "case when trans = 'Sales' then 'Sales' end) ignore nulls" does.
Then we just need to check that this value isn't sales! This gives the start of each sales sequence. So the first clause is:
when trans[cv()] = 'Sales' and
coalesce(
first_value(
case when trans = 'Sales' then 'Sales' end) ignore nulls over (
order by transactiondate range between 40 preceding and 1 preceding
), 'not sales'
) != 'Sales' then
1
For the second step, we can reuse the last_value calculation above. If this is sales, then we want to increment the sequence counter:
seq[cv()-1]+1
This finds the value of seq for the previous row. Then adds one!
So the next clause is:
when first_value(case when trans = 'Sales' then 'Sales' end) ignore nulls over (
order by transactiondate range between 40 preceding and 1 preceding
) = 'Sales' then
seq[cv()-1]+1
Now we need to find the transactions in the 10 days before a sale. Again, we can use first_value. But instead of the "40 preceding and 1 preceding", we want to look at the rows from the current onward. This is "range between current row and 10 following".
Now we can start counting down. We can do this by looking at the value for seq in the next row. Then subtract 1 from it.
The row immediately before the sales transaction will see a "next seq" of 1. Take one from this and we get zero. But you want to skip this. So if the next value is 1, we need to map it to -1.
This gives:
when first_value(case when trans = 'Sales' then 'Sales' end) ignore nulls over (
order by transactiondate range between current row and 10 following
) = 'Sales' then
case when seq[cv()+1] = 1 then -1 else seq[cv()+1]-1 end
Note to look forward like this you need to state "rules automatic order". Otherwise you'll get an "ORA-32637: Self cyclic rule in sequential order MODEL" error.
Finally, we need the "nothing matched" case. This is simple an else clause that returns zero.
Put it all together and you get:
drop table t purge;
CREATE TABLE t (CustId varchar2(4), TransactionDate date, Trans varchar2(14) );
alter session set nls_date_format = 'yyyy-mm-dd';
INSERT INTO t VALUES ('C001', '2016-01-01', 'Inquiry');
INSERT INTO t VALUES ('C001', '2016-01-10', 'Sales');
INSERT INTO t VALUES ('C001', '2016-01-21', 'Repeat Inquiry');
INSERT INTO t VALUES ('C001', '2016-02-10', 'Repeat Inquiry');
INSERT INTO t VALUES ('C001', '2016-05-01', 'Sales');
INSERT INTO t VALUES ('C001', '2016-05-06', 'Sales');
INSERT INTO t VALUES ('C001', '2016-06-10', 'Inquiry 1');
INSERT INTO t VALUES ('C001', '2016-09-01', 'Inquiry 2');
INSERT INTO t VALUES ('C002', '2016-02-01', 'Inquiry 1');
INSERT INTO t VALUES ('C002', '2016-02-25', 'Inquiry 2');
INSERT INTO t VALUES ('C002', '2016-03-04', 'Inquiry 4');
INSERT INTO t VALUES ('C002', '2016-05-03', 'Inquiry 3');
INSERT INTO t VALUES ('C003', '2016-02-01', 'Inquiry 2');
INSERT INTO t VALUES ('C003', '2016-02-10', 'Sales');
INSERT INTO t VALUES ('C003', '2016-03-10', 'Inquiry 2');
INSERT INTO t VALUES ('C004', '2016-04-15', 'Sales');
INSERT INTO t VALUES ('C005', '2016-06-01', 'Inquiry 1');
select custid, transactiondate, trans, seq from t
model
partition by (custid)
dimension by (
row_number() over (partition by custid order by transactiondate ) rn
)
measures (0 seq, transactiondate, trans)
rules automatic order (
seq[any] = case
when trans[cv()] = 'Sales' and
coalesce(
first_value(
case when trans = 'Sales' then 'Sales' end) ignore nulls over (
order by transactiondate range between 40 preceding and 1 preceding
), 'not sales'
) != 'Sales' then
1
when first_value(case when trans = 'Sales' then 'Sales' end) ignore nulls over (
order by transactiondate range between 40 preceding and 1 preceding
) = 'Sales' then
seq[cv()-1]+1
when
first_value(case when trans = 'Sales' then 'Sales' end) ignore nulls over (
order by transactiondate range between current row and 10 following
) = 'Sales' then
case when seq[cv()+1] = 1 then -1 else seq[cv()+1]-1 end
else 0
end
)
order by 1, 2;
CUSTID TRANSACTIONDATE TRANS SEQ
C001 2016-01-01 Inquiry -1
C001 2016-01-10 Sales 1
C001 2016-01-21 Repeat Inquiry 2
C001 2016-02-10 Repeat Inquiry 3
C001 2016-05-01 Sales 1
C001 2016-05-06 Sales 2
C001 2016-06-10 Inquiry 1 3
C001 2016-09-01 Inquiry 2 0
C002 2016-02-01 Inquiry 1 0
C002 2016-02-25 Inquiry 2 0
C002 2016-03-04 Inquiry 4 0
C002 2016-05-03 Inquiry 3 0
C003 2016-02-01 Inquiry 2 -1
C003 2016-02-10 Sales 1
C003 2016-03-10 Inquiry 2 2
C004 2016-04-15 Sales 1
C005 2016-06-01 Inquiry 1 0