Cacher is the code snippet organizer for pro developers

SELECT
        b.SubscriberKey as Contact_ID,
        b.EventDate as BounceDate,
        'Hard Bounce' as BounceCategory
From _Bounce as b
Inner join _Subscribers as s on s.SubscriberID=b.SubscriberID
        where b.IsUnique=1
        and DateDiff(day, b.EventDate,GetDate()) = 4
        and s.status = 'held'