How to create a new column in a select statement using some conditions in Mysql?

[Zajjith Ahmath]

sf_name finish_position official_rating date bsp diff
Camanche Grey 11 NULL 2013-09-18 72.53 0.00
Camanche Grey 4 NULL 2013-11-08 12.50 0.00
Camanche Grey 3 NULL 2014-02-27 24.00 0.00
Camanche Grey 6 65 2014-04-24 32.00 0.00
Camanche Grey 13 63 2014-05-05 109.06 0.00
Camanche Grey 1 59 2014-07-04 4.16 0.00
Camanche Grey 2 65 2014-08-04 5.10 -6.00
Camanche Grey 12 68 2014-10-28 55.00 -9.00
Camanche Grey 9 62 2014-12-09 110.89 -3.00
Camanche Grey 5 65 2015-01-08 61.07 -6.00
Camanche Grey 7 60 2015-01-29 12.54 -1.00
Camanche Grey 11 65 2015-04-08 15.50 -6.00
Camanche Grey 5 63 2015-04-22 36.07 -4.00
Camanche Grey 1 60 2015-07-14 4.25 0.00
Camanche Grey 9 60 2015-08-04 5.30 0.00
Camanche Grey 8 56 2016-01-22 22.67 4.00
Camanche Grey 4 52 2016-02-26 11.76 8.00
Camanche Grey 7 50 2016-03-31 4.30 10.00
Camanche Grey 8 48 2016-04-25 6.31 12.00
Camanche Grey 4 56 2016-07-12 9.50 4.00
Camanche Grey 8 47 2016-09-23 23.47 13.00
Camanche Grey 9 52 2017-06-20 114.54 8.00
Camanche Grey 1 50 2017-07-04 15.51 0.00
Camanche Grey 3 55 2017-07-20 5.29 -5.00
Camanche Grey 3 48 2017-07-29 15.51 2.00
Camanche Grey 2 47 2017-08-24 8.00 3.00
Camanche Grey 10 38 2018-01-12 17.70 12.00
Camanche Grey 2 55 2018-07-19 27.28 -5.00
Camanche Grey 3 54 2018-07-30 6.78 -4.00
Camanche Grey 1 54 2018-08-21 24.00 0.00

I have a table. I want an output result with extra new column called "profit/loss".

Here are the conditions to make that column.

1. We need to find the first row that has finish_position is 1 (for this table it is in 6th row). So this row and above rows profit/loss is 0.00
2. We need to find the first row that has diff is 10 or greater than 10 (for this table it is in 18th row). Then we need to assign profit/loss is 10 for that row. Also above rows profit/loss is 0.00
3. Then each next rows profit/loss will increase by 10 until finish_position is 1 (for this table it is in 23rd row).
4. Finally we need to do a calculation in profit/loss column in 23rd row.
Calculation Formula = (bsp for that row * 10) - (profit/loss for that row)
example = (15.51 * 10) - (60)
= 155.1 - 60
= 95.1

So in that 23rd row profit/loss column has 95.1 value. You guys can get a clear idea from below table.

sf_name finish_position official_rating date bsp diff profit/loss
Camanche Grey 11 NULL 2013-09-18 72.53 0.00 0.00
Camanche Grey 4 NULL 2013-11-08 12.50 0.00 0.00
Camanche Grey 3 NULL 2014-02-27 24.00 0.00 0.00
Camanche Grey 6 65 2014-04-24 32.00 0.00 0.00
Camanche Grey 13 63 2014-05-05 109.06 0.00 0.00
Camanche Grey 1 59 2014-07-04 4.16 0.00 0.00
Camanche Grey 2 65 2014-08-04 5.10 -6.00 0.00
Camanche Grey 12 68 2014-10-28 55.00 -9.00 0.00
Camanche Grey 9 62 2014-12-09 110.89 -3.00 0.00
Camanche Grey 5 65 2015-01-08 61.07 -6.00 0.00
Camanche Grey 7 60 2015-01-29 12.54 -1.00 0.00
Camanche Grey 11 65 2015-04-08 15.50 -6.00 0.00
Camanche Grey 5 63 2015-04-22 36.07 -4.00 0.00
Camanche Grey 1 60 2015-07-14 4.25 0.00 0.00
Camanche Grey 9 60 2015-08-04 5.30 0.00 0.00
Camanche Grey 8 56 2016-01-22 22.67 4.00 0.00
Camanche Grey 4 52 2016-02-26 11.76 8.00 0.00
Camanche Grey 7 50 2016-03-31 4.30 10.00 10.00
Camanche Grey 8 48 2016-04-25 6.31 12.00 20.00
Camanche Grey 4 56 2016-07-12 9.50 4.00 30.00
Camanche Grey 8 47 2016-09-23 23.47 13.00 40.00
Camanche Grey 9 52 2017-06-20 114.54 8.00 50.00
Camanche Grey 1 50 2017-07-04 15.51 0.00 95.51
Camanche Grey 3 55 2017-07-20 5.29 -5.00 0.00
Camanche Grey 3 48 2017-07-29 15.51 2.00 0.00
Camanche Grey 2 47 2017-08-24 8.00 3.00 0.00
Camanche Grey 10 38 2018-01-12 17.70 12.00 10.00
Camanche Grey 2 55 2018-07-19 27.28 -5.00 20.00
Camanche Grey 3 54 2018-07-30 6.78 -4.00 30.00
Camanche Grey 1 54 2018-08-21 24.00 0.00 200.00

Another calculation happened from row 27th to row 30. Because 27th row diff is greater than 12.

Here is the fiddle link : https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f56a46b0771e02bed82dc9a6cfba0afc


Is there any Mysql select query to perform this tasks?