Snowflake SQL Tutorial 63 - AFTER MATCH SKIP in Snowflake - Same Pattern, Same 23 Rows, Three Different Answers

Snowflake SQL · Episode 63

In this post we are going to cover AFTER MATCH SKIP in Snowflake.

AFTER MATCH SKIP is one line inside MATCH_RECOGNIZE. It decides where the engine starts looking again once it has found a match. If we never write it, we get one particular answer, and most people never find out there were others.

We will run one pattern over one series three times, changing nothing but that line, and get three different match counts. Then we will ask the pattern what it missed, put RUNNING next to FINAL, do more with MEASURES, and finish with a worked funnel where MATCH_RECOGNIZE and CONDITIONAL_TRUE_EVENT disagree and both are right.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 63 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page
  1. Let's create some sample data
  2. Let's start with the default: SKIP PAST LAST ROW
  3. Now let's change one line: SKIP TO NEXT ROW
  4. Now let's skip to a named symbol with SKIP TO LAST B
  5. Now let's ask what the pattern missed
  6. Now let's put RUNNING and FINAL side by side
  7. Now let's do more with MEASURES
  8. Now let's try alternation and optional
  9. Let's build a funnel: a run of shipped orders, ended
  10. Can two answers disagree and both be right?

Let's create some sample data

This post picks up exactly where Episode 62 left off, on the same monthly revenue series: ORDERS grouped into months, in a CTE called M. Run the setup once.

Sample data — run once, then build M
-- ORDERS comes from the setup script this course uses throughout.
-- Run it once; every episode reuses the same tables.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

-- Every pattern on this page reads one monthly series, built in a CTE called M
-- so you can paste any query below straight into a worksheet.
WITH M AS (
    SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START,
           COUNT(*)                        AS N_ORDERS,
           SUM(AMOUNT)                     AS TOTAL
      FROM ORDERS
     GROUP BY 1
)
SELECT * FROM M ORDER BY MONTH_START;

Let's look at the series every pattern on this page reads.

Query — the input to every pattern below
SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START,
       COUNT(*) AS N_ORDERS, SUM(AMOUNT) AS TOTAL
FROM ORDERS GROUP BY 1 ORDER BY 1;
Result23 rows · 3 columns
MONTH_STARTN_ORDERSTOTAL
2023-01-0125090.50
2023-02-0127650.00
2023-03-0127330.75
2023-04-0129010.00
2023-05-0123980.25
2023-06-01210075.50
2023-07-0127170.00
2023-08-01212110.00
2023-09-0115540.00
2023-10-0128445.00
2023-11-01211380.00
2023-12-0128980.00
2024-01-01215110.00
2024-02-0118340.00
2024-03-0122930.00
2024-04-01212640.00
2024-05-01216875.00
2024-06-01211660.00
2024-07-0118820.00
2024-08-01215640.00
2024-09-0127010.00
2024-10-0119640.00
2024-11-011NULL

23 months. The pattern we will use for most of this page is the one Episode 62 finished on, PATTERN (A B+): any row, then one or more rows higher than the one before. A rising run.

Nothing about that pattern changes. The only thing that changes is one line, and the answer changes with it.

Let's start with the default: SKIP PAST LAST ROW

When MATCH_RECOGNIZE finds a match it has to decide where to carry on looking. AFTER MATCH SKIP PAST LAST ROW is the answer we get if we never write the clause at all. It resumes from the row after the match ended.

Let's write it out explicitly. It is worth doing once, so that the comparison coming up is honest. The three queries differ in that line and nowhere else.

Query — AFTER MATCH SKIP PAST LAST ROW
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
)
SELECT * FROM M
MATCH_RECOGNIZE (
  ORDER BY MONTH_START
  MEASURES MATCH_NUMBER() AS MATCH_NO,
           FIRST(MONTH_START) AS FROM_MONTH,
           LAST(MONTH_START)  AS TO_MONTH,
           COUNT(*)           AS MONTHS_IN_RUN
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (A B+)
  DEFINE B AS TOTAL > LAG(TOTAL)
)
ORDER BY MATCH_NO;
Result9 rows · 4 columns
MATCH_NOFROM_MONTHTO_MONTHMONTHS_IN_RUN
12023-01-012023-02-012
22023-03-012023-04-012
32023-05-012023-06-012
42023-07-012023-08-012
52023-09-012023-11-013
62023-12-012024-01-012
72024-03-012024-05-013
82024-07-012024-08-012
92024-09-012024-10-012

9 matches. Because the engine always resumes past the end of what it just found, no row can be in two matches and the matches cannot overlap.

