how to know the changed name in table by date_key

Question

i have a table with 3 value

Date_key    | user_name  | user_id
2022-07-12  | milkcotton | 1
2022-09-12  | cereal     | 2
2022-06-12  | musicbox1  | 3
2022-12-31  | harrybel1  | 1
2022-12-25  | milkcotton1| 4
2023-01-01  | cereal     | 2

i want to know the user who changed the user_name in 1 semester (01 july 2022 - 31 december 2022). Can i do this? my expected value is:

previous_name| new_name  | user_id
milkcotton  | harrybel1 | 1

Thank you!

know the changed of the user_name from 1 table

Answer 1

Note: This is done in Postgres SQL. This should be similar in most of the SQL engines. Date functions could slightly different in other SQL engines.

Try this:

with BaseTbl as(
 select *,
  cast(to_char(Date_key, 'YYYYMM') as int) as year_month,
  cast(to_char(Date_key, 'MM') as int) as month,
  row_number() over(partition by user_id order by date_key desc) as rnk
 from Table1
   ),
   LatestTwoChanges as(
  select * 
  from BaseTbl
  where user_id in (select user_id from BaseTbl where rnk=2 ) 
  and rnk <=2
   )
  select 
  t2.user_name as previous_name,
  t1.user_name as new_name,
  t1.user_id
  from LatestTwoChanges t1
  join LatestTwoChanges t2
  on t1.user_id=t2.user_id
  where t1.rnk=1
  and t2.rnk=2
  and t1.year_month-t2.year_month <6
  and t1.user_name <> t2.user_name
  and (t1.month + t2.month <= 12 or  t1.month + t2.month >=14 )
  -- this is to check whether the date falling in the same semester. 

SQL fiddle demo Here

Here, the table t1 contains the latest changes and table t2 contains the previous changes for a user_id. The last filter condition

and (t1.month + t2.month <= 12 or  t1.month + t2.month >=14 )

is to make sure that the two dates are falling in the same semester or not . which means the two months should be either between 1 and 6 or 7 and 12