finding difference among record values in same column SQL

Looking to find difference between two values in same column at specific window in table.

Data:

date        sid category    st  st_status   %discount 20/05/22    123 c1          az  in_stock          12 20/05/22    123 c1          ph  in_stock          10 20/05/22    123 c1          mg  out_of_stock      20 20/05/22    345 c2          mg  out_of_stock     20/05/22    345 c2          az  in_stock           2 20/05/22    789 c3          mg  in_stock           0 20/05/22    789 c3          ph  out_of_stock     

Expected results:

date        sid category    st  st_status   %discount   parity 20/05/22    123 c1          az  in_stock       12       lead 20/05/22    123 c1          ph  in_stock       10       lead 20/05/22    123 c1          mg  in_stock       20    20/05/22    345 c2          mg  out_of_stock         20/05/22    345 c2          az  in_stock        2        lag 20/05/22    789 c3          mg  in_stock        0    20/05/22    789 c3          ph  out_of_stock             lead 

Note : The parity is w.r.t mg & if mg is in_stock but others are out_of_stock then parity will be lead for mg else vice versa. Moreover, If the difference between mg & other st values for specific sid is +ve then parity is lead else lag

It would be great if st column is in pivot form for every date & sid.

What I have tried:

select date, sid, category, st, st_status, %discount, lag(%discount) over (partition by date, sid, st order by date) as lag_discount from table1 

This will not work as my date is fixed as a window

Answers 1: of finding difference among record values in same column SQL

with data_cte as ( select  date1,sid,category,st,st_status,discount,condition1, case when st = 'mg' then null when tab.condition1 > 0 OR tab.condition2 = 1 then 'lead' when  tab.condition1 < 0 OR tab.condition2 = 0 then 'lag' end lead_lag from  ( select *, case when  (select count(*) from test t1  where t1.date1 = t.date1      and t1.sid = t.sid      and t1.category = t.category      and t1.st = 'mg'      and t1.discount is null) > 0     then -1 else sum( case when st = 'mg'    then discount * (-1)    else discount end)   over (partition by date1, sid,category  order by null)  end condition1, case when st = 'mg' then null when st<>'mg' and st_status = 'out_of_stock' and (select count(*) from test t1  where t1.date1 = t.date1      and t1.sid = t.sid      and t1.category = t.category      and t1.st <> 'mg'      and t1.st_status = 'in_stock') = 0 and (select count(*) from test t1  where t1.date1 = t.date1      and t1.sid = t.sid      and t1.category = t.category      and t1.st = 'mg'      and t1.st_status = 'in_stock') > 0  then 1 else 0 end condition2 from test t ) tab ) select date1, sid, max(case when st = 'mg' then lead_lag end)  'mg', max(case when st='az' then lead_lag end)  'az', max(case when st = 'ph' then lead_lag end)  'ph' from data_cte group by date1,sid;