Read FROM_MONTH and TO_MONTH down the grid and they march forward without ever doubling back.

Two of the nine runs are three months long rather than two: match 5 and match 7. Hold on to those two. They are the only reason the next thing we try gives a different number.

Now let's change one line: SKIP TO NEXT ROW

TO NEXT ROW means resume from the row after the match started, not after it ended. The engine goes back and tries again from inside the match it has just reported.

Let's change that one line and nothing else.

Query — AFTER MATCH SKIP TO NEXT ROW
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
)
SELECT * FROM M
MATCH_RECOGNIZE (
  ORDER BY MONTH_START
  MEASURES MATCH_NUMBER() AS MATCH_NO,
           FIRST(MONTH_START) AS FROM_MONTH,
           LAST(MONTH_START)  AS TO_MONTH,
           COUNT(*)           AS MONTHS_IN_RUN
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO NEXT ROW
  PATTERN (A B+)
  DEFINE B AS TOTAL > LAG(TOTAL)
)
ORDER BY MATCH_NO;
Result11 rows · 4 columns
MATCH_NOFROM_MONTHTO_MONTHMONTHS_IN_RUN
12023-01-012023-02-012
22023-03-012023-04-012
32023-05-012023-06-012
42023-07-012023-08-012
52023-09-012023-11-013
62023-10-012023-11-012
72023-12-012024-01-012
82024-03-012024-05-013
92024-04-012024-05-012
102024-07-012024-08-012
112024-09-012024-10-012

11 matches from the same pattern over the same months. And we can see exactly where the two extra ones came from. Line this grid up against the default one and every row is the same except two.

New matchIts spanSits insideThat parent's span
match 62023-10-01 → 2023-11-01match 52023-09-01 → 2023-11-01
match 92024-04-01 → 2024-05-01match 82024-03-01 → 2024-05-01

Match 6 is the tail end of match 5. Match 9 is the tail end of match 8. That is what overlapping means. The engine went back one row and found a shorter rising run inside a longer one.

Why only two extra, and not nine

Those two parents are precisely the two three-month runs we were told to hold on to in the default grid. That is not a coincidence: a two-month match has nothing inside it to find, so only a run longer than the pattern's minimum can contain a shorter one. The flatter your data, the less difference this mode makes — which is exactly why it is easy to change it and not notice.

Now let's skip to a named symbol with SKIP TO LAST B

The third family of modes resumes at a named symbol's row rather than at a fixed offset. There is SKIP TO LAST <symbol> and SKIP TO FIRST <symbol>.

Our pattern has a B, so SKIP TO LAST B means “carry on from the last row that matched B”. Let's run it.

Query — AFTER MATCH SKIP TO LAST B
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
)
SELECT * FROM M
MATCH_RECOGNIZE (
  ORDER BY MONTH_START
  MEASURES MATCH_NUMBER() AS MATCH_NO,
           FIRST(MONTH_START) AS FROM_MONTH,
           LAST(MONTH_START)  AS TO_MONTH,
           COUNT(*)           AS MONTHS_IN_RUN
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO LAST B
  PATTERN (A B+)
  DEFINE B AS TOTAL > LAG(TOTAL)
)
ORDER BY MATCH_NO;
Result9 rows · 4 columns
MATCH_NOFROM_MONTHTO_MONTHMONTHS_IN_RUN
12023-01-012023-02-012
22023-03-012023-04-012
32023-05-012023-06-012
42023-07-012023-08-012
52023-09-012023-11-013
62023-12-012024-01-012
72024-03-012024-05-013
82024-07-012024-08-012
92024-09-012024-10-012

9 matches, which is the same answer as the default, row for row.

Read this before you copy that clause

That tie is a fact about this pattern, not a rule about these two modes. B is the last symbol in PATTERN (A B+), so “the last B” is the last row of the match and the two modes land in the same place. Put a symbol after B+ in the pattern and they come apart immediately. Never read “same count” as “same behaviour”.

Let's put all three modes in one statement, so there is nothing to take on trust.

