Percentage of the orders shipped before first message date

Given the tables Order_Timeline(schema id,order_id, message, created) & Order_Shipment Table(schema id, order_id,actual_dispatch_date,created) , write a SQL Query to find % orders shipped before first message date(OTIF) % orders shipped on first message date+1(OTIF+1) % orders shipped on first message date+2(OTIF+2) %orders shipped after that(OTIF+>2)

(select a.OTIF, b.OTIF1, c.OTIF2, d.OTIF3, e.total FROM  (SELECT count(*) as total from (Select ot.order_id,  DATE(os.actual_dispatch_date) As dispatch_date from order_timeline ot  inner join order_shipment os  on ot.order_id = os.order_id where dispatch_date != "None")) as e  inner join   (Select count(*) as OTIF FROM (Select * FROM (select ot.order_id, DATE(os.actual_dispatch_date) As dispatch_date from order_timeline ot  inner join order_shipment os  on ot.order_id = os.order_id where dispatch_date != "None") as x  where x.message_date >= x.dispatch_date) as y) as a  inner join  (Select count(*) as OTIF1 FROM (Select * FROM (select ot.order_id, DATE(os.actual_dispatch_date) As dispatch_date from order_timeline ot  inner join order_shipment os  on ot.order_id = os.order_id where dispatch_date != "None") as x  where x.dispatch_date = date(x.message_date, '+1 day')) as z) as b  inner join  (Select count(*) as OTIF2 FROM (Select * FROM (select ot.order_id, DATE(os.actual_dispatch_date) As dispatch_date from order_timeline ot  inner join order_shipment os  on ot.order_id = os.order_id where dispatch_date != "None") as x  where x.dispatch_date = date(x.message_date, '+2 day')) as z) as c  inner join   (Select count(*) as OTIF3 FROM (Select * FROM (select ot.order_id, DATE(os.actual_dispatch_date) As dispatch_date from order_timeline ot  inner join order_shipment os  on ot.order_id = os.order_id where dispatch_date != "None") as x  where x.dispatch_date > date(x.message_date, '+2 day')) as z) as d) as m  ;    is this right?  

https://stackoverflow.com/questions/66556947/percentage-of-the-orders-shipped-before-first-message-date March 10, 2021 at 09:04AM