Snowflake MATCH_RECOGNIZE for finding the Stock with the longest streak of increasing stock price


For a Applied Time Series Analysis course project, we had to build forecasting model for stocks that exhibit increase in the stock price for several days in a row. More specifically, in building the forecasting models we had to use Stocks that exhibit the longest streak of increasing stock price day over day. Since I had to use the current daily data, identifying stocks that exhibit this behavior was rather challenging.

There no published list of Stocks that exhibit this behavior.

I had access to Stock data from Zepl in our class Snowflake account. But how do I identify Stocks with the longest streak of prices increases, day over day?

Initially I was thinking of using Knime to accomplish this. Then it struck me why not use MATCH_RECOGNIZE in Snowflake for this purpose.

Enter MATCH_RECOGNIZE

I use MATCH_RECOGNIZE frequently at work to identify rows that match a certain pattern.

Problem of finding the Stocks with the longest streak of increasing closing price was not exactly what I use MATCH_RECOGNIZE for at work. I had to come up with a PATTERN that would match increasing stock price. I used the LAG function for this. Here is the SQL that I came up with

select symbol, days_of_increasing_stock_price, STREAK_START_DATE, STREAK_END_DATE
from US_STOCKS_DAILY.PUBLIC.STOCK_HISTORY
match_recognize(
    partition by symbol
    order by DATE
    measures
        first(DATE) as STREAK_START_DATE,
        last(DATE) as STREAK_END_DATE,
        count(*) as days_of_increasing_stock_price
    ONE ROW PER MATCH
    PATTERN (INCREASE+)
    DEFINE
          INCREASE as close > LAG(close)
)
order by days_of_increasing_stock_price DESC;

Note that the PATTERN that we are trying to match in the INCREASE, and INCREASE is defined as the BOOLEANTRUE if today’s Closing Stock price was greater than yesterday Closing price. INCREASE+ REGEX pattern indicates one or more Increases in the Stock price.

Use of MATCH_RECOGNIZE to find the Stocks with longest streak of increasing closing price.

This query worked really well. If you are curious, it was OPER Stock that exhibited the longest streak of increasing closing price for at total of 48 days in a row (at the time of this writing).

Longest streak of increasing closing Stock Price

For what would have taken a complex workflow in Knime to achieve, Snowflake’s powerful MATCH_RECOGNIZE was able to perform in a single concise query. Go Snowflake!