Query — the three counts side by side
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
)
SELECT
  (SELECT COUNT(*) FROM M MATCH_RECOGNIZE (
     ORDER BY MONTH_START
     MEASURES MATCH_NUMBER() AS MATCH_NO
     ONE ROW PER MATCH
     AFTER MATCH SKIP PAST LAST ROW
     PATTERN (A B+) DEFINE B AS TOTAL > LAG(TOTAL))) AS SKIP_PAST_LAST_ROW,
  (SELECT COUNT(*) FROM M MATCH_RECOGNIZE (
     ORDER BY MONTH_START
     MEASURES MATCH_NUMBER() AS MATCH_NO
     ONE ROW PER MATCH
     AFTER MATCH SKIP TO NEXT ROW
     PATTERN (A B+) DEFINE B AS TOTAL > LAG(TOTAL))) AS SKIP_TO_NEXT_ROW,
  (SELECT COUNT(*) FROM M MATCH_RECOGNIZE (
     ORDER BY MONTH_START
     MEASURES MATCH_NUMBER() AS MATCH_NO
     ONE ROW PER MATCH
     AFTER MATCH SKIP TO LAST B
     PATTERN (A B+) DEFINE B AS TOTAL > LAG(TOTAL))) AS SKIP_TO_LAST_B,
  (SELECT COUNT(*) FROM M) AS MONTHS;
Result1 row · 4 columns
SKIP_PAST_LAST_ROWSKIP_TO_NEXT_ROWSKIP_TO_LAST_BMONTHS
911923

9, 11, 9 out of 23 months. One pattern, one series, three answers. That row is the whole lesson of the first half of this post.

Skip modeResumes fromCan matches overlap?
PAST LAST ROW (default)the row after the match endedNo
TO NEXT ROWthe row after the match startedYes
TO LAST <symbol>the last row that matched that symbolYes, unless that symbol ends the match
TO FIRST <symbol>the first row that matched that symbolYes

Now let's ask what the pattern missed

Every grid so far shows what matched. The more useful question when we are writing a pattern is what did not.

ALL ROWS PER MATCH WITH UNMATCHED ROWS returns every input row, matched or not.

Query — ALL ROWS PER MATCH WITH UNMATCHED ROWS
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
)
SELECT * FROM M
MATCH_RECOGNIZE (
  ORDER BY MONTH_START
  MEASURES MATCH_NUMBER() AS MATCH_NO, CLASSIFIER() AS SYMBOL
  ALL ROWS PER MATCH WITH UNMATCHED ROWS
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (A B+)
  DEFINE B AS TOTAL > LAG(TOTAL)
)
ORDER BY MONTH_START;
Result23 rows · 4 columns
MONTH_STARTTOTALMATCH_NOSYMBOL
2023-01-015090.501A
2023-02-017650.001B
2023-03-017330.752A
2023-04-019010.002B
2023-05-013980.253A
2023-06-0110075.503B
2023-07-017170.004A
2023-08-0112110.004B
2023-09-015540.005A
2023-10-018445.005B
2023-11-0111380.005B
2023-12-018980.006A
2024-01-0115110.006B
2024-02-018340.00NULLNULL
2024-03-012930.007A
2024-04-0112640.007B
2024-05-0116875.007B
2024-06-0111660.00NULLNULL
2024-07-018820.008A
2024-08-0115640.008B
2024-09-017010.009A
2024-10-019640.009B
2024-11-01NULLNULLNULL

All 23 months are back, in order. The rows that took part in a match carry a MATCH_NO and a SYMBOL. The rows that matched nothing carry NULL in both.

Let's count that.

Query — the same thing, counted
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
), R AS (
  SELECT * FROM M
  MATCH_RECOGNIZE (
    ORDER BY MONTH_START
    MEASURES MATCH_NUMBER() AS MATCH_NO
    ALL ROWS PER MATCH WITH UNMATCHED ROWS
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B+)
    DEFINE B AS TOTAL > LAG(TOTAL))
)
SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(MATCH_NO) AS ROWS_IN_A_MATCH,
       COUNT(*) - COUNT(MATCH_NO) AS ROWS_IN_NO_MATCH
FROM R;
Result1 row · 3 columns
ROWS_TOTALROWS_IN_A_MATCHROWS_IN_NO_MATCH
23203

23 rows in, 20 of them inside some match, 3 in no match at all. That is the same 20 and 3 Episode 62 arrived at from the opposite direction, by counting member rows.

The three are the months carrying NULL in the WITH UNMATCHED ROWS grid, and that grid also shows why each one is out.

  • February 2024 and June 2024 — each is followed by a month that fell, so neither can start a climb. An A needs at least one B after it to complete PATTERN (A B+).
  • November 2024 — the last month in the series, and its TOTAL is NULL, so it can neither rise nor be risen past.
The habit worth taking from this episode

This is how you check a pattern — by asking what it missed, not by reading it. A pattern that quietly skips a tenth of your rows looks exactly like a pattern that works, right up until someone reconciles a total. WITH UNMATCHED ROWS costs you one clause and tells you straight away.

