Getting consecutive intervals by comparing to the previous day

Find out the longest run of consecutive days of gains in the price for a stock.

SQL

SELECT max(consecutive_day)
FROM (SELECT count(*) consecutive_day FROM
           (SELECT sum(rise_or_fall) OVER(ORDER BY trade_date)
                   day_no_gain FROM
                   (SELECT trade_date, CASE
                          when close_price>lag(close_price)
                          OVER(ORDER BY trade_date)
                          then 0 else 1 end rise_or_fall
                   FROM stock_price) )
GROUP BY day_no_gain)

SQL syntax cannot handle the complex order-related computation modeled in the natural logical mental process. Therefore, a shift of mind is required. The above syntax computes the cumulative days with no price gains from the initial date, the trade dates with the same number of no-gain days indicate the consecutive days of gains, thus grouping records by this same number can get the longest run of consecutive days with gains. This theory is already so difficult to understand, not to mention to code them.

SPL

A
1 =demo.query("select * from stock_price").sort(trade_date)
2 =0 A temporary variable for storing the number of consecutive days of gains.
3 =A1.max(A2=if(close_price>close_price[-1],A2+1,0)) Compare with the previous day to determine if A2 will be increased.

SPL allows the user to write code by following their natural mental process.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.