how to detect peak of two columns in sql

Question

I have two columns in mysql:

row  A    B
1    90   80 
2    80   57
3    57   5
4    48   30
5    30   15

I need to compare the value of B and the next value of A, how could I detect a peak when B is 5 (row 3) and A is 48 (row 4)? New column can be added to say whether a peak is detected.

The result should be:

row  A    B     peak_detection
1    90   80     0
2    80   57     0
3    57   5      0
4    48   30     1
5    30   15     0

Thank you

Answer 1

In steps:

1. How big is the difference:

SELECT
   A,
   B,
   LAG(A) over (order by r)-B  difference
FROM Table1

2. Select the max row:

SELECT r
FROM (
   SELECT
      r,
      A,
      B,
      LAG(A) over (order by r)-B  difference
   FROM Table1
) t2
ORDER BY difference DESC
LIMIT 1

3. Add the column peak_detection:

SELECT 
   A,
   B,
   IF(Table1.r=t2.r,1,0) peak_detection   
FROM Table1
LEFT JOIN (
   SELECT r
   FROM (
      SELECT
         r,
         A,
         B,
         LAG(A) over (order by r)-B  difference
      FROM Table1
   ) t2
   ORDER BY difference DESC
   LIMIT 1
) t2 on t2.r=Table1.r

See: dbfiddle

output:

A	B	peak_detection
90	80	0
80	57	0
57	5	1
48	30	0
30	15	0

P.S. code improvement can be done (and might be needed) on the last query, if needed.