Now let's put RUNNING and FINAL side by side

A measure like COUNT(*) inside MEASURES has two possible meanings, and there are two keywords that choose between them.

KeywordMeansDefault for
RUNNINGso far in this match, up to and including the current rowALL ROWS PER MATCH
FINALthe value for the whole match, on every row of it—

With ONE ROW PER MATCH the distinction is invisible, because the only row we get is the last one. With ALL ROWS PER MATCH we can put both on the same grid and watch them diverge.

Query — RUNNING and FINAL side by side
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
)
SELECT * FROM M
MATCH_RECOGNIZE (
  ORDER BY MONTH_START
  MEASURES MATCH_NUMBER()          AS MATCH_NO,
           CLASSIFIER()            AS SYMBOL,
           RUNNING COUNT(*)        AS RUNNING_ROWS,
           FINAL   COUNT(*)        AS FINAL_ROWS,
           RUNNING LAST(TOTAL)     AS RUNNING_LAST_TOTAL,
           FINAL   LAST(TOTAL)     AS FINAL_LAST_TOTAL
  ALL ROWS PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (A B+)
  DEFINE B AS TOTAL > LAG(TOTAL)
)
ORDER BY MATCH_NO, MONTH_START;
Result20 rows · 8 columns · scroll →
MONTH_STARTTOTALMATCH_NOSYMBOLRUNNING_ROWSFINAL_ROWSRUNNING_LAST_TOTALFINAL_LAST_TOTAL
2023-01-015090.501A125090.507650.00
2023-02-017650.001B227650.007650.00
2023-03-017330.752A127330.759010.00
2023-04-019010.002B229010.009010.00
2023-05-013980.253A123980.2510075.50
2023-06-0110075.503B2210075.5010075.50
2023-07-017170.004A127170.0012110.00
2023-08-0112110.004B2212110.0012110.00
2023-09-015540.005A135540.0011380.00
2023-10-018445.005B238445.0011380.00
2023-11-0111380.005B3311380.0011380.00
2023-12-018980.006A128980.0015110.00
2024-01-0115110.006B2215110.0015110.00
2024-03-012930.007A132930.0016875.00
2024-04-0112640.007B2312640.0016875.00
2024-05-0116875.007B3316875.0016875.00
2024-07-018820.008A128820.0015640.00
2024-08-0115640.008B2215640.0015640.00
2024-09-017010.009A127010.009640.00
2024-10-019640.009B229640.009640.00

Look at match 1, which is two months long. RUNNING_ROWS goes 1 then 2. FINAL_ROWS says 2 on both of its rows, including the one where the second month has not been seen yet.

Now look at match 5, which is three months long. RUNNING_ROWS climbs 1, 2, 3 while FINAL_ROWS reads 3 all the way down.

The same split runs through RUNNING LAST(TOTAL) and FINAL LAST(TOTAL). The running one is whatever the latest row of the match is. The final one is the total the run ended on.

If we want a running number, we say RUNNING. If we want the answer for the whole match on every row, we say FINAL.

This grid has 20 rows, the same 20 that were in a match when we asked WITH UNMATCHED ROWS.

Now let's do more with MEASURES

MEASURES is not limited to MATCH_NUMBER() and CLASSIFIER(). Ordinary aggregates work across the rows of a match, and FIRST and LAST let us describe the shape of a run in one row.

Query — SUM, AVG, MIN, MAX and a CLIMB column
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
)
SELECT * FROM M
MATCH_RECOGNIZE (
  ORDER BY MONTH_START
  MEASURES MATCH_NUMBER()     AS MATCH_NO,
           COUNT(*)           AS MONTHS,
           SUM(TOTAL)         AS REVENUE_IN_RUN,
           AVG(TOTAL)         AS AVG_MONTH,
           MIN(TOTAL)         AS SMALLEST_MONTH,
           MAX(TOTAL)         AS BIGGEST_MONTH,
           LAST(TOTAL) - FIRST(TOTAL) AS CLIMB
  ONE ROW PER MATCH
  PATTERN (A B+)
  DEFINE B AS TOTAL > LAG(TOTAL)
)
ORDER BY MATCH_NO;
Result9 rows · 7 columns · scroll →
MATCH_NOMONTHSREVENUE_IN_RUNAVG_MONTHSMALLEST_MONTHBIGGEST_MONTHCLIMB
1212740.506370.250005090.507650.002559.50
2216340.758170.375007330.759010.001679.25
3214055.757027.875003980.2510075.506095.25
4219280.009640.000007170.0012110.004940.00
5325365.008455.000005540.0011380.005840.00
6224090.0012045.000008980.0015110.006130.00
7332445.0010815.000002930.0016875.0013945.00
8224460.0012230.000008820.0015640.006820.00
9216650.008325.000007010.009640.002630.00

