SQLite where condition - compare two last rows

Question

I have a SQLite DB table like this:

[Stock_price_Table]:

+-----+-------------+--------+------------+--------+--------------+----------------------+
|code |    date     | first  |    open    | close  |    sma_9     | sma_26     | rsi     |
+-----+-------------+--------+------------+--------+--------------+----------------------+
|1223 | 20200213    | 205    | 205        | 300    | 225          | 300        | 20      | 
|1223 | 20200313    | 209    | 209        | 340    | 235          | 330        | 70      | 
|4556 | 20200213    | 205    | 205        | 300    | 225          | 300        | 67      | 
|4443 | 20200213    | 205    | 205        | 300    | 225          | 300        | 33      | 
|9877 | 20200218    | 205    | 205        | 300    | 225          | 300        | 39      |    +-----+-------------+--------+------------+--------+--------------+------------+---------+

I want to compare last two records of each stock and get the stocks that have these conditions:

1. `sma_9[n] >= sma_26[n] && sma_9[n-1] <= sma_26[n-1]`

2. `rsi[n] >= rsi[n-1]`

where [n] is last record and [n-1] the next-to-last.

I can do this in python and dataframe like below, but I want to do it using SQL code:

    stocks = db.session.query(Stocks).all()

    for stock in enumerate(stocks):
       query = f"select * from Stock_price_Table where code = {stock.code}"

       df = pd.read_sql(query, db.engine)

       result1=((df.iloc[-1]['SMA_9'] >= df.iloc[-1]['SMA_26']) & (df.iloc[-2]['SMA_9'] <= df.iloc[-2]['SMA_26']))

       result2=(df.iloc[-1]['rsi'] >= df.iloc[-2]['rsi']) 

Answer 1

Something like this should work (it's hard to verify without a fiddle):

WITH temp AS (
  SELECT code, sma_9, sma_26, rsi,
    RANK() OVER (PARTITION BY code ORDER BY date DESC) AS rank
  FROM Stock_price_Table
)
SELECT t1.code
  FROM temp AS t1
  INNER JOIN temp AS t2
    ON t1.code = t2.code
   AND t1.rank = 1
   AND t2.rank = 2
  WHERE t1.sma_9 >= t1.sma_26
    AND t2.sma_9 <= t2.sma_26
    AND t1.rsi >= t2.rsi

Basically, in the temp table you select the stock records, RANKed by date and 'grouped' by code (through the PARTITION BY clause). In the main SELECT, you link the first and second ranked records per stock code, and select only those which match your criteria (the WHERE clause).