--set up dummy tables. These should match everywhere that's important
declare @prodTable table (
	UserID int,
	AboutMe nvarchar(100)
)

declare @testAudit table (
	UserID int,
	ChangeDate date,
	AboutMe_Before varchar(100)

)

--either comment this or change the user ID to test 'row deleted' functionality
insert into @prodTable (UserID, AboutMe) values
(26837, 'This is not null'),
(26838, 'abcde')

--note dates are different. I just need a little time difference in order to sort.
--This assumption should hold for real auditing anyway - changes will be at different times
insert into @testAudit (UserID, ChangeDate, AboutMe_Before) values
(26837, '2024-01-01', '<p>I make Microsoft SQL Server etc etc'),
(26838, '2024-01-02', 'original'),
(26837, '2024-01-03', 'Update Numero Uno'),
(26838, '2024-01-04', 'an update'),
(26837, '2024-01-05', null);

--append the current value in the prod table as an 'audit' for each user id.
--Also add an extra column - lastUpdate, so that the last row is not shown
with a as (
	select *, 0 as lastUpdate from @testAudit

	union

	select t.UserID, 
	getdate() as ChangeDate,
	case when max(p.AboutMe) is null then 'row deleted' else max(p.AboutMe) end as AboutMe,
	1 as lastUpdate
	from @testAudit t
	left join @prodTable p on t.UserID = p.UserID
	group by t.UserID
),

--extra cte so the lead function doesn't cut off
b as (
	select
	UserID,
	ChangeDate,
	AboutMe_Before,
	lead(aboutMe_Before,1) over (partition by UserID order by changeDate) as AboutMe_After,
	lastUpdate
	from a
)

--remove the last update
select * from b where lastUpdate = 0