One row per rising run, with the revenue inside it, the average month, the smallest and biggest months, and LAST(TOTAL) - FIRST(TOTAL) as a CLIMB column. That last one is how much the series gained over the run.

Match 7 has the biggest CLIMB of the nine, and it is the three-month run starting in March 2024.

Counting rows per symbol

COUNT(<symbol>.*) counts only the rows of the match that matched that symbol. On a V-shape pattern that tells us how much of each match was the fall and how much was the recovery, without looking at the member rows at all.

Query — COUNT(DOWN.*) and COUNT(UP.*)
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
)
SELECT * FROM M
MATCH_RECOGNIZE (
  ORDER BY MONTH_START
  MEASURES MATCH_NUMBER() AS MATCH_NO,
           COUNT(*)       AS ROWS_IN_MATCH,
           COUNT(DOWN.*)  AS DOWN_ROWS,
           COUNT(UP.*)    AS UP_ROWS,
           FIRST(MONTH_START) AS FROM_MONTH,
           LAST(MONTH_START)  AS TO_MONTH
  ONE ROW PER MATCH
  PATTERN (S DOWN+ UP+)
  DEFINE DOWN AS TOTAL < LAG(TOTAL),
         UP   AS TOTAL > LAG(TOTAL)
)
ORDER BY MATCH_NO;
Result5 rows · 6 columns · scroll →
MATCH_NOROWS_IN_MATCHDOWN_ROWSUP_ROWSFROM_MONTHTO_MONTH
13112023-02-012023-04-01
23112023-06-012023-08-01
33112023-11-012024-01-01
44122024-02-012024-05-01
53112024-06-012024-08-01

5 V-shapes. DOWN_ROWS is 1 in every one of them, so each match fell for exactly one month. Match 4 is the outlier on the other side, with UP_ROWS 2 and ROWS_IN_MATCH 4, because it recovered over two months instead of one.

Where the extra row in each match comes from

S is the starting row and is defined by nothing, so it matches anything — the ROWS_IN_MATCH figure counts it along with the DOWN and UP rows. That is why DOWN_ROWS plus UP_ROWS is one short of ROWS_IN_MATCH on every row of this grid.

Now let's try alternation and optional

Two more pieces of pattern syntax are coming up. But first a word about picking thresholds, because this is where demos go wrong. A pattern that matches nothing teaches nothing. Ask for month-on-month moves above 50% on a series that barely reaches that and we get an empty grid and no lesson.

So let's look at the actual month-on-month ratios first.

Query — every month against the one before it
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
)
SELECT MONTH_START, TOTAL,
       LAG(TOTAL) OVER (ORDER BY MONTH_START) AS PREV_TOTAL,
       ROUND(TOTAL / NULLIF(LAG(TOTAL) OVER (ORDER BY MONTH_START), 0), 3)
         AS RATIO_TO_PREV
FROM M
ORDER BY MONTH_START;
Result23 rows · 4 columns
MONTH_STARTTOTALPREV_TOTALRATIO_TO_PREV
2023-01-015090.50NULLNULL
2023-02-017650.005090.501.503
2023-03-017330.757650.000.958
2023-04-019010.007330.751.229
2023-05-013980.259010.000.442
2023-06-0110075.503980.252.531
2023-07-017170.0010075.500.712
2023-08-0112110.007170.001.689
2023-09-015540.0012110.000.457
2023-10-018445.005540.001.524
2023-11-0111380.008445.001.348
2023-12-018980.0011380.000.789
2024-01-0115110.008980.001.683
2024-02-018340.0015110.000.552
2024-03-012930.008340.000.351
2024-04-0112640.002930.004.314
2024-05-0116875.0012640.001.335
2024-06-0111660.0016875.000.691
2024-07-018820.0011660.000.756
2024-08-0115640.008820.001.773
2024-09-017010.0015640.000.448
2024-10-019640.007010.001.375
2024-11-01NULL9640.00NULL

Now let's pull the extremes out and count how many months two candidate thresholds would catch.

