Unable to resolve Rank Over Partition with multiple variables

Question

I am trying to analyse a bunch of transaction data and have set up a series of different ranks to help me. The one I can't get right is the beneficiary rank. I want it to partition where there is a change in beneficiary chronologically rather than alphabetically.

Where the same beneficiary is paid from January to March and then again in June I would like the June to be classed a separate 'session'.

I am using Teradata SQL if that makes a difference.

I thought the solution was going to be a DENSE_RANK but if I PARTITION BY (CustomerID, Beneficiary) ORDER BY SystemDate it counts up the number of months. If I PARTITION BY (CustomerID) ORDER BY Beneficiary then it is not chronological, I need the highest rank to be the latest Beneficiary.

SELECT CustomerID, Beneficiary, Amount, SystemDate, Month
  ,RANK() OVER(PARTITION BY CustomerID ORDER BY SystemDate ASC) AS PaymentRank
  ,RANK() OVER(PARTITION BY CustomerID ORDER BY PaymentMonth ASC) AS MonthRank
  ,RANK() OVER(PARTITION BY CustomerID , Beneficiary ORDER BY SystemDate ASC) AS Beneficiary
  ,RANK() OVER(PARTITION BY CustomerID , Beneficiary, ROUND(TRNSCN_AMOUNT, 0) ORDER BY SYSTEM_DATE ASC) AS TransRank
FROM table ORDER BY CustomerID, PaymentRank

CustomerID  Beneficiary Amount  DateStamp   Month   PaymentRank MonthRank   BeneficiaryRank TransactionRank
a   aa  10      Jan 1   1   1   1
a   aa  20      Feb 2   2   2   1
a   aa  20      Mar 3   3   3   2
a   aa  20      Apr 4   4   4   3
a   bb  20      May 5   5   1   1
a   bb  30      Jun 6   6   2   1
a   aa  30      Jul 7   7   5   2
a   aa  30      Aug 8   8   6   1
a   cc  5       Sep 9   9   1   1
a   cc  5       Oct 10  10  2   2
a   cc  5       Nov 11  11  3   3
b   cc  5       Dec 1   1   1   1

This is what I have so far, I want a column alongside this which will look like the below

CustomerID  Beneficiary Amount  DateStamp   Month   NewRank
a   aa  10      Jan 1
a   aa  20      Feb 1
a   aa  20      Mar 1
a   aa  20      Apr 1
a   bb  20      May 2
a   bb  30      Jun 2
a   aa  30      Jul 3
a   aa  30      Aug 3
a   cc  5       Sep 4
a   cc  5       Oct 4
a   cc  5       Nov 4
b   cc  5       Dec 1

Answer 1

This is a type of gaps-and-islands problem. I would recommend lag() and a cumulative sum:

select t.*,
       sum(case when prev_systemdate > systemdate - interval '1' month then 0 else 1 end) over (partition by customerid, beneficiary order by systemdate)            
from (select t.*,
             lag(systemdate) over (partition by customerid, beneficiary order by systemdate) as prev_systemdate
      from t
     ) t

Answer 2

SELECT dt.*,
   -- now do a Cumulative Sum over those 0/1
   SUM(flag)
   OVER(PARTITION BY CustomerID
        ORDER BY SystemDate ASC
                ,flag DESC -- needed if the order by columns are not unique
        ROWS UNBOUNDED PRECEDING) AS NewRank
FROM
 ( 
    SELECT CustomerID, Beneficiary, Amount, SystemDate, Month
      ,RANK() OVER(PARTITION BY CustomerID ORDER BY SystemDate ASC) AS PaymentRank
      ,RANK() OVER(PARTITION BY CustomerID ORDER BY PaymentMonth ASC) AS MonthRank
      ,RANK() OVER(PARTITION BY CustomerID , Beneficiary ORDER BY SystemDate ASC) AS Beneficiary
      ,RANK() OVER(PARTITION BY CustomerID , Beneficiary, ROUND(TRNSCN_AMOUNT, 0) ORDER BY SYSTEM_DATE ASC) AS TransRank
      -- assign a 0 if current & previous Beneficiary are the same, otherwise 1 
      ,CASE WHEN Beneficiary = LAG(Beneficiary) OVER(PARTITION BY CustomerID ORDER BY SystemDate) THEN 0 ELSE 1 END AS flag
    FROM table 
 ) AS dt
ORDER BY CustomerID, PaymentRank

Your problem with Gordon's query is probably caused by your Teradata release, LAG is only supported in 16.10+. But there's a simple workaround:

LAG(Beneficiary) OVER(PARTITION BY CustomerID ORDER BY SystemDate)

--is equivalent to 
MIN(Beneficiary) OVER(PARTITION BY CustomerID ORDER BY SystemDate
                      ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING))

Answer 3

Credits to @Gordon and @dnoeth for providing the ideas and code to get me on the right track.

The below is mostly ripped from dnoeth but needed to add ROWS unbounded preceding to get the aggregation correct. Without this it was just showing the total for the partition. I also changed the systemdate to paymentrank as I had to fiddle about a bit with duplicate entries on a day.

SELECT dt.*,
   -- now do a Cumulative Sum over those 0/1
   SUM(flag) OVER(PARTITION BY CustomerID ORDER BY PaymentRank ASC ROWS UNBOUNDED PRECEDING) AS NewRank
FROM
 ( 
    SELECT CustomerID, Beneficiary, Amount, SystemDate, Month
      -- assign a 0 if current & previous Beneficiary are the same, otherwise 1 
      ,CASE WHEN  Beneficiary = MIN(Beneficiary) OVER (PARTITION BY CustomerID ORDER BY PaymentRank ASC  ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)  THEN 0 ELSE 1 END  AS Flag ) AS dt
ORDER BY CustomerID, PaymentRank

The inner query sets a flag whenever the beneficiary changes. The outer query then does a cumulative sum on those.

I was unsure what the unbounded preceding was doing and @dnoeth has a great explanation here Below is taken from that explanation.

•UNBOUNDED PRECEDING, all rows before the current row -> fixed

•UNBOUNDED FOLLOWING, all rows after the current row -> fixed

•x PRECEDING, x rows before the current row -> relative

•y FOLLOWING, y rows after the current row -> relative