find suggested edits by now deleted users


Please login or register to vote for this query.

(click on this box to dismiss)

Ask Different

Q&A for power users of Apple hardware and software

-- parse the Comment of typeid = 24 
;with data as (
    , postid
    , creationdate
    , comment
    , row_number() over (partition by id order by postid) [row]
    , rd.value
from posthistory
cross apply string_split(comment, ' ') rd
where posthistorytypeid = 24 -- suggested edit applied
and  charindex('|', comment) = 0


-- we store in a temptable because I suspect SQL Server
-- happily destroys the row_numbers if we go directly into the CTE
select *
into #appliedsuggestededits
from data

-- store again but now only row = 3 
     , value
     , id
     , postid
     , comment
into #appliedproposed
from #appliedsuggestededits
where row = 3 -- userid (1 = proposed, 2 = by)

-- outer join with users
-- this should return those posts
-- that have a suggested edit by a user that had an profile
-- when then suggested the edit but have since deleted their userprofile
select ap.*
     , concat('site://q/', postid, '|', 'Link') [Link]
from #appliedproposed ap
left outer join users u on = convert(int, ap.value)
where is null

Enter Parameters

Switch to meta site
loading Hold tight while we fetch your results