You cannot have two columns with the same name in your temporary table 'bob'. If you run the query without "create temporary table" statement, it will show the following columns: " person_id | event_type | event_period | event_type | event_period". That's why you are getting the error. To fix the error, rename the columns using AS clause.
create temporary table bob as (
SELECT DISTINCT X1.person_id, X1.event_type as event1, X1.event_period as event_period1, X2.event_type as event2, X2.event_period as event_period2
FROM mdcr2003_2015_mortality.events_of_interest X1, mdcr2003_2015_mortality.events_of_interest X2
WHERE X1.person_id = X2.person_id
AND X1.event_type = 'death'
AND upper(X1.event_period) < lower(X2.event_period)