I want to find percentage change of Open Interest for every Scrip in the table (previous day’s Open Interest value being the base value) in MySQL

Posted on

Question :

 Trade_date  | Scrip    | Open_Interest | Change_in_OI | Underlying |
+------------+----------+---------------+--------------+------------+
| 2020-03-16 | ACC      |       2669600 |      -346800 |     1133.4 |
| 2020-03-17 | ACC      |      12293200 |      -376400 |     1124.7 |
| 2020-03-16 | RELIANCE |      31975000 |       -17000 |     1015.7 |
| 2020-03-17 | RELIANCE |      31302000 |      -673000 |       1008 |

for example the output can be

|SCRIP   | % Change_in_OI |

|ACC     | -0.14          |
|RELIANCE| -0.02          |

Answer :

SELECT 
  y.Trade_date,x.Scrip, 
  (((y.Open_Interest - x.Open_Interest)/x.Open_Interest)*100)
     AS Change_in_OI from stock_future x 
JOIN stock_future y 
  ON y.Scrip = x.Scrip 
  AND y.Trade_date = '2020-03-17' 
WHERE x.Trade_date = '2020-03-16';

Leave a Reply

Your email address will not be published.