ct1.p_event_time createTime, ct2.p_event_time deleteTime,
timediff('s',createTime, deleteTime) timeExtant,
ct1.requestparameters:"bucketName"::varchar createdBucket,
ct1.useridentity:"arn"::varchar createArn, deleteArn,
ct1.useragent createUserAgent, deleteUserAgent
from panther_logs.public.aws_cloudtrail ct1
select p_event_time, requestparameters:"bucketName"::varchar deletedBucket, errorcode,
eventname deleteEvent, useridentity:"arn"::varchar deleteArn, useragent deleteUserAgent from panther_logs.public.aws_cloudtrail) ct2
on (ct1.requestparameters:"bucketName"::varchar = ct2.DeletedBucket
and ct2.p_event_time > ct1.p_event_Time
and timediff('s',ct1.p_event_time, ct2.p_event_time) < 3600)
where ct2.deleteEvent = 'DeleteBucket'
and ct1.eventName = 'CreateBucket'
and ct1.errorCode is null and ct2.errorcode is null
and p_occurs_since('1 day',ct2) -- apply to ct2
and p_occurs_since('24 hours',ct1) -- apply to ct1
order by createdBucket, createTime;