Query — the extremes, and two candidate thresholds
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
), R AS (
  SELECT TOTAL / NULLIF(LAG(TOTAL) OVER (ORDER BY MONTH_START), 0) AS RATIO
  FROM M
)
SELECT ROUND(MIN(RATIO), 3) AS SMALLEST_RATIO,
       ROUND(MAX(RATIO), 3) AS BIGGEST_RATIO,
       COUNT_IF(RATIO > 1.25) AS MONTHS_UP_OVER_25_PCT,
       COUNT_IF(RATIO < 0.80) AS MONTHS_DOWN_OVER_20_PCT
FROM R;
Result1 row · 4 columns
SMALLEST_RATIOBIGGEST_RATIOMONTHS_UP_OVER_25_PCTMONTHS_DOWN_OVER_20_PCT
0.3514.314109

The smallest ratio in the series is 0.351 and the biggest is 4.314. At a threshold of 1.25, 10 months qualify as a big rise. At 0.80, 9 qualify as a big fall.

Those are thresholds this data can actually show, which is why the patterns below use them.

Alternation: this symbol or that one

A pipe inside PATTERN means “either”, exactly as in a text regular expression. PATTERN (S (BIGUP | BIGDOWN)) is “any row, then either a big rise or a big fall”.

Query — PATTERN (S (BIGUP | BIGDOWN))
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
)
SELECT * FROM M
MATCH_RECOGNIZE (
  ORDER BY MONTH_START
  MEASURES MATCH_NUMBER() AS MATCH_NO, CLASSIFIER() AS SYMBOL,
           FIRST(MONTH_START) AS FROM_MONTH, LAST(MONTH_START) AS TO_MONTH
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (S (BIGUP | BIGDOWN))
  DEFINE BIGUP   AS TOTAL > LAG(TOTAL) * 1.25,
         BIGDOWN AS TOTAL < LAG(TOTAL) * 0.80
)
ORDER BY MATCH_NO;
Result10 rows · 4 columns
MATCH_NOSYMBOLFROM_MONTHTO_MONTH
1BIGUP2023-01-012023-02-01
2BIGDOWN2023-04-012023-05-01
3BIGDOWN2023-06-012023-07-01
4BIGDOWN2023-08-012023-09-01
5BIGUP2023-10-012023-11-01
6BIGUP2023-12-012024-01-01
7BIGDOWN2024-02-012024-03-01
8BIGUP2024-04-012024-05-01
9BIGDOWN2024-06-012024-07-01
10BIGDOWN2024-08-012024-09-01

10 matches, and CLASSIFIER() earns its keep here. It names which branch of the alternation each match took. Count the SYMBOL column and four of the ten are BIGUP and six are BIGDOWN.

That is fewer matches than the thresholds counted, and the reason is the skip mode. This pattern uses the default, so each match consumes the rows it used and the search starts again after them. A qualifying month whose predecessor has already been taken never gets to start a match of its own.

Optional: a step that may or may not be there

A question mark makes a symbol optional. PATTERN (A B? C) is “a row, then possibly a rise, then a fall”. The rise is allowed to be missing.

Query — PATTERN (A B? C)
WITH M AS (
  SELECT DATE_TRUNC('MONTH', ORDER_DATE) AS MONTH_START, SUM(AMOUNT) AS TOTAL
  FROM ORDERS GROUP BY 1
)
SELECT COUNT(*) AS MATCHES FROM M
MATCH_RECOGNIZE (
  ORDER BY MONTH_START
  MEASURES MATCH_NUMBER() AS MATCH_NO
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (A B? C)
  DEFINE B AS TOTAL > LAG(TOTAL),
         C AS TOTAL < LAG(TOTAL)
);
Result1 row · 1 column
MATCHES
8

8 matches. This is the quantifier that makes patterns tolerant of real data, where the middle step of a sequence is often simply absent.

Let's build a funnel: a run of shipped orders, ended

Now something we would actually be asked for. Take each customer's orders in date order and find every run of SHIPPED orders that was ended by an order that was not shipped.

That is PATTERN (SHIPPED+ BROKE), with BROKE defined as any status other than SHIPPED. We add PARTITION BY CUSTOMER_ID so a run never crosses from one customer to another.

