SQL derived value by a set of Row

Question

Hi my table looks like this

Combination of Rows with same S id is termed as a family. Output should have a Calculated Tier code based on below derivation

Output put should be Output

First time i am using Stackoverflow. I need a query in db2 and I can use the output as a report.

Answer 1

You need windows function as follows:

Select t.*,
       Concat('Tier ', count(*) over (partition by s_id) ) as tier
  From your_table t