Question: finding difference among record values in same column SQL
Looking to find difference between two values in same column at specific window in table.
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
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
in_stock but others are
parity will be lead for
mg else vice versa. Moreover, If the difference between
other st values for specific sid is
+ve then parity is
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