Query — PATTERN (SHIPPED+ BROKE)
SELECT * FROM ORDERS
MATCH_RECOGNIZE (
  PARTITION BY CUSTOMER_ID
  ORDER BY ORDER_DATE, ORDER_ID
  MEASURES MATCH_NUMBER()    AS MATCH_NO,
           COUNT(SHIPPED.*)  AS SHIPPED_IN_A_ROW,
           FIRST(ORDER_DATE) AS RUN_STARTED,
           LAST(ORDER_DATE)  AS BROKE_ON,
           LAST(STATUS)      AS BROKE_WITH
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (SHIPPED+ BROKE)
  DEFINE SHIPPED AS STATUS = 'SHIPPED',
         BROKE   AS STATUS <> 'SHIPPED'
)
ORDER BY CUSTOMER_ID, MATCH_NO;
Result5 rows · 6 columns · scroll →
CUSTOMER_IDMATCH_NOSHIPPED_IN_A_ROWRUN_STARTEDBROKE_ONBROKE_WITH
102122023-01-282023-07-29RETURNED
102222023-11-242024-09-29RETURNED
104122023-03-092023-12-09CANCELLED
105142023-04-052024-06-13PENDING
106122023-10-032024-11-05PENDING

5 runs. Customer 105 is the clearest one: four shipped orders in a row, then a PENDING order stopped it.

LAST(STATUS) as BROKE_WITH tells us what broke each run, which is usually the column the question was really about.

Why BROKE

The symbol is called BROKE and not BREAK on purpose — BREAK is a keyword in Snowflake, and a pattern symbol that collides with a keyword is not a problem worth discovering under time pressure. Episode 62 made the same point about START.

COUNT(<symbol>.*) has a sibling, so we can total only the rows that matched one symbol. Let's add up the money.

Query — SUM(SHIPPED.AMOUNT)
SELECT * FROM ORDERS
MATCH_RECOGNIZE (
  PARTITION BY CUSTOMER_ID
  ORDER BY ORDER_DATE, ORDER_ID
  MEASURES MATCH_NUMBER()      AS MATCH_NO,
           COUNT(SHIPPED.*)    AS SHIPPED_IN_A_ROW,
           SUM(SHIPPED.AMOUNT) AS REVENUE_IN_RUN
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (SHIPPED+ BROKE)
  DEFINE SHIPPED AS STATUS = 'SHIPPED',
         BROKE   AS STATUS <> 'SHIPPED'
)
ORDER BY CUSTOMER_ID, MATCH_NO;
Result5 rows · 4 columns
CUSTOMER_IDMATCH_NOSHIPPED_IN_A_ROWREVENUE_IN_RUN
102122010.50
102222220.00
104124286.25
1051429665.00
106123120.00

SUM(SHIPPED.AMOUNT) adds up the amounts of the SHIPPED rows only and leaves the BROKE row out of the figure.

Symbol-qualified aggregates are the part of MEASURES that makes this clause worth learning. The alternative is a CASE expression inside a SUM and a grouping key we have to build ourselves.

Can two answers disagree and both be right?

Episode 57 answered a very similar question with CONDITIONAL_TRUE_EVENT, which numbers runs by counting how many times a condition has been true so far. Same data, same intent, no pattern matching at all.

Let's run it and compare.

Query — the same question with CONDITIONAL_TRUE_EVENT
WITH R AS (
  SELECT CUSTOMER_ID, ORDER_DATE, STATUS, AMOUNT,
         CONDITIONAL_TRUE_EVENT(STATUS <> 'SHIPPED')
           OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE, ORDER_ID) AS RUN_ID
  FROM ORDERS
)
SELECT CUSTOMER_ID, RUN_ID,
       COUNT_IF(STATUS = 'SHIPPED') AS SHIPPED_IN_A_ROW,
       SUM(CASE WHEN STATUS = 'SHIPPED' THEN AMOUNT END) AS REVENUE_IN_RUN
FROM R
GROUP BY CUSTOMER_ID, RUN_ID
HAVING COUNT_IF(STATUS = 'SHIPPED') > 0
ORDER BY CUSTOMER_ID, RUN_ID;
Result10 rows · 4 columns
CUSTOMER_IDRUN_IDSHIPPED_IN_A_ROWREVENUE_IN_RUN
1010961660.00
102022010.50
102122220.00
1031414135.25
104024286.25
104112540.00
1050429665.00
106123120.00
1070556800.00
1090315870.00

10 rows. Not 5. And this is the most useful thing on the page, because neither query is wrong.

Look at what the two grids share. All five MATCH_RECOGNIZE runs are in this grid too, with the same customers, the same shipped counts and the same amounts: customer 102 twice, then 104, 105 and 106.

The five rows that are only here are runs that never ended.

Customer 101 is the plainest example. It has nine shipped orders and nothing after them. PATTERN (SHIPPED+ BROKE) requires a BROKE row to complete the match, so there is no match. CONDITIONAL_TRUE_EVENT counts the run anyway, because all it is doing is numbering.

