Complex Ranking in SQL (Teradata)

Question

I have a peculiar problem at hand. I need to rank in the following manner:

1. Each ID gets a new rank.
2. rank #1 is assigned to the ID with the lowest date. However, the subsequent dates for that particular ID can be higher but they will get the incremental rank w.r.t other IDs. (E.g. ADF32 series will be considered to be ranked first as it had the lowest date, although it ends with dates 09-Nov, and RT659 starts with 13-Aug it will be ranked subsequently)
3. For a particular ID, if the days are consecutive then ranks are same, else they add by 1.
4. For a particular ID, ranks are given in date ASC.

How to formulate a query?

Answer 1

You need two steps:

select
   id_col
  ,dt_col
  ,dense_rank()
   over (order by min_dt, id_col, dt_col - rnk) as part_col  
from
 (
    select
       id_col
      ,dt_col
      ,min(dt_col)
       over (partition by id_col) as min_dt
      ,rank()
       over (partition by id_col
             order by dt_col) as rnk
    from tab
 ) as dt

dt_col - rnk caluclates the same result for consecutives dates -> same rank

Answer 2

One way to think about this problem is "when to add 1 to the rank". Well, that occurs when the previous value on a row with the same id_col differs by more than one day. Or when the row is the earliest day for an id.

This turns the problem into a cumulative sum:

select t.*,
       sum(case when prev_dt_col = dt_col - 1 then 0 else 1
           end) over 
            (order by min_dt_col, id_col, dt_col) as ranking
from (select t.*,
             lag(dt_col) over (partition by id_col order by dt_col) as prev_dt_col,
             min(dt_col) over (partition by id_col) as min_dt_col
      from t
     ) t;

Answer 3

Try datediff on lead/lag and then perform partitioned ranking

select t.ID_COL,t.dt_col, 
rank() over(partition by t.ID_COL, t.date_diff order by t.dt_col desc) as rankk 
from ( SELECT ID_COL,dt_col, 
DATEDIFF(day, Lag(dt_col, 1) OVER(ORDER BY dt_col),dt_col) as date_diff FROM table1 ) t