Codes!

SELECT model_audits.entity_id as "Transaction ID",hh.v , gg.v, timestampdiff(hour,hh.v,gg.v) as derog, timestampdiff(hour,from_unixtime(order_items.created_at),gg.v) as "Time Betwen firt interference and time of transaction creation"

 FROM model_audits

 LEFT join order_items on model_audits.entity_id = order_items.id and entity_name= 'order_item'

 left join 

 (select model_audits.entity_id as x ,model_audits.id as c, min(from_unixtime(created_at)) as v 

 from model_audits

 where entity_name= 'order_item'

 and(

 (action = 'delivery date has been changed' and revision_number = 4)

  or

  (action = 'pickup date has been changed' and revision_number = 4)

 )

 and entity_id = ANY (select entity_id from `model_audits` 

  WHERE 

  (`action` = 'delivery date has been changed' and revision_number = 4)

  or

  (`action` = 'pickup date has been changed' and revision_number = 4)

 ) 

 and model_audits.entity_id = ANY (select entity_id from `model_audits` 

  WHERE 

  (`action`='order has been confirmed' and revision_number = 3 )

  )

 and entity_id = ANY (select entity_id from `model_audits` 

  WHERE 

  (`action` = 'delivery dates have been selected' and revision_number = 2)

  or

  (`action` = 'delivery dates hav been selected' and revision_number = 2)

 )  

 group by 2

 ) as gg on gg.x = model_audits.entity_id and entity_name = 'order_item'

left join 

 (select model_audits.entity_id as x ,model_audits.id as c, from_unixtime(created_at) as v 

 from model_audits

 where entity_name= 'order_item'

 and

 (`action`='order has been confirmed' and revision_number = 3 )

 and model_audits.entity_id = ANY (select entity_id from `model_audits` 

  WHERE 

  (`action`='order has been confirmed' and revision_number = 3 )

  )

 and entity_id = ANY (select entity_id from `model_audits` 

  WHERE 

  (`action` = 'delivery dates have been selected' and revision_number = 2)

  or

  (`action` = 'delivery dates hav been selected' and revision_number = 2)

 )  

 group by 2

 ) as hh on hh.x = model_audits.entity_id and entity_name = 'order_item'

 WHERE entity_name= 'order_item'

 and from_unixtime(model_audits.created_at) between '2016-01-01 00:00:00' and now()

 and from_unixtime(order_items.created_at) between '2016-01-01 00:00:00' and now()

 and entity_id not in (12029,7523,1624,11332,2450,8561,8331,11408,2168,2546,2581,2722,2765,4816)

 group by 1

 having derog is not null