Setting alias name as per timestamp-CodePudding

I am trying to set an alias name for column challenge_name especially when I encounter 'challenge' based on timestamp/date as challenge 1, challenge 2, etc. If it's not 'challenge' I would like to set the column name the same as challenge_name

This is my current input

 ----------- ---------------- 
|   date    | challenge_name |
 ----------- ---------------- 
| 1/11/2022 | find the bug   |
| 1/12/2022 | challenge      |
| 1/13/2022 | kill pill      |
| 1/14/2022 | hello copter   |
| 1/15/2022 | challenge      |
| 1/16/2022 | miami          |
| 1/17/2022 | hello world    |
| 1/18/2022 | challenge      |
| 1/19/2022 | dominc         |
| 1/20/2022 | challenge      |
 ----------- ----------------

This is my expected output

 ----------- ---------------- -------------- 
|   date    | challenge_name | updated_name |
 ----------- ---------------- -------------- 
| 1/11/2022 | find the bug   | find the bug |
| 1/12/2022 | challenge      | challenge 1  |
| 1/13/2022 | kill pill      | kill pill    |
| 1/14/2022 | hello copter   | hello copter |
| 1/15/2022 | challenge      | challenge 2  |
| 1/16/2022 | miami          | miami        |
| 1/17/2022 | hello world    | hello world  |
| 1/18/2022 | challenge      | challenge 3  |
| 1/19/2022 | dominc         | dominc       |
| 1/20/2022 | challenge      | challenge 4  |
 ----------- ---------------- --------------

CodePudding user response：

A simple case with rank should do the trick. Rank will assign a rank for each specific challenge_name group, I didn't filter out only the values when challenge_name = challenge, because they are filtered on the case condition

select `date`,
       challenge_name,
       case  when challenge_name = 'challenge' then concat('challenge ', rank() over(partition by challenge_name order by `date` asc )) 
             else challenge_name end  as challenge_name                               
  from test
  order by `date` asc ;

https://dbfiddle.uk/oRJ0bQh7

CodePudding user response：

This might work:

-- Fuzzy match
CASE 
    WHEN REGEXP_LIKE(challenge_name, 'challenge') THEN 
        CONCAT('challenge ', 
               ROW_NUMBER() OVER (
                   PARTITION BY REGEXP_LIKE(challenge_name, 'challenge')
                   ORDER BY date
               )
        )
    ELSE challenge_name
END AS updated_name

EDIT

Added order by clause to the window function