sqlteradatardbmsanalytical

Complex Ranking in SQL (Teradata)

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.

sample

How to formulate a query?

Solution

  • 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