Calculating YOY growth rates on selected records

Find out the three trading days with the highest stock prices, as well as their closing prices and growth rates.

SQL

SELECT *
FROM (SELECT trade_date,close_price,
             close_price/lag(close_price)
             OVER(ORDER BY trade_date)-1
             rising_range
      FROM stock_price)
WHERE trade_date IN
     (SELECT trade_date
      FROM (SELECT trade_date,
                   row_number() OVER(ORDER BY close_price desc)
                   stock_rank FROM stock_price)
      WHERE stock_rank<=3)

The users can use window functions to conduct a comparison between the current period and the corresponding previous period and rank the data conveniently. However, for filtering, the users have to calculate the YOY growth rates for all records beforehand, and the subquery would be necessary in this process.

SPL

A
1 =demo.query("select * from stock_price").sort(trade_date)
2 =A1.psort(close_price:-1)([1,2,3]) The three trading days with the highest stock price.
3 =A1.calc(A2, close_price/close_price[-1]-1) Compute the increasing in these three days.
4 =A1(A2).new(trade_date,close_price,A3(#):rising_range)

SPL is very good at computations related with the order and position. The code is very simple and conforms to user's natural logical 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.