How to find stock turning price points

Question

Let me know how to find turning price points in stock data in SQL. for example we have following data columns and N rows:

Date     |Price|
20150101 | 100  |
20150102 | 50   |
20150103 | 80   |
     .
     .
     .
201708027 | 200  |

and I want to find the turning points (date and price) , to understand clearly check following image.please help me how to find red points.

Answer 1

Using SELF JOIN:

SELECT cur.*
FROM tab cur
JOIN tab prev
  ON cur.date = prev.date + 1
JOIN tab next
  ON cur.date = next.date - 1
WHERE cur.price > prev.price AND cur.price > next.price;   -- peak
-- WHERE cur.price < prev.price AND cur.price < next.price; -- bottom

Using LEAD and LAG will be much more readable but they are available from SQL Server 2012+.