MATCH_RECOGNIZECONDITIONAL_TRUE_EVENT
What it answersRuns that were ended by a non-shipped orderEvery run of shipped orders, ended or not
A run still open at the end of the dataNot reportedReported
Rows returned here510

Which one we want depends entirely on whether a run that has not finished yet counts. For “how often does a customer's streak get broken, and by what?” the pattern is the right tool. For “how long are our shipped streaks?” the window function is, because the open ones are the interesting ones.

One last check on the funnel

Let's count the statuses in ORDERS, and then the orders, the matches and the orders that could have broken a run.

Query — the statuses in ORDERS
SELECT STATUS, COUNT(*) AS N FROM ORDERS GROUP BY STATUS ORDER BY N DESC;
Result4 rows · 2 columns
STATUSN
SHIPPED34
PENDING3
CANCELLED2
RETURNED2
Query — orders, matches, and candidate breakers
SELECT
  (SELECT COUNT(*) FROM ORDERS) AS ORDERS_TOTAL,
  (SELECT COUNT(*) FROM ORDERS MATCH_RECOGNIZE (
     PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE, ORDER_ID
     ONE ROW PER MATCH AFTER MATCH SKIP PAST LAST ROW
     PATTERN (SHIPPED+ BROKE)
     DEFINE SHIPPED AS STATUS = 'SHIPPED',
            BROKE   AS STATUS <> 'SHIPPED')) AS FUNNEL_MATCHES,
  (SELECT COUNT(*) FROM ORDERS WHERE STATUS <> 'SHIPPED') AS NON_SHIPPED_ORDERS;
Result1 row · 3 columns
ORDERS_TOTALFUNNEL_MATCHESNON_SHIPPED_ORDERS
4157

41 orders in all, 5 funnel matches, and 7 orders whose status is not SHIPPED. The three non-shipped statuses in the status grid add up to exactly that.

So seven orders could have broken a run and only five did. The other two had no shipped order in front of them in their customer's sequence, and a BROKE row with no SHIPPED+ before it completes nothing.

That is the check worth copying. Count the rows that could have matched, count the ones that did, and make yourself explain the gap. It is either a fact about your data or a bug in your pattern, and you want to know which.

And that closes Level 6

Level 6 was about reshaping and sampling: PIVOT and UNPIVOT for turning rows into columns and back, SAMPLE and TABLESAMPLE for working on part of a table, and MATCH_RECOGNIZE for finding shapes in ordered rows.

MATCH_RECOGNIZE is the one to go back to. Most of what it does can be reproduced with window functions and enough patience. What it gives us instead is a single clause that names the shape we are looking for, and, with WITH UNMATCHED ROWS, tells us honestly what it did not find.

Key points

  • AFTER MATCH SKIP decides where the engine resumes after a match. It is not cosmetic: the same pattern over the same 23 months returned 9, 11 and 9 matches under three different modes.
  • SKIP PAST LAST ROW is the default and resumes after the match ended, so matches can never overlap.
  • SKIP TO NEXT ROW resumes after the match started, so the engine can find a shorter run inside a longer one — which is exactly where the two extra matches here came from.
  • SKIP TO LAST B gave the same count as the default on this pattern because B is its last symbol. That is a fact about the pattern, not a rule about the modes.
  • ALL ROWS PER MATCH WITH UNMATCHED ROWS returns every input row with a NULL match number where nothing matched. Check a pattern by asking what it missed.
  • RUNNING is “so far in this match”; FINAL is the whole match's answer repeated on every row. The difference only shows up under ALL ROWS PER MATCH.
  • Aggregates work inside MEASURES, and they can be qualified by symbol: COUNT(DOWN.*) and SUM(SHIPPED.AMOUNT) read only the rows that matched that symbol.
  • | is alternation and ? makes a symbol optional, and CLASSIFIER() tells you which branch of an alternation a match took.
  • Pick thresholds your data can demonstrate. Looking at the ratios first is why this episode's pattern uses 1.25 and 0.80 rather than a round number that matches nothing.
  • MATCH_RECOGNIZE found 5 shipped runs and CONDITIONAL_TRUE_EVENT found 10 on the same data. The pattern requires a run to be ended; the window function counts open runs too. Decide which question you are asking.
Next in this series

Episode 64 — Level 7 begins: functions. Strings, numbers, dates and conversion, starting with CONCAT, the || operator and LENGTH.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. The ORDERS data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series — the comparison between MATCH_RECOGNIZE and CONDITIONAL_TRUE_EVENT here is about which question each one answers, not about speed. Clause behaviour follows the Snowflake MATCH_RECOGNIZE documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)