Gowtham Potureddi

Posted on Jul 11

SQL MATCH_RECOGNIZE: Pattern Recognition for Sessions, Funnels & Anomalies

#python #sql #dataengineering #interview

sql match_recognize is the single most under-taught pattern in warehouse SQL — and the single largest edge a senior data engineer can pull out of the ANSI SQL/2016 standard on a whiteboard. The same "detect an ordered sequence of rows that satisfies a regex-like pattern" ask shows up in five different costumes: sessionization ("group events into 30-minute sessions"), funnel conversion ("who did signup → view → cart → checkout in that order?"), anomaly detection ("find the V-shape reversal in this price series"), fraud bursts ("five spend events inside sixty seconds"), and streaming CEP ("emit an alert as soon as the pattern completes"). Every answer starts from the same seven-slot skeleton: PARTITION BY entity ORDER BY time MEASURES ... PATTERN (A B+ C) DEFINE A AS ..., B AS ..., C AS .... The interviewer wants to hear you say "row pattern matching is regex over ordered rows" in the first sentence.

This guide is the mid-to-senior tour you wished existed the first time an interviewer asked you to write a row pattern matching sql query on the whiteboard, sketch an oracle match_recognize V-shape anomaly for a financial series, port a query to snowflake match_recognize, wire a flink sql pattern for streaming CEP, or design a funnel analysis sql pipeline that reports drop-off with sql pattern detection primitives. It walks through the ANSI clause anatomy (PARTITION BY / ORDER BY / MEASURES / ONE ROW PER MATCH vs ALL ROWS PER MATCH / AFTER MATCH SKIP / PATTERN with regex quantifiers / DEFINE), the sessionization pattern with PATTERN ((A B*)+) and per-user partitioning, the funnel pattern with anchored ordering and PERMUTE variants, the V-shape and W-shape stock anomaly the Oracle documentation made famous, and the dialect matrix — Oracle 12c+, Snowflake, Flink SQL, Trino/Presto, RisingWave versus Postgres and BigQuery with the portable LAG + SUM(new_group) OVER fallback. Every section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the pattern-matching practice library →, rehearse on gaps-and-islands problems → for the portable fallback, and sharpen the streaming axis with streaming drills →.

On this page

Why row pattern matching matters in 2026
MATCH_RECOGNIZE anatomy
Sessionization with MR
Funnel & conversion patterns
Anomaly detection & dialect matrix
Cheat sheet — MATCH_RECOGNIZE recipe list
Frequently asked questions
Practice on PipeCode

1. Why row pattern matching matters in 2026

The "regex over ordered rows" family — sessionization, funnels, anomalies, fraud bursts, and every other row-sequence question a senior interviewer might ask

The one-sentence invariant: a sql match_recognize question asks you to detect an ordered sequence of rows that satisfies a regex-like pattern inside a partition, and the canonical answer is a seven-clause skeleton where PATTERN is a regex over labelled row classes and DEFINE assigns predicates to those labels. Once you internalise that "PATTERN + DEFINE is the whole engine," the row-pattern interview surface collapses into a template you can write from muscle memory.

Five faces of the same problem.

Sessionization. Given an event stream, group consecutive events into sessions separated by a gap of inactivity — a pattern (A B*)+ where B continues while the inactivity gap is inside the threshold. Every product analytics platform under the sun — Google Analytics, Segment, Rudderstack, Snowplow, PostHog, Mixpanel — implements exactly this logic. The interviewer wants to see the MR version and the portable LAG + SUM version, and to hear you say which one you would ship on which warehouse.
Funnels. Given a table of user events, find users who went SIGNUP → VIEW_PRODUCT+ → ADD_TO_CART → CHECKOUT in that order. Growth teams live and die on funnel conversion — every product analytics dashboard does this, and every senior data engineer needs to write the query cold. MR reads like a regex and expresses the strict ordering trivially; the portable fallback needs a self-join per step.
Anomaly detection. Given a price series, find every V-shape reversal (N down candles followed by N up candles) or every W-shape double bottom. The classic Oracle MATCH_RECOGNIZE documentation example is exactly this shape — it's the query the ANSI SQL/2016 committee had in mind when they wrote the clause. Fraud detection uses PATTERN (SPEND{5,}) for spend bursts inside a time window.
CEP / streaming pattern detection. flink sql pattern and the Flink CEP library expose MATCH_RECOGNIZE as a streaming operator — the same pattern-detection semantics but with watermarks, event time, and low-latency emission. RisingWave ships the same clause with streaming semantics. Trino and Presto ship the batch variant.
Time-series segmentation. Given a sensor stream, find every run of contiguous HIGH readings followed by a LOW — a two-part MR pattern that reads more clearly than the equivalent LAG + CASE chain.

Two mental primitives.

PATTERN is a regex over row labels. The alphabet is user-defined row labels (A, B, C, or named labels like SIGNUP, VIEW, CART, CHECKOUT). Quantifiers are the standard regex set: * (zero or more), + (one or more), ? (zero or one), {n} (exactly n), {n,m} (n to m). Alternation is |. Grouping is parentheses.
DEFINE is a predicate per label. For each label used in PATTERN, DEFINE assigns a boolean predicate that the row must satisfy to be classified with that label. Predicates can reference PREV(col), NEXT(col), FIRST(col), LAST(col), and any other label's rows already matched — the state machine is aware of what came before.

Why senior interviewers love this family.

It tests window-function fluency plus regex thinking. Everyone can write ROW_NUMBER for a leaderboard; fewer can write PATTERN (STRT DOWN+ UP+) for a V-shape reversal. The interviewer probes whether you understand that pattern matching over rows is a distinct primitive from windowing over rows.
It has a portable answer and a native answer. The interviewer wants to see both: the LAG + SUM(new_group) OVER template from gaps-and-islands and the MATCH_RECOGNIZE version. Senior candidates know when to reach for which — MR on Oracle, Snowflake, Flink, Trino, RisingWave; portable everywhere else.
It's compositional. Once you can sessionize, funnels are the same skeleton with a longer PATTERN. Once you can do funnels, anomalies are a PATTERN with DEFINE predicates that reference PREV(col). Once you can do anomalies, streaming CEP is the same query with a watermark. A senior candidate walks through this progression without prompting.
It exposes partitioning and ordering discipline. Every answer must PARTITION BY user_id (or symbol, or device_id) and ORDER BY event_time. Candidates who forget the partition get wrong answers on multi-entity data — an instant red flag.

Where row-pattern queries land in your pipelines.

Product analytics. Every funnel report is a MR query underneath (or a chain of self-joins that could be one MR query). Every session is a PATTERN ((A B*)+) match. Every retention cohort is an anchored MR match against the first activity date.
Financial time series. V-shape reversals, W-shape double bottoms, breakout patterns, three-white-soldiers candlestick patterns — every classical technical-analysis pattern is a MR query. Fraud detection uses MR for burst detection, velocity checks, and sequence-of-events triggers.
Reliability / SRE. "N consecutive failed health-checks then an outage marker" is a MR pattern. Post-mortem timeline reconstruction is easier as MR than as a chain of window functions.
Streaming CEP. Flink SQL exposes MR as a streaming operator so an alert emits the moment the pattern completes on a live event stream. RisingWave ships the same primitive with streaming semantics. Real-time fraud, uptime monitoring, and order-flow anomaly detection all use this shape.
Data quality checks. "Assert that every START event is followed by a matching END within 5 minutes" is a MR query with a WITH UNMATCHED ROWS clause that exposes the violating rows.

What senior interviewers actually probe.

Anatomy first. Do you know the seven clauses? Can you name each one and say what it controls? PARTITION BY, ORDER BY, MEASURES, ONE ROW PER MATCH vs ALL ROWS PER MATCH, AFTER MATCH SKIP, PATTERN, DEFINE — say them in order without prompting and you're already in the top 20%.
PATTERN quantifiers. Do you know * vs + vs ? vs {n} vs {n,m} vs | vs grouping? Do you know the greedy vs reluctant distinction (+ vs +?)? Do you know PERMUTE(A, B, C) for unordered variants?
AFTER MATCH SKIP. Do you know SKIP TO NEXT ROW vs SKIP PAST LAST ROW vs SKIP TO FIRST <label> vs SKIP TO LAST <label>? The default is SKIP PAST LAST ROW on most engines; SKIP TO NEXT ROW lets matches overlap. Getting this wrong doubles or halves your output row count.
ONE ROW PER MATCH vs ALL ROWS PER MATCH. Do you know when to collapse a match into one summary row vs when to preserve the raw rows with a match id? Session summaries want ONE ROW; per-event enrichment wants ALL ROWS.
Dialect matrix. Can you name the engines that ship MR (Oracle, Snowflake, Flink SQL, Trino, Presto, RisingWave) and the ones that don't (Postgres, BigQuery, SQL Server, MySQL, Redshift as of 2026)? Do you default to the portable LAG + SUM pattern for a multi-warehouse codebase?

Worked example — the "consecutive down candles" anomaly starter

Detailed explanation. The canonical warm-up: given a prices(t, price) series, find every run of two or more consecutive price drops. This is the fastest way to check that a candidate recognises the MR family — the two-line PATTERN + DEFINE answer is a fingerprint on the whiteboard.

Question. Given prices(t, price) where t is a dense ascending integer and price is a positive integer, write an Oracle / Snowflake MR query that returns the start and end index of every run of two or more consecutive price drops.

Input.

t	price
1	100
2	98
3	95
4	97
5	90
6	88

Code.

SELECT *
FROM prices
MATCH_RECOGNIZE (
  ORDER BY t
  MEASURES
    FIRST(t)  AS run_start,
    LAST(t)   AS run_end,
    COUNT(*)  AS run_length
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (DOWN{2,})
  DEFINE
    DOWN AS price < PREV(price)
);

Step-by-step explanation.

ORDER BY t establishes the row order the pattern is matched against. Without it, the engine has no notion of "previous" or "next."
PATTERN (DOWN{2,}) — the regex says "match two or more consecutive rows classified as DOWN." The quantifier {2,} is "two or more" (regex convention).
DEFINE DOWN AS price < PREV(price) — a row is classified as DOWN only if its price is less than the previous row's price. PREV(col) reads the value from the previous row inside the current pattern match.
MEASURES FIRST(t) AS run_start, LAST(t) AS run_end, COUNT(*) AS run_length — for each match, emit the first row's t, the last row's t, and the count of matched rows.
AFTER MATCH SKIP PAST LAST ROW — after a match ends, resume matching at the row after the last matched row. This prevents overlapping matches — the default and the right choice for anomaly runs.

Output.

run_start	run_end	run_length
2	3	2
5	6	2

Rule of thumb. The consecutive-down-candles pattern is the MR "hello world" — recognise the shape in 5 seconds, write PATTERN + DEFINE in 30, and move on to the harder V-shape / W-shape patterns the interviewer really wants.

Worked example — the LAG + SUM portable equivalent

Detailed explanation. The senior follow-up: "write the same query without MATCH_RECOGNIZE." This is the portable answer that ships on Postgres, BigQuery, SQL Server, MySQL, Redshift, and every warehouse that doesn't ship MR. The equivalence check is a real interview probe — the interviewer wants to see that you can translate between the two.

Question. Rewrite the consecutive-down-candles query using LAG and cumulative SUM only.

Input. (Same as above.)

Code.

WITH flagged AS (
  SELECT
    t,
    price,
    CASE
      WHEN price < LAG(price) OVER (ORDER BY t) THEN 1
      ELSE 0
    END AS is_down,
    CASE
      WHEN (price < LAG(price) OVER (ORDER BY t))
        <> COALESCE(LAG(price) OVER (ORDER BY t) < LAG(price, 2) OVER (ORDER BY t), FALSE)
        THEN 1
      ELSE 0
    END AS is_new_run
  FROM prices
),
grouped AS (
  SELECT
    t,
    price,
    is_down,
    SUM(is_new_run) OVER (ORDER BY t) AS run_id
  FROM flagged
  WHERE is_down = 1
)
SELECT
  MIN(t)   AS run_start,
  MAX(t)   AS run_end,
  COUNT(*) AS run_length
FROM grouped
GROUP BY run_id
HAVING COUNT(*) >= 2
ORDER BY run_start;

Step-by-step explanation.

flagged CTE — mark every row where the price is less than the previous row's price as is_down = 1. The is_new_run flag fires on a DOWN row whose predecessor was not DOWN, so the cumulative sum starts a new run.
grouped CTE — keep only DOWN rows and cumulative-sum is_new_run into run_id. Rows inside the same run share the same id.
Final SELECT — group by run_id and aggregate. Filter to HAVING COUNT(*) >= 2 to enforce the "two or more" clause the PATTERN quantifier {2,} gave us for free.
This portable version is 15+ lines vs 12 lines for the MR version. On complex patterns the gap widens dramatically — a "buy then three drops then a stop-loss" pattern is 40+ lines portable vs 20 lines MR.
Rule of thumb: for portable code, the LAG + SUM template works. For readability on Oracle / Snowflake / Flink / Trino / RisingWave, MR wins as soon as the pattern is more than one row class.

Output. (Same as above — same rows, different plan.)

Rule of thumb. Every MR pattern has a LAG + SUM portable equivalent — the translation is mechanical for simple patterns and painful for regex-heavy ones. Portability first; MR when the pattern justifies it.

Worked example — engine dialect quickstart

Detailed explanation. A quick sanity check the interviewer might drop: "Which of these engines can I run MATCH_RECOGNIZE on today?" The answer is a short list — knowing it cold is a senior signal.

Question. Given a list of warehouses / engines, mark each as MR-capable or portable-fallback-only in 2026.

Input. Oracle 12c+, Snowflake, Flink SQL, Trino, Presto, RisingWave, Postgres 16, BigQuery, SQL Server 2022, MySQL 8, Redshift, Databricks SQL.

Code. (No code — a dialect matrix answer.)

Engine              | MATCH_RECOGNIZE support (as of 2026)
Oracle 12c+         | YES — full ANSI, shipped 2013 (first in the market)
Snowflake           | YES — full ANSI, GA since 2021
Flink SQL           | YES — streaming CEP semantics
Trino / Presto      | YES — batch, since Trino 361
RisingWave          | YES — streaming, since 1.7
Postgres 16         | NO — no planned support; use LAG + SUM
BigQuery            | NO — no planned support; use LAG + SUM
SQL Server 2022     | NO — no planned support; use LAG + SUM
MySQL 8             | NO — no planned support; use LAG + SUM
Redshift            | NO — no planned support; use LAG + SUM
Databricks SQL      | NO (as of 2026) — use LAG + SUM

Step-by-step explanation.

Oracle was first — the ANSI SQL/2016 committee based the standard on Oracle's original implementation from 12c.
Snowflake followed in 2021 GA — the batch semantics match Oracle's exactly.
Flink SQL and RisingWave ship the streaming variant — watermark-driven pattern completion with low-latency emission.
Trino / Presto ship the batch variant — most cloud data lakehouses (Athena, Dremio, Ahana) that build on Trino inherit MR support.
Postgres, BigQuery, SQL Server, MySQL, Redshift, and Databricks SQL do not ship MR in 2026; portable LAG + SUM is the fallback. If your codebase is multi-warehouse, default to portable.

Output. The matrix above.

Rule of thumb. Memorise the five-yes / six-no list. When an interviewer asks "would you use MATCH_RECOGNIZE here?" the first question back is "what's the warehouse?" — say it out loud and you signal senior instincts.

Senior interview question on the row-pattern mental model

A senior interviewer often opens with: "I hand you an event stream and ask for a metric — pick sessionization, funnel conversion, or anomaly detection — that requires you to detect an ordered row pattern. Walk me through the seven-clause skeleton, and tell me what changes across the three asks."

Solution Using the seven-clause MATCH_RECOGNIZE skeleton

-- Universal MATCH_RECOGNIZE skeleton — seven clauses, one regex over rows
SELECT <select-list>
FROM <input-table>
MATCH_RECOGNIZE (
  PARTITION BY <entity-col>                          -- Slot 1: per-entity scope
  ORDER BY <ordering-col>                            -- Slot 2: row order
  MEASURES                                           -- Slot 3: per-match columns
    FIRST(<label>.<col>) AS <alias>,
    LAST (<label>.<col>) AS <alias>,
    COUNT(*)             AS <alias>,
    CLASSIFIER()         AS matched_label
  ONE ROW PER MATCH                                  -- Slot 4: output shape
  AFTER MATCH SKIP PAST LAST ROW                     -- Slot 5: overlap policy
  PATTERN (<A> <B>+ <C>?)                            -- Slot 6: regex over labels
  DEFINE                                             -- Slot 7: label predicates
    <A> AS <predicate on A>,
    <B> AS <predicate on B, may reference PREV/NEXT>,
    <C> AS <predicate on C>
);

Step-by-step trace.

Ask	PARTITION BY	PATTERN	Key DEFINE predicate	Output shape
Sessionization	user_id	`((A B*)+)`	`B AS ts - LAG(B.ts) <= INTERVAL '30 minutes'`	ONE ROW PER MATCH → session summary
Funnel	user_id	`(SIGNUP VIEW+ CART CHECKOUT)`	`VIEW AS event_type = 'view_product'`	ONE ROW PER MATCH → converted user + timings
V-shape anomaly	symbol	`(STRT DOWN+ UP+)`	`DOWN AS price < PREV(price)`, `UP AS price > PREV(price)`	ONE ROW PER MATCH → reversal boundaries
W-shape anomaly	symbol	`(DOWN+ UP+ DOWN+ UP+)`	Same as V-shape	ONE ROW PER MATCH → double-bottom boundaries
Fraud burst	user_id	`(SPEND{5,})`	`SPEND AS ts - FIRST(SPEND.ts) <= INTERVAL '60' SECOND`	ONE ROW PER MATCH → burst boundaries
Per-event enrichment	user_id	Any	Same as above	ALL ROWS PER MATCH → per-row classifier

The universal skeleton turns "which query do I write?" from a taste question into a mechanical checklist. Pick the partition, pick the ordering, pick the PATTERN, write the DEFINE predicates — the whole query falls out.

Output:

Ask	Approx query length (MR)	Approx query length (portable LAG + SUM)
Sessionization	~15 lines	~20 lines
Funnel	~15 lines	~40 lines (4 self-joins)
V-shape anomaly	~15 lines	~25 lines
W-shape anomaly	~15 lines	~40+ lines
Fraud burst	~15 lines	~30 lines

Why this works — concept by concept:

PATTERN is a regex over row labels — every MR answer starts with a regex over labels drawn from an alphabet you define. The moment you can express the ask as a regex, PATTERN writes itself. A B+ C is the archetype; every variant is a quantifier tweak.
DEFINE is a predicate per label — for each label used in PATTERN, DEFINE gives the boolean predicate that classifies a row into that label. Predicates can reference PREV, NEXT, FIRST, LAST of the current or previous labels — the state machine is aware of history.
PARTITION BY entity is non-negotiable — multi-entity data always requires per-entity partitioning. Skipping it produces a global pattern that mixes users / symbols / devices, which is a wrong-answer buffer that the interviewer catches immediately.
ORDER BY inside the MR block matters — the ordering determines what "previous" and "next" mean. Sessionization orders by event_time; anomaly detection orders by ts; funnels order by event_time. Get the ordering wrong and PREV / NEXT lie.
Cost — MR compiles to a state-machine scan over each partition, cost O(N) per partition after the sort. The dominant cost is the ORDER BY sort — O(N log N) per partition. On modern engines the state-machine step is streamed row-by-row inside the sort output, so wall clock is essentially the sort cost. Portable LAG + SUM is the same cost class; MR just reads better for regex-shaped patterns.

SQL
Topic — pattern matching
Pattern matching problems

Practice →

SQL Topic — gaps and islands Gaps and islands drills

Practice →

2. MATCH_RECOGNIZE anatomy

The seven-clause skeleton — PARTITION BY, ORDER BY, MEASURES, ONE ROW / ALL ROWS PER MATCH, AFTER MATCH SKIP, PATTERN, DEFINE

The mental model in one line: MATCH_RECOGNIZE is a regex engine that runs over ordered rows inside a partition, where PATTERN is the regex, DEFINE is the alphabet's semantics, and MEASURES + ONE ROW / ALL ROWS PER MATCH shape the output. Once you say "PATTERN is regex, DEFINE is semantics, MEASURES is payload" out loud, the anatomy interview surface collapses to a slot-filling exercise.

$Visual diagram of the MATCH_RECOGNIZE anatomy — a vertical annotated skeleton showing the seven clause slots (PARTITION BY, ORDER BY, MEASURES, ONE ROW PER MATCH vs ALL ROWS PER MATCH, AFTER MATCH SKIP, PATTERN with quantifiers, DEFINE), a right-hand toy consecutive-price-drop example, and a small legend for the regex quantifiers *, +, ?, {n}, {n,m}; on a light PipeCode card.$

Slot 1 — PARTITION BY <cols>.

Scopes the pattern engine per entity. Every partition is matched independently — one match per user, per symbol, per device, per session.
Optional but almost always present. Omitting it runs the pattern over the whole table as one partition — right only for single-entity data (a single sensor stream, a single stock price series).
Multiple columns supported — PARTITION BY user_id, device_id scopes per (user, device) pair.

Slot 2 — ORDER BY <cols>.

Required. Establishes the row order the pattern is matched against. PREV and NEXT are defined relative to this ordering.
Almost always event_time or a monotonically-increasing sequence. Ties are broken by additional columns — ORDER BY event_time, event_id is the defensive form.
Skipping ORDER BY inside MR raises a compilation error on every engine.

Slot 3 — MEASURES.

Declares the per-match output columns. Each measure is an expression evaluated at match time, using FIRST(label.col), LAST(label.col), COUNT(*), aggregations, and any expression that references the pattern's labels.
Standard measure functions:
- FIRST(A.col) — value of col from the first A-labelled row in the match.
- LAST(A.col) — value of col from the last A-labelled row.
- COUNT(A.*) — count of A-labelled rows.
- SUM(A.col), AVG(A.col), MIN(A.col), MAX(A.col) — aggregate over A-labelled rows.
- CLASSIFIER() — returns the label a specific row was classified into (used with ALL ROWS PER MATCH).
- MATCH_NUMBER() — a monotonically-increasing per-partition match id.

Slot 4 — ONE ROW PER MATCH vs ALL ROWS PER MATCH.

ONE ROW PER MATCH — one output row per matched pattern. The output row's values come from the MEASURES expressions. Right for session summaries, funnel outcomes, anomaly boundaries.
ALL ROWS PER MATCH — one output row per row inside the match. Each row carries the match id (MATCH_NUMBER()), its classifier (CLASSIFIER()), and any MEASURES you declared. Right for per-event enrichment inside a session, funnel step attribution.
Variants — ALL ROWS PER MATCH SHOW EMPTY MATCHES (include zero-length matches), ALL ROWS PER MATCH OMIT EMPTY MATCHES (skip them), ALL ROWS PER MATCH WITH UNMATCHED ROWS (also emit rows that never matched any pattern — key for drop-off attribution).

Slot 5 — AFTER MATCH SKIP ….

Controls where the engine resumes matching after a successful match. Four canonical options:
- AFTER MATCH SKIP PAST LAST ROW — resume at the row after the last matched row. No overlap. Default on Oracle / Snowflake / Trino / Flink.
- AFTER MATCH SKIP TO NEXT ROW — resume at the row after the first matched row. Allows overlap.
- AFTER MATCH SKIP TO FIRST <label> — resume at the first row labelled <label> in the match.
- AFTER MATCH SKIP TO LAST <label> — resume at the last row labelled <label> in the match.
Getting this wrong doubles or halves your output row count. For anomaly runs and funnel completions, SKIP PAST LAST ROW is right. For "count every possible occurrence" analytics, SKIP TO NEXT ROW is right.

Slot 6 — PATTERN (...).

The regex over row labels. Syntax:
- Concatenation — A B C matches an A row followed by a B row followed by a C row.
- * — zero or more.
- + — one or more.
- ? — zero or one.
- {n} — exactly n.
- {n,m} — n to m (inclusive).
- | — alternation (A | B matches either).
- Parentheses — grouping ((A B)+ matches one or more A B pairs).
- Reluctant quantifiers — +?, *?, ?? — match as few rows as possible (greedy is default).
- Anchors — ^ (start of partition), $ (end of partition) on engines that support them.
- PERMUTE(A, B, C) — match A, B, C in any order (Oracle / Trino / Flink).
Reads like a regex over the row alphabet — once you can write regex, PATTERN writes itself.

Slot 7 — DEFINE <label> AS <predicate>.

Assigns a boolean predicate to each PATTERN label. A row is classified into a label only if it satisfies the label's predicate at that step of the state machine.
If a label is used in PATTERN but not defined, it defaults to TRUE (matches any row). PATTERN (A B*) DEFINE B AS ... — A matches anything.
Predicates can reference:
- The current row's columns — DEFINE UP AS price > 100.
- The previous row inside the match — DEFINE UP AS price > PREV(price).
- The first row of the match — DEFINE B AS ts - FIRST(A.ts) <= INTERVAL '30 minutes'.
- The last row of a specific label — DEFINE C AS price > LAST(B.price).
- Aggregates over previously matched rows — DEFINE C AS SUM(A.qty) + SUM(B.qty) > 100.

Reading order for a MR query.

What's the partition? → PARTITION BY.
What's the row order? → ORDER BY.
What's the pattern I'm looking for? → PATTERN.
What predicates define each label? → DEFINE.
What do I emit per match? → MEASURES.
One row per match or all rows? → ONE ROW / ALL ROWS PER MATCH.
Where do I resume after a match? → AFTER MATCH SKIP.

Every senior candidate walks through the seven slots in this order without prompting.

Worked example — consecutive-price-drop with ONE ROW PER MATCH

Detailed explanation. The classic anatomy warm-up: use ONE ROW PER MATCH to emit one summary row per run of consecutive price drops. This is where every candidate should start when learning MR.

Question. Given prices(t, price) ordered by t, use MATCH_RECOGNIZE to emit one row per run of two or more consecutive price drops, with the run's start ts, end ts, count, and first / last prices.

Input.

t	price
1	100
2	98
3	95
4	93
5	97
6	90

Code.

SELECT *
FROM prices
MATCH_RECOGNIZE (
  ORDER BY t
  MEASURES
    FIRST(t)     AS run_start,
    LAST(t)      AS run_end,
    FIRST(price) AS start_price,
    LAST(price)  AS end_price,
    COUNT(*)     AS run_length
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (DOWN{2,})
  DEFINE
    DOWN AS price < PREV(price)
);

Step-by-step explanation.

ORDER BY t — the state machine walks rows in t order. PREV(price) refers to the immediately preceding row inside the match.
PATTERN (DOWN{2,}) — regex says "two or more DOWN rows in a row." Matches greedily by default — as many DOWN rows as possible.
DEFINE DOWN AS price < PREV(price) — a row is DOWN only if its price is less than the previous row's. PREV(price) on the first row of a partition is NULL — the predicate evaluates to NULL, treated as FALSE, so no match starts on row 1.
MEASURES — FIRST(t) and LAST(t) are the boundary timestamps; FIRST(price) and LAST(price) are the boundary prices; COUNT(*) is the run length.
AFTER MATCH SKIP PAST LAST ROW — after emitting a match ending at row k, resume at row k+1. No overlapping runs.

Output.

run_start	run_end	start_price	end_price	run_length
2	4	98	93	3
6	6	90	90	0

Wait — the second row is wrong because row 6 alone can't satisfy DOWN{2,}. Correct output:

run_start	run_end	start_price	end_price	run_length
2	4	98	93	3

Rule of thumb. ONE ROW PER MATCH collapses each pattern match into one summary row. Perfect for "how many runs did I have?" reporting. When you need per-row enrichment, switch to ALL ROWS PER MATCH.

Worked example — same pattern with ALL ROWS PER MATCH + CLASSIFIER

Detailed explanation. The follow-up variant: preserve every row inside the match and label each with its classifier. Useful for per-event enrichment — you keep every input row and enrich it with match id and label.

Question. Rewrite the consecutive-price-drop query to preserve every row and label each row with its match id and classifier.

Input. (Same as above.)

Code.

SELECT *
FROM prices
MATCH_RECOGNIZE (
  ORDER BY t
  MEASURES
    MATCH_NUMBER() AS match_id,
    CLASSIFIER()   AS matched_as
  ALL ROWS PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (DOWN{2,})
  DEFINE
    DOWN AS price < PREV(price)
);

Step-by-step explanation.

ALL ROWS PER MATCH — instead of one summary row per match, emit every input row that was part of a match.
MATCH_NUMBER() — a monotonically-increasing per-partition id. Rows from match 1 have match_id = 1; rows from match 2 have match_id = 2.
CLASSIFIER() — returns the label the row was classified into. In this query it's always DOWN (the only label).
Rows that didn't match any pattern are not emitted with ALL ROWS PER MATCH. To emit them too, use ALL ROWS PER MATCH WITH UNMATCHED ROWS.
Use this variant when downstream code needs the original rows enriched with match metadata — e.g., a Sankey diagram of the funnel or a per-event timeline replay.

Output.

t	price	match_id	matched_as
2	98	1	DOWN
3	95	1	DOWN
4	93	1	DOWN

Rule of thumb. ONE ROW PER MATCH for summary reporting; ALL ROWS PER MATCH for per-event enrichment. Match id + classifier are the standard columns you'd project.

Worked example — AFTER MATCH SKIP TO NEXT ROW for overlapping matches

Detailed explanation. The nuanced follow-up: sometimes you want every possible pattern occurrence, including overlapping ones. Switching AFTER MATCH SKIP PAST LAST ROW to AFTER MATCH SKIP TO NEXT ROW changes the output row count dramatically.

Question. Given prices(t, price), count every pair of consecutive drops — even when one pair's second drop is the next pair's first drop. Use AFTER MATCH SKIP TO NEXT ROW and PATTERN (DOWN DOWN).

Input.

t	price
1	100
2	98
3	95
4	90

Code.

SELECT *
FROM prices
MATCH_RECOGNIZE (
  ORDER BY t
  MEASURES
    FIRST(t) AS pair_start,
    LAST(t)  AS pair_end
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO NEXT ROW
  PATTERN (DOWN DOWN)
  DEFINE
    DOWN AS price < PREV(price)
);

Step-by-step explanation.

PATTERN (DOWN DOWN) — match exactly two consecutive DOWN rows.
AFTER MATCH SKIP TO NEXT ROW — after a match starting at row k and ending at k+1, resume matching at row k+1 (the middle row), not k+2. This lets the next match start at what was the previous match's second row.
Row 2 → 3 forms one match (DOWN at 2, DOWN at 3). Engine resumes at row 3.
Row 3 → 4 forms another match (DOWN at 3, DOWN at 4). Engine resumes at row 4.
Result: two overlapping matches instead of one. With SKIP PAST LAST ROW, we'd only get the first match — the engine would resume at row 4 after emitting the 2 → 3 match.

Output.

pair_start	pair_end
2	3
3	4

Rule of thumb. SKIP PAST LAST ROW for non-overlapping runs (sessions, anomalies, funnels). SKIP TO NEXT ROW for "every possible occurrence" analytics (candlestick pair counting, sliding-window pattern probes). Choose deliberately.

Senior interview question on anatomy fluency

A senior interviewer might ask: "Walk me through a MATCH_RECOGNIZE query that finds every user's first session of length ≥ 3 events, ordered by event time. I want to see the seven clauses filled in with the right choices — including ONE ROW vs ALL ROWS, AFTER MATCH SKIP semantics, and the PATTERN quantifier."

Solution Using the seven-clause skeleton with first-match selection

SELECT *
FROM events
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY event_time
  MEASURES
    MATCH_NUMBER()      AS match_id,
    FIRST(A.event_time) AS session_start,
    LAST(B.event_time)  AS session_end,
    COUNT(*)            AS event_count
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (A B{2,})
  DEFINE
    A AS TRUE,
    B AS event_time - LAST(A.event_time) <= INTERVAL '30' MINUTE
       AND event_time - LAST(B.event_time, 1) <= INTERVAL '30' MINUTE
)
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY session_start) = 1;

Step-by-step trace.

Slot	Choice	Why
PARTITION BY	user_id	Per-user pattern scope
ORDER BY	event_time	Pattern runs in time order
MEASURES	match_id, session_start, session_end, event_count	Session summary payload
Output shape	ONE ROW PER MATCH	Session summary, not per-event enrichment
AFTER MATCH SKIP	SKIP PAST LAST ROW	No overlapping sessions
PATTERN	`(A B{2,})`	Anchor A + 2 or more continuation rows
DEFINE A	TRUE	Any row can start a session
DEFINE B	Within 30 min of prior row	Sessionization inactivity threshold
Post-filter	QUALIFY ROW_NUMBER() = 1	First session per user

The MR block finds every session of length ≥ 3 per user; the QUALIFY picks the earliest one per user. This is the sessionization + first-match composition pattern.

Output:

user_id	match_id	session_start	session_end	event_count
u1	1	2026-07-01 10:00	2026-07-01 10:20	3
u2	1	2026-07-02 09:00	2026-07-02 09:10	4

Why this works — concept by concept:

PATTERN (A B{2,}) encodes "anchor plus 2+ continuation" — the anchor A matches any row (DEFINE A AS TRUE); the B{2,} quantifier says "two or more continuation rows." Together, the pattern matches sessions of length ≥ 3 (one A + two or more B).
DEFINE B references LAST(A.event_time) and LAST(B.event_time, 1) — the predicate says "this B row is within 30 minutes of the previous B row (or the A row if this is the first B)." LAST(B.event_time, 1) is the "one B ago" — the immediately preceding B row inside the match.
QUALIFY for first-per-user selection — QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY session_start) = 1 picks the earliest session per user. QUALIFY is the Snowflake / BigQuery shorthand; on Oracle / Trino wrap it in a subquery with WHERE rn = 1.
MATCH_NUMBER() in MEASURES — the per-partition match id is useful downstream for debugging and for joining back to the raw rows. Even when you don't display it, projecting it costs nothing.
Cost — one MR pass with a state-machine scan + one QUALIFY window pass. On a 1B-row event table with 10M users, the MR pass is O(N log N) for the sort + O(N) for the state machine; the QUALIFY is O(sessions). Fast on modern warehouses — single-digit minutes on a medium Snowflake warehouse.

SQL
Topic — pattern matching
Pattern matching drills

Practice →

SQL Topic — regular expressions Regular expression problems

Practice →

3. Sessionization with MR

`sql pattern detection` for event streams — the `PATTERN ((A B*)+)` sessionization primitive with per-user partitioning and inactivity DEFINE

The mental model in one line: sessionization with MATCH_RECOGNIZE reads "anchor A, then zero-or-more continuation rows B where B is within 30 minutes of the previous B or the anchor A" — expressed as PATTERN ((A B*)+) with a DEFINE predicate on B for the inactivity threshold. Once you say "A is any row, B stays inside the threshold, and (A B*)+ produces one match per session," the MR sessionization interview surface reduces to a two-line PATTERN + DEFINE recipe.

The canonical MR sessionization contract.

Definition. A session is a maximal run of events from one user where consecutive events are separated by no more than the inactivity threshold. PATTERN ((A B*)+) expresses this as "anchor + zero or more continuations, repeated one or more times." Each MR match is one session.
Anchor semantics. A matches any row; it's the session anchor. DEFINE A AS TRUE is the standard.
Continuation semantics. B matches a row within the inactivity threshold of the previous B row (or the anchor if this is the first B). DEFINE B AS event_time - LAST(A.event_time) <= INTERVAL '30' MINUTE AND event_time - LAST(B.event_time, 1) <= INTERVAL '30' MINUTE. On simple engines the second clause reduces to event_time - PREV(event_time) <= INTERVAL '30' MINUTE.
Match maximality. MR is greedy by default — it matches as many rows as possible. Sessions naturally end when the next row exceeds the threshold, and the match terminates at the last row that stayed inside.

Two equivalent PATTERNs.

PATTERN ((A B*)+) — nested pattern: one or more (A + zero-or-more B) blocks. Rarely used in this form; the outer + is redundant because AFTER MATCH SKIP handles the "next session" transition.
PATTERN (A B*) — the canonical form. One anchor A + zero or more continuations B. Each match is one session; AFTER MATCH SKIP PAST LAST ROW starts the next match at the next row.

Choosing the threshold.

30 minutes. The Google Analytics / Segment / Rudderstack / Snowplow / PostHog / Mixpanel default. Right for consumer web / mobile.
60 minutes. Right for long-attention apps — video streaming, IDEs, coding editors.
10 minutes. Right for high-frequency contexts — IoT dashboards, trading terminals.
Custom. Plot inter-event gap histograms; pick the elbow at the 95th percentile.

Per-user partitioning is non-negotiable.

PARTITION BY user_id inside the MR block is mandatory for multi-user data. Without it, the pattern engine matches across user boundaries — a session from u1 can absorb u2's next event if their timestamps are close.
Multiple partition columns supported — PARTITION BY user_id, device_id for per-(user, device) sessions.

Watermark ceiling for streaming.

On Flink SQL / RisingWave, sessionization runs in streaming mode with a watermark. The MR block can only close a session when the watermark passes the last event's timestamp + the inactivity threshold — otherwise a late event could still extend the session.
Batch MR doesn't need a watermark — the whole partition is available.

Session-level aggregations via MEASURES.

FIRST(event_time) — session start.
LAST(event_time) — session end.
LAST(event_time) - FIRST(event_time) — session duration.
COUNT(*) — event count.
COUNT(A.*) — anchor count (should always be 1 for PATTERN (A B*)).
COUNT(B.*) — continuation count.
FIRST(page_url) — landing page.
LAST(page_url) — exit page.

Common MR sessionization interview probes.

"Why PATTERN (A B*) and not (A+)?" — A+ uses one label with the same DEFINE for both anchor and continuation; the DEFINE would need to handle the "first row" case. A B* cleanly separates anchor semantics from continuation semantics.
"How do you handle the first row?" — A matches any row (DEFINE A AS TRUE), so the first row of the partition is always the anchor of the first match.
"What if a user has no events?" — no rows in the partition, no matches, no output. Fine.
"How do you re-sessionize when late data arrives?" — recompute the MR query over a rolling window. The pattern engine is deterministic given the input rows.
"MR sessionization vs LAG + SUM — which do you ship?" — MR on Oracle / Snowflake / Flink / Trino / RisingWave for readability; portable LAG + SUM for multi-warehouse dbt projects.

Worked example — 30-minute sessionization on Snowflake

Detailed explanation. The canonical Snowflake sessionization ask: given events(user_id, event_time, event_name, page_url), produce session summaries with MR. This is the query every senior candidate should be able to write from memory on Snowflake.

Question. Given events(user_id, event_time, event_name, page_url) with a 30-minute inactivity threshold, use MATCH_RECOGNIZE to produce (user_id, session_id, session_start, session_end, event_count, landing_page, exit_page).

Input.

user_id	event_time	event_name	page_url
u1	2026-07-01 10:00	page_view	/home
u1	2026-07-01 10:05	click	/home
u1	2026-07-01 10:20	page_view	/pricing
u1	2026-07-01 11:15	page_view	/home
u1	2026-07-01 11:20	click	/home

Code.

SELECT *
FROM events
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY event_time
  MEASURES
    MATCH_NUMBER()     AS session_id,
    FIRST(event_time)  AS session_start,
    LAST(event_time)   AS session_end,
    COUNT(*)           AS event_count,
    FIRST(page_url)    AS landing_page,
    LAST(page_url)     AS exit_page
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (A B*)
  DEFINE
    B AS DATEDIFF('minute', LAG(event_time), event_time) <= 30
);

Step-by-step explanation.

PARTITION BY user_id — per-user sessionization. Each user is independent.
ORDER BY event_time — rows walked in time order.
PATTERN (A B*) — one anchor A + zero or more continuations B. Each match is one session.
DEFINE B AS DATEDIFF('minute', LAG(event_time), event_time) <= 30 — a continuation row is one within 30 minutes of the previous row (which may be A on the first B, or the previous B). Snowflake's LAG inside DEFINE reads the previous row's value in the current match.
MEASURES — MATCH_NUMBER for session_id, FIRST/LAST for boundaries, COUNT for event count, FIRST/LAST of page_url for landing / exit page. AFTER MATCH SKIP PAST LAST ROW starts the next match right after the last matched row.

Output.

user_id	session_id	session_start	session_end	event_count	landing_page	exit_page
u1	1	2026-07-01 10:00	2026-07-01 10:20	3	/home	/pricing
u1	2	2026-07-01 11:15	2026-07-01 11:20	2	/home	/home

Rule of thumb. MR sessionization on Snowflake is one query — PATTERN (A B*), DEFINE B on inactivity, MEASURES for the boundaries. Reads more clearly than the LAG + CASE + SUM equivalent; ships faster if your warehouse supports it.

Worked example — the LAG + SUM portable equivalent

Detailed explanation. For a multi-warehouse project, ship the portable LAG + SUM version. This is the "same query, no MR" answer for Postgres, BigQuery, SQL Server, MySQL, Redshift, Databricks.

Question. Rewrite the sessionization query without MATCH_RECOGNIZE.

Input. (Same as above.)

Code.

WITH flagged AS (
  SELECT
    user_id,
    event_time,
    event_name,
    page_url,
    CASE
      WHEN event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
             > INTERVAL '30 minutes'
        OR LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
        THEN 1
      ELSE 0
    END AS new_session
  FROM events
),
sessionized AS (
  SELECT
    user_id,
    event_time,
    event_name,
    page_url,
    SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM flagged
)
SELECT
  user_id,
  session_id,
  MIN(event_time) AS session_start,
  MAX(event_time) AS session_end,
  COUNT(*)        AS event_count,
  MIN(page_url) FILTER (WHERE event_time = MIN(event_time) OVER (PARTITION BY user_id, session_id)) AS landing_page,
  MAX(page_url) FILTER (WHERE event_time = MAX(event_time) OVER (PARTITION BY user_id, session_id)) AS exit_page
FROM sessionized
GROUP BY user_id, session_id
ORDER BY user_id, session_id;

Step-by-step explanation.

flagged CTE — flag new_session = 1 when the inter-event gap exceeds 30 minutes, or when there's no previous event.
sessionized CTE — cumulative-sum the flag per user to produce a per-user session id.
Final SELECT — group by (user_id, session_id) and aggregate to session summary. The FILTER clauses for landing / exit page pick the URL of the first / last event in the session.
Landing / exit page trick — using a window function inside FILTER makes the aggregate pick the URL from the boundary row. Simpler alternative on Postgres 16+: (ARRAY_AGG(page_url ORDER BY event_time))[1] for the first, (ARRAY_AGG(page_url ORDER BY event_time DESC))[1] for the last.
Runtime — three window passes + one aggregate. Same cost class as the MR version; the query is longer but portable to every warehouse.

Output. (Same as above.)

Rule of thumb. MR is shorter; portable is longer but universally supported. For dbt projects that run on multiple warehouses, ship portable. For Snowflake-only projects, ship MR.

Worked example — MR sessionization with explicit sign-out

Detailed explanation. The nuanced follow-up: some products close sessions on explicit user actions (sign-out, tab-close). Extend the PATTERN with a terminator label C that closes the session when a specific event fires.

Question. Given events(user_id, event_time, event_name), treat event_name = 'sign_out' as an explicit session terminator. Use MR to produce session summaries.

Input.

user_id	event_time	event_name
u1	10:00	page_view
u1	10:05	sign_out
u1	10:07	page_view
u1	10:10	click

Code.

SELECT *
FROM events
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY event_time
  MEASURES
    MATCH_NUMBER()    AS session_id,
    FIRST(event_time) AS session_start,
    LAST(event_time)  AS session_end,
    COUNT(*)          AS event_count
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (A B* C?)
  DEFINE
    B AS DATEDIFF('minute', LAG(event_time), event_time) <= 30
       AND event_name != 'sign_out',
    C AS event_name = 'sign_out'
);

Step-by-step explanation.

PATTERN (A B* C?) — anchor A + zero-or-more continuations B + optional terminator C. The terminator is a sign-out event.
DEFINE B AS ... AND event_name != 'sign_out' — B continuations exclude sign-out events. The moment we hit a sign-out, B stops matching.
DEFINE C AS event_name = 'sign_out' — C matches only the sign-out event. C? makes it optional — sessions without a sign-out still match.
After a session with a sign-out C, AFTER MATCH SKIP PAST LAST ROW moves past the sign-out to the next event. That next event becomes the anchor of the following session.
Compared to the "LAG(event_name) = 'sign_out'" trick in the portable version, MR expresses the terminator cleanly with a labelled row class. Much more readable.

Output.

user_id	session_id	session_start	session_end	event_count
u1	1	10:00	10:05	2
u1	2	10:07	10:10	2

Rule of thumb. For any product with explicit session-close semantics, add a terminator label to PATTERN with ? quantifier and a DEFINE predicate matching the closing event. The rest of the pipeline stays the same.

Senior interview question on MR sessionization at scale

A senior interviewer might ask: "You run a product with 500M daily events and 50M users on Snowflake. Design the nightly sessionization pipeline using MATCH_RECOGNIZE, compute per-session aggregates, and forward-fill the last non-null utm_campaign across sessions per user for last-touch attribution. Walk me through the query and the plan cost."

Solution Using MR sessionization + per-session aggregate + forward-fill

-- Stage 1 — MR sessionize
WITH sessionized AS (
  SELECT *
  FROM events
  MATCH_RECOGNIZE (
    PARTITION BY user_id
    ORDER BY event_time
    MEASURES
      MATCH_NUMBER() AS session_id
    ALL ROWS PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B*)
    DEFINE
      B AS DATEDIFF('minute', LAG(event_time), event_time) <= 30
  )
),
-- Stage 2 — per-session aggregate + last non-null campaign
per_session AS (
  SELECT
    user_id,
    session_id,
    MIN(event_time) AS session_start,
    MAX(event_time) AS session_end,
    COUNT(*)        AS event_count,
    (ARRAY_AGG(utm_campaign ORDER BY event_time DESC)
       FILTER (WHERE utm_campaign IS NOT NULL))[1] AS last_touch_campaign
  FROM sessionized
  GROUP BY user_id, session_id
),
-- Stage 3 — forward-fill across sessions
per_user_last_touch AS (
  SELECT
    user_id,
    session_id,
    session_start,
    session_end,
    event_count,
    last_touch_campaign,
    LAST_VALUE(last_touch_campaign IGNORE NULLS) OVER (
      PARTITION BY user_id
      ORDER BY session_start
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS user_last_touch_campaign
  FROM per_session
)
SELECT * FROM per_user_last_touch
ORDER BY user_id, session_start;

Step-by-step trace.

Stage	What runs	Approx rows out on 500M input
1	MR sessionization (ALL ROWS PER MATCH)	500M rows enriched with session_id
2	Per-session aggregate	~100M sessions (assuming 5 events/session avg)
3	Forward-fill last_touch	100M rows with per-user attribution

The three-stage MR + aggregate + forward-fill pipeline sessionizes the stream, aggregates each session to a summary row, then propagates the most recent non-null campaign across the user's session history.

Output:

user_id	session_id	session_start	last_touch_campaign	user_last_touch_campaign
u1	1	2026-07-01	google_ads	google_ads
u1	2	2026-07-05	NULL	google_ads
u1	3	2026-07-10	facebook_ads	facebook_ads

Why this works — concept by concept:

ALL ROWS PER MATCH keeps raw rows for later aggregation — the MR block emits the raw event rows enriched with session_id via MATCH_NUMBER(). This is the cleanest way to sessionize when the downstream stage still needs per-event columns.
Per-session aggregate collapses via GROUP BY session_id — standard SQL from here — GROUP BY (user_id, session_id) with MIN / MAX / COUNT. The ARRAY_AGG(... ORDER BY event_time DESC) FILTER (WHERE ... IS NOT NULL)[1] idiom picks the most recent non-null value inside the session.
LAST_VALUE IGNORE NULLS for cross-session forward-fill — the ANSI-standard "carry the last non-null forward" primitive. The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame is required — without it, LAST_VALUE reads the whole partition.
Modular pipeline — Stage 1 is MR-heavy (fine on Snowflake); Stages 2 and 3 are portable SQL. If you later move to a portable-only warehouse, only Stage 1 needs a rewrite to LAG + SUM.
Cost — Stage 1 is O(N log N) per partition — the MR sort dominates. Stages 2 and 3 are O(sessions) — cheap. On 500M events with 50M users on a medium Snowflake warehouse, the pipeline runs in 15-30 minutes. Well inside a nightly window.

SQL
Topic — streaming
Streaming analytics drills

Practice →

SQL Topic — streaming (medium) Streaming problems (medium)

Practice →

4. Funnel & conversion patterns

`funnel analysis sql` reads like a regex — `PATTERN (SIGNUP VIEW+ CART CHECKOUT)` collapses a four-step funnel into one MR block

The mental model in one line: funnel analysis with MATCH_RECOGNIZE encodes the step ordering directly in the PATTERN — PATTERN (SIGNUP VIEW+ CART CHECKOUT) reads left-to-right as "signup then one-or-more product views then add-to-cart then checkout," and DEFINE gives each label its event-type predicate. Once you say "PATTERN is the funnel shape, DEFINE is each step's event predicate, MEASURES emits the conversion metrics," the funnel interview surface reduces to a regex over your event stream.

The canonical funnel PATTERN.

PATTERN (SIGNUP VIEW+ CART CHECKOUT) — strict left-to-right ordering. User must fire SIGNUP, then one or more VIEW events, then CART, then CHECKOUT. Any deviation breaks the match.
Label naming — use event-type-suggestive labels (SIGNUP, VIEW, CART, CHECKOUT) rather than A, B, C, D. Reads better; downstream engineers get the semantics for free.
Quantifiers matter — VIEW+ (one or more) requires at least one product view; VIEW* (zero or more) allows the user to skip views; VIEW{2,} requires at least two views.

The DEFINE predicates.

DEFINE SIGNUP AS event_type = 'signup' — matches only signup events.
DEFINE VIEW AS event_type = 'view_product' — matches only product view events.
DEFINE CART AS event_type = 'add_to_cart' — matches only add-to-cart events.
DEFINE CHECKOUT AS event_type = 'checkout' — matches only checkout events.

Time constraints inside DEFINE.

Sometimes the funnel has an SLA — "checkout must occur within 24 hours of signup." Add a time predicate to CHECKOUT: DEFINE CHECKOUT AS event_type = 'checkout' AND event_time - FIRST(SIGNUP.event_time) <= INTERVAL '24' HOUR.
Cross-step SLAs — "the last view must be within 1 hour of add-to-cart": DEFINE CART AS event_type = 'add_to_cart' AND event_time - LAST(VIEW.event_time) <= INTERVAL '1' HOUR.

MEASURES — the funnel metrics payload.

FIRST(SIGNUP.event_time) AS signup_ts — funnel entry ts.
FIRST(CHECKOUT.event_time) AS checkout_ts — funnel exit ts.
LAST(CHECKOUT.event_time) - FIRST(SIGNUP.event_time) AS time_to_convert — end-to-end conversion time.
COUNT(VIEW.*) AS view_count — how many product views before add-to-cart.
COUNT(*) AS total_events — total events in the funnel path.
FIRST(VIEW.product_id) AS first_product_viewed — which product started the exploration.
LAST(VIEW.product_id) AS last_product_viewed — which product ended the exploration (often the one added to cart).

AFTER MATCH SKIP for funnels.

AFTER MATCH SKIP PAST LAST ROW — one funnel completion per user, ending at the checkout. The user's post-checkout events don't count for the current funnel. Right for "how many users completed the funnel?" reports.
AFTER MATCH SKIP TO NEXT ROW — allow overlapping funnels (a user starts a new SIGNUP after a checkout, before the next SIGNUP). Rare; only when the pattern legitimately overlaps.
AFTER MATCH SKIP TO LAST CHECKOUT — same as SKIP PAST LAST ROW since CHECKOUT is the terminal label.

PERMUTE — unordered variants.

PATTERN (PERMUTE(A, B, C, D)) — matches A, B, C, D in any order. Oracle / Trino / Flink support PERMUTE; Snowflake does not (as of 2026).
Use for onboarding funnels where the order between steps 2 and 3 doesn't matter — "verify_email, add_phone" is the same as "add_phone, verify_email."
Rare but powerful — PERMUTE explodes to N! alternations under the hood, so keep the arity ≤ 4.

Drop-off attribution — WITH UNMATCHED ROWS.

Standard funnel analysis reports both converters and non-converters. ALL ROWS PER MATCH WITH UNMATCHED ROWS emits every input row, either as part of a match or as an unmatched row.
Unmatched rows have MATCH_NUMBER() = NULL and CLASSIFIER() = NULL. Filter to those to identify drop-off events.
Powerful for "at which step did most users drop off?" reports — group unmatched rows by their event_type.

Common funnel analysis sql interview probes.

"Why is MR better than four self-joins?" — the self-join version is O(N^4) if you're unlucky; MR is O(N log N) per partition. Correctness is also easier — no accidental duplicate rows.
"How do you handle users who repeat steps?" — quantifiers. VIEW+ handles multiple views; CART? handles optional cart.
"How do you enforce a time window between steps?" — DEFINE predicates with event_time - LAST(...) inside them.
"How do you compute drop-off per step?" — WITH UNMATCHED ROWS + group unmatched by event_type.
"MR funnel vs LEAD chain — which do you ship?" — MR on capable engines; LEAD chain (four LEAD calls) on portable stacks.

Worked example — signup → view → cart → checkout funnel

Detailed explanation. The canonical funnel: given a user event stream, find every user who completed a four-step funnel. Emit one row per completing user with conversion time and view count.

Question. Given events(user_id, event_time, event_type, product_id), use MR to find every user who completed the signup → view+ → cart → checkout funnel. Emit (user_id, signup_ts, checkout_ts, time_to_convert_min, view_count, first_product, cart_product).

Input.

user_id	event_time	event_type	product_id
u1	09:00	signup	NULL
u1	09:02	view_product	101
u1	09:05	view_product	102
u1	09:08	add_to_cart	102
u1	09:12	checkout	102
u2	10:00	signup	NULL
u2	10:03	view_product	201
u2	10:05	pageview	NULL

Code.

SELECT *
FROM events
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY event_time
  MEASURES
    FIRST(SIGNUP.event_time)              AS signup_ts,
    LAST(CHECKOUT.event_time)             AS checkout_ts,
    DATEDIFF('minute', FIRST(SIGNUP.event_time), LAST(CHECKOUT.event_time)) AS time_to_convert_min,
    COUNT(VIEW.*)                          AS view_count,
    FIRST(VIEW.product_id)                 AS first_product,
    LAST(CART.product_id)                  AS cart_product
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (SIGNUP VIEW+ CART CHECKOUT)
  DEFINE
    SIGNUP   AS event_type = 'signup',
    VIEW     AS event_type = 'view_product',
    CART     AS event_type = 'add_to_cart',
    CHECKOUT AS event_type = 'checkout'
);

Step-by-step explanation.

PARTITION BY user_id ORDER BY event_time — per-user pattern scope, time-ordered.
PATTERN (SIGNUP VIEW+ CART CHECKOUT) — strict left-to-right: signup, one or more views, cart, checkout.
DEFINE — each label matches a specific event_type. Any deviation breaks the match — a user who sees a page_view between VIEWs doesn't match the strict pattern.
MEASURES — signup ts (FIRST of SIGNUP), checkout ts (LAST of CHECKOUT), time_to_convert (delta), view_count (COUNT of VIEW rows), first product viewed, product in cart.
u1 matches; u2 fails (never got to add_to_cart or checkout). Only u1 appears in the output.

Output.

user_id	signup_ts	checkout_ts	time_to_convert_min	view_count	first_product	cart_product
u1	09:00	09:12	12	2	101	102

Rule of thumb. MR funnel = one PATTERN with the strict step order + one DEFINE per step + MEASURES for the conversion metrics. Reads like the funnel diagram; ships as one query.

Worked example — funnel with drop-off attribution

Detailed explanation. The standard funnel report needs both converters and the drop-off point for non-converters. ALL ROWS PER MATCH WITH UNMATCHED ROWS exposes every input row along with match metadata — the query below emits converters as matched rows and drop-off events as unmatched rows.

Question. Extend the funnel to expose drop-off events. For every user, emit either their conversion path (matched) or their last event before drop-off (unmatched with CLASSIFIER = NULL).

Input. (Same as above.)

Code.

SELECT *
FROM events
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY event_time
  MEASURES
    MATCH_NUMBER() AS match_id,
    CLASSIFIER()   AS matched_step
  ALL ROWS PER MATCH WITH UNMATCHED ROWS
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (SIGNUP VIEW+ CART CHECKOUT)
  DEFINE
    SIGNUP   AS event_type = 'signup',
    VIEW     AS event_type = 'view_product',
    CART     AS event_type = 'add_to_cart',
    CHECKOUT AS event_type = 'checkout'
);

Step-by-step explanation.

ALL ROWS PER MATCH WITH UNMATCHED ROWS — emit every input row. Matched rows carry match_id and matched_step. Unmatched rows have NULL for both.
Downstream aggregation groups unmatched rows by event_type to see where drop-off happened: SELECT event_type, COUNT(*) FROM ... WHERE match_id IS NULL GROUP BY event_type ORDER BY COUNT(*) DESC.
For u2, the signup and view_product rows are unmatched — the pattern couldn't complete because there's no add_to_cart or checkout. Both are emitted with matched_step = NULL.
For u1, every event is emitted with the step it matched (SIGNUP, VIEW, VIEW, CART, CHECKOUT).
This is exactly the shape a Sankey funnel diagram wants — one row per event, enriched with match id and step label.

Output.

user_id	event_time	event_type	match_id	matched_step
u1	09:00	signup	1	SIGNUP
u1	09:02	view_product	1	VIEW
u1	09:05	view_product	1	VIEW
u1	09:08	add_to_cart	1	CART
u1	09:12	checkout	1	CHECKOUT
u2	10:00	signup	NULL	NULL
u2	10:03	view_product	NULL	NULL
u2	10:05	pageview	NULL	NULL

Rule of thumb. For funnel drop-off attribution, use ALL ROWS PER MATCH WITH UNMATCHED ROWS. Unmatched rows are your drop-off population — filter and aggregate by event_type to identify the leaky step.

Worked example — PERMUTE for onboarding step order

Detailed explanation. Onboarding funnels sometimes let users complete steps in any order — "verify_email, add_phone, complete_profile" can happen 1-2-3 or 2-1-3 or any permutation. PERMUTE inside PATTERN matches any ordering.

Question. Given events(user_id, event_time, event_type), find every user who completed the three-step onboarding (verify_email, add_phone, complete_profile) in any order. Emit (user_id, first_step_ts, last_step_ts, total_time_min).

Input.

user_id	event_time	event_type
u1	09:00	verify_email
u1	09:05	add_phone
u1	09:08	complete_profile
u2	10:00	add_phone
u2	10:03	complete_profile
u2	10:06	verify_email

Code.

SELECT *
FROM events
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY event_time
  MEASURES
    FIRST(event_time)                                         AS first_step_ts,
    LAST(event_time)                                          AS last_step_ts,
    DATEDIFF('minute', FIRST(event_time), LAST(event_time))   AS total_time_min
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (PERMUTE(EMAIL, PHONE, PROFILE))
  DEFINE
    EMAIL   AS event_type = 'verify_email',
    PHONE   AS event_type = 'add_phone',
    PROFILE AS event_type = 'complete_profile'
);

Step-by-step explanation.

PATTERN (PERMUTE(EMAIL, PHONE, PROFILE)) — match EMAIL, PHONE, PROFILE in any order. Under the hood the engine expands to all 3! = 6 alternations: (EMAIL PHONE PROFILE | EMAIL PROFILE PHONE | PHONE EMAIL PROFILE | PHONE PROFILE EMAIL | PROFILE EMAIL PHONE | PROFILE PHONE EMAIL).
Both u1 (1-2-3 order) and u2 (2-3-1 order) match.
FIRST(event_time) — the earliest event in the match (regardless of which label it was).
LAST(event_time) — the latest event in the match.
PERMUTE is available on Oracle, Trino, Flink. Snowflake requires explicit alternation as of 2026 — write the 6 branches manually or use a portable fallback.

Output.

user_id	first_step_ts	last_step_ts	total_time_min
u1	09:00	09:08	8
u2	10:00	10:06	6

Rule of thumb. PERMUTE handles unordered funnel steps declaratively — write PERMUTE(A, B, C) instead of a 6-way OR. Keep arity ≤ 4 (24 alternations); above that, the state machine gets expensive.

Senior interview question on multi-step funnel attribution

A senior interviewer might ask: "You run an e-commerce site and want a nightly report: for each user who completed the signup → view+ → cart → checkout funnel, emit (user_id, signup_ts, checkout_ts, time_to_convert, view_count, drop_off_step) — where drop_off_step is 'CONVERTED' for completers and the last step reached for non-completers. Walk me through the query."

Solution Using MR + WITH UNMATCHED ROWS + last-step aggregate

WITH walk AS (
  SELECT *
  FROM events
  MATCH_RECOGNIZE (
    PARTITION BY user_id
    ORDER BY event_time
    MEASURES
      MATCH_NUMBER() AS match_id,
      CLASSIFIER()   AS matched_step
    ALL ROWS PER MATCH WITH UNMATCHED ROWS
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (SIGNUP VIEW+ CART CHECKOUT)
    DEFINE
      SIGNUP   AS event_type = 'signup',
      VIEW     AS event_type = 'view_product',
      CART     AS event_type = 'add_to_cart',
      CHECKOUT AS event_type = 'checkout'
  )
),
per_user AS (
  SELECT
    user_id,
    MAX(CASE WHEN matched_step = 'CHECKOUT' THEN event_time END) AS checkout_ts,
    MIN(CASE WHEN matched_step = 'SIGNUP'   THEN event_time END) AS signup_ts,
    COUNT(CASE WHEN matched_step = 'VIEW'   THEN 1 END)          AS view_count,
    -- The last step reached in any run (matched or unmatched signup / view / cart)
    CASE
      WHEN MAX(CASE WHEN matched_step = 'CHECKOUT' THEN 1 END) = 1 THEN 'CONVERTED'
      WHEN MAX(CASE WHEN matched_step = 'CART'     THEN 1 END) = 1 THEN 'CART'
      WHEN MAX(CASE WHEN matched_step = 'VIEW'     THEN 1 END) = 1 THEN 'VIEW'
      WHEN MAX(CASE WHEN matched_step = 'SIGNUP'   THEN 1 END) = 1 THEN 'SIGNUP'
      WHEN MAX(CASE WHEN event_type   = 'signup'   THEN 1 END) = 1 THEN 'SIGNUP_UNMATCHED'
      ELSE 'NO_SIGNUP'
    END AS drop_off_step
  FROM walk
  GROUP BY user_id
)
SELECT
  user_id,
  signup_ts,
  checkout_ts,
  DATEDIFF('minute', signup_ts, checkout_ts) AS time_to_convert_min,
  view_count,
  drop_off_step
FROM per_user
ORDER BY user_id;

Step-by-step trace.

Step	What runs	Purpose
1	`walk` — MR with WITH UNMATCHED ROWS	Emit every event with match_id + matched_step (or NULLs)
2	`per_user` — aggregate	Per-user MIN(signup_ts), MAX(checkout_ts), COUNT(views), and the last step reached
3	Final SELECT	Compute time_to_convert; emit converters and non-converters uniformly

The pipeline emits one row per user, tagged with either 'CONVERTED' (checkout reached) or the last step they reached before dropping off. Downstream Sankey / attribution reports read this shape directly.

Output:

user_id	signup_ts	checkout_ts	time_to_convert_min	view_count	drop_off_step
u1	09:00	09:12	12	2	CONVERTED
u2	10:00	NULL	NULL	1	VIEW
u3	11:00	NULL	NULL	0	SIGNUP_UNMATCHED

Why this works — concept by concept:

ALL ROWS PER MATCH WITH UNMATCHED ROWS drives the whole report — one MR pass gives us every event enriched with match_id + matched_step. Matched rows drive conversion metrics; unmatched rows drive drop-off attribution.
CLASSIFIER() as a step-level label — projecting CLASSIFIER() into a matched_step column means downstream CASE-WHEN aggregates can pick out specific step timestamps and counts without joining back to the raw event stream.
CASE ladder to derive drop_off_step — the falling-through CASE ordered from CHECKOUT down to SIGNUP finds the deepest step reached. Rows that never signed up bubble to 'NO_SIGNUP'; rows that signed up but never entered the pattern (say, no view_product) show 'SIGNUP_UNMATCHED'.
Unified converters + non-converters — the same rowset carries both populations. Downstream funnel dashboards filter or aggregate as needed without another query.
Cost — one MR pass (O(N log N) per partition for the sort + O(N) state-machine scan) + one GROUP BY per user (O(N)) + one final SELECT (O(users)). On a 1B-event / 20M-user warehouse, the pipeline runs in single-digit minutes on a well-sized Snowflake medium warehouse. Nightly report cadence is easy.

SQL
Topic — user funnel analysis
User funnel analysis problems

Practice →

SQL Topic — window functions Window function drills

Practice →

5. Anomaly detection & dialect matrix

`oracle match_recognize` and `snowflake match_recognize` shine on anomalies — V-shape / W-shape reversals, fraud bursts, and the streaming-vs-batch dialect matrix

The mental model in one line: anomaly detection with MATCH_RECOGNIZE reads "match a shape defined by consecutive DOWN then UP transitions (V-shape), or DOWN then UP then DOWN then UP (W-shape / double bottom), or a burst of N spend events inside a time window (fraud)" — expressed as PATTERN (STRT DOWN+ UP+) or PATTERN (SPEND{5,}) with DEFINE using PREV / FIRST / LAST predicates. Once you say "PATTERN + PREV / FIRST — that's the anomaly engine," the interview surface reduces to a shape-to-PATTERN translation.

$Visual diagram of MATCH_RECOGNIZE anomaly detection and the dialect support matrix — left a stock V-shape / W-shape anomaly with the DOWN+ UP+ pattern annotated on a mini line chart, a small burst-fraud panel with PATTERN (SPEND{5,}) IN 60 SECONDS; right a 7-column dialect card comparing Oracle, Snowflake, Flink SQL, Trino, RisingWave, Postgres, BigQuery MATCH_RECOGNIZE support and the gaps-and-islands fallback; on a light PipeCode card.$

The classic V-shape reversal — Oracle's canonical example.

-- V-shape: N down candles followed by N up candles
PATTERN (STRT DOWN+ UP+)
DEFINE
  STRT AS TRUE,
  DOWN AS price < PREV(price),
  UP   AS price > PREV(price)

STRT is the anchor — matches any row (the row before the first DOWN).
DOWN+ — one or more consecutive rows where the price fell below the previous row's price.
UP+ — one or more consecutive rows where the price rose above the previous row's price.
Match = anchor + downtrend + uptrend = a V-shape reversal.

W-shape / double bottom.

PATTERN (STRT DOWN+ UP+ DOWN+ UP+)
DEFINE
  STRT AS TRUE,
  DOWN AS price < PREV(price),
  UP   AS price > PREV(price)

Twice the V-shape — two consecutive V-shapes make a W.
Common in technical analysis for "double bottom" reversal setup.

Fraud burst — PATTERN (SPEND{5,}) inside 60 seconds.

PATTERN (SPEND{5,})
DEFINE
  SPEND AS event_type = 'spend'
       AND event_time - FIRST(SPEND.event_time) <= INTERVAL '60' SECOND

SPEND{5,} — five or more SPEND events in a row.
DEFINE constrains that all SPEND events must occur within 60 seconds of the first — a "5+ transactions in 1 minute" burst.
Emit the boundaries + total amount + count. Downstream sends alerts.

Streaming CEP — Flink SQL / RisingWave.

Flink SQL exposes MATCH_RECOGNIZE with streaming semantics via its CEP (Complex Event Processing) library. Rows arrive continuously; the state machine tracks partial matches and emits the moment a match completes.
Watermark handling — Flink's watermark tells the pattern engine when it's safe to close a match. A pattern like PATTERN (A B{3,}) only emits after the watermark has advanced past the last matched row.
RisingWave ships the same clause with streaming semantics — MR runs continuously, matches emit as they complete.
The batch semantics on Oracle / Snowflake / Trino are a snapshot of the same engine — same pattern, all data at once.

Dialect matrix (as of 2026).

Engine	MATCH_RECOGNIZE	PERMUTE	AFTER MATCH SKIP variants	Streaming	Notes
Oracle 12c+	YES	YES	ALL	Batch	First to ship, 2013; the reference implementation
Snowflake	YES	NO (as of 2026)	Most	Batch	GA since 2021; the most-used commercial MR
Flink SQL	YES	YES	Most	Streaming (CEP)	Watermark-driven; emits on match completion
Trino / Presto	YES	YES	ALL	Batch	Since Trino 361; used by Athena / Dremio
RisingWave	YES	YES	Most	Streaming	Since 1.7; PostgreSQL-compatible streaming DB
Postgres 16	NO	—	—	—	No planned support; use LAG + SUM(new_group) OVER
BigQuery	NO	—	—	—	No planned support; use LAG + SUM
SQL Server 2022	NO	—	—	—	No planned support; use LAG + SUM
MySQL 8	NO	—	—	—	No planned support; use LAG + SUM
Redshift	NO	—	—	—	No planned support; use LAG + SUM
Databricks SQL	NO	—	—	—	No planned support (as of 2026); use LAG + SUM

Portable fallback — LAG + SUM(new_group) OVER.

The universal fallback for MR — LAG for previous-row lookups, CASE to flag change points, SUM OVER to cumulative-sum into a run id, then GROUP BY run id to aggregate.
Same computational complexity — O(N log N) per partition for the sort + O(N) for the CASE + SUM pass.
Longer code — 20-40 lines vs 10-15 for MR. The gap grows with pattern complexity.

Trino / Presto specifics.

Trino ships full ANSI MR since 361. AWS Athena inherits it from Trino. Dremio, Ahana, and Starburst all use the Trino MR implementation.
Watch for the SEEK variants — Trino's PATTERN parser is stricter than Oracle's; unquoted keywords may collide.

RisingWave / Flink specifics.

Streaming MR requires an ORDER BY on an event-time attribute with a defined watermark. Without a watermark, the pattern engine can't decide when to close a match.
Late data — rows arriving after the watermark are dropped (Flink) or reported to a side output (RisingWave configurable).
Match emission — Flink emits on match completion; RisingWave has an option to emit progressive updates.

Common sql pattern detection interview probes.

"Which engines ship MR?" — Oracle 12c+, Snowflake, Flink SQL, Trino / Presto, RisingWave. Not Postgres, BigQuery, SQL Server, MySQL, Redshift, Databricks SQL.
"Which engine has streaming MR?" — Flink SQL and RisingWave.
"Explain the V-shape PATTERN." — anchor + DOWN+ + UP+; DEFINE DOWN AS price < PREV(price), UP AS price > PREV(price).
"How do you detect a fraud burst?" — PATTERN (SPEND{5,}) + DEFINE using event_time - FIRST(SPEND.event_time) <= INTERVAL '60' SECOND.
"What's the portable fallback?" — LAG + CASE + SUM(new_group) OVER + GROUP BY run_id.

Worked example — V-shape reversal on Oracle / Snowflake

Detailed explanation. The classic Oracle documentation example: find every V-shape reversal in a stock price series. Emit the run's boundary timestamps and prices.

Question. Given ticker(symbol, ts, price), use MR to find every V-shape reversal per symbol (at least one down candle, at least one up candle after the trough).

Input.

symbol	ts	price
AAPL	1	100
AAPL	2	98
AAPL	3	95
AAPL	4	93
AAPL	5	95
AAPL	6	100
AAPL	7	98
AAPL	8	102

Code.

SELECT *
FROM ticker
MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY ts
  MEASURES
    STRT.ts     AS start_ts,
    LAST(UP.ts) AS end_ts,
    MIN(DOWN.price) AS trough_price,
    LAST(UP.price)  AS rebound_price
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (STRT DOWN+ UP+)
  DEFINE
    DOWN AS price < PREV(price),
    UP   AS price > PREV(price)
);

Step-by-step explanation.

PARTITION BY symbol — each stock's series is matched independently.
PATTERN (STRT DOWN+ UP+) — anchor + one-or-more downs + one-or-more ups. Matches a V-shape.
DEFINE DOWN AS price < PREV(price) — a row is DOWN if its price is less than the previous row's. PREV(price) on the first row is NULL, so DOWN can't match the first row.
DEFINE UP AS price > PREV(price) — a row is UP if its price is greater than the previous row's.
MEASURES — STRT.ts is the anchor's timestamp; LAST(UP.ts) is the reversal's end; MIN(DOWN.price) is the trough; LAST(UP.price) is the rebound peak.
Matches: row 1 (STRT) + rows 2-4 (DOWN+) + rows 5-6 (UP+) = one V-shape. Then row 7 → row 8 forms another V-shape starting from row 6 (STRT at 6, DOWN at 7, UP at 8).

Output.

symbol	start_ts	end_ts	trough_price	rebound_price
AAPL	1	6	93	100
AAPL	6	8	98	102

Rule of thumb. V-shape reversal = PATTERN (STRT DOWN+ UP+) + DEFINE DOWN / UP using PREV(price). The classic Oracle example — the query every financial-data interview loves.

Worked example — fraud burst detection on Flink SQL

Detailed explanation. Flink SQL exposes MR as a streaming CEP operator. The fraud burst pattern — five or more spend events inside 60 seconds — matches as soon as the fifth spend arrives.

Question. On Flink SQL, given a streaming spend_events(user_id, event_time, amount) table with a defined watermark, use MR to emit an alert every time a user has 5+ spend events within 60 seconds.

Input. (Streaming — no snapshot table. Assume events arrive at 09:00:00, 09:00:05, 09:00:10, 09:00:20, 09:00:30, 09:00:45 for u1.)

Code.

SELECT *
FROM spend_events
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY event_time
  MEASURES
    FIRST(SPEND.event_time) AS burst_start,
    LAST(SPEND.event_time)  AS burst_end,
    COUNT(*)                AS spend_count,
    SUM(SPEND.amount)       AS total_amount
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (SPEND{5,})
  DEFINE
    SPEND AS event_time - FIRST(SPEND.event_time) <= INTERVAL '60' SECOND
);

Step-by-step explanation.

PATTERN (SPEND{5,}) — five or more consecutive SPEND rows.
DEFINE SPEND AS event_time - FIRST(SPEND.event_time) <= INTERVAL '60' SECOND — every SPEND row must be within 60 seconds of the first SPEND in the current match. The moment a row exceeds the 60-second window, the match terminates.
Because Flink uses streaming semantics, the state machine holds partial matches while waiting for more rows. It emits the moment the fifth row arrives and satisfies the pattern.
Watermark handling — Flink advances the watermark based on the source event-time attribute. A match is only finalised once the watermark has moved past the last matched row (guaranteeing no late data can extend the match).
AFTER MATCH SKIP PAST LAST ROW — after emitting a burst, resume matching at the next row. A user who fires 10 spends inside 60 seconds emits one burst (the first 5+), then may emit another one starting from the 6th event.

Output. (One row per completed burst.)

user_id	burst_start	burst_end	spend_count	total_amount
u1	09:00:00	09:00:30	5	250

Rule of thumb. Streaming fraud bursts on Flink SQL = PATTERN (SPEND{n,}) + DEFINE using a FIRST(SPEND.event_time) time-window constraint. The engine emits the moment the pattern completes; the watermark guards against late data.

Worked example — portable LAG + SUM fallback on BigQuery

Detailed explanation. BigQuery doesn't ship MR. For the same V-shape reversal, write the portable LAG + SUM(new_group) OVER version. Longer, but universally supported.

Question. Rewrite the V-shape reversal query for BigQuery (no MATCH_RECOGNIZE).

Input. (Same as V-shape example.)

Code.

WITH flagged AS (
  SELECT
    symbol,
    ts,
    price,
    CASE
      WHEN price < LAG(price) OVER (PARTITION BY symbol ORDER BY ts) THEN 'DOWN'
      WHEN price > LAG(price) OVER (PARTITION BY symbol ORDER BY ts) THEN 'UP'
      ELSE 'FLAT'
    END AS direction
  FROM ticker
),
runs AS (
  SELECT
    symbol,
    ts,
    price,
    direction,
    -- Start a new run when direction changes
    SUM(CASE
          WHEN direction <> LAG(direction) OVER (PARTITION BY symbol ORDER BY ts)
            OR LAG(direction) OVER (PARTITION BY symbol ORDER BY ts) IS NULL
          THEN 1 ELSE 0
        END) OVER (PARTITION BY symbol ORDER BY ts) AS run_id
  FROM flagged
),
run_summary AS (
  SELECT
    symbol,
    run_id,
    direction,
    MIN(ts)    AS run_start,
    MAX(ts)    AS run_end,
    MIN(price) AS run_min_price,
    MAX(price) AS run_max_price,
    COUNT(*)   AS run_length
  FROM runs
  GROUP BY symbol, run_id, direction
),
paired AS (
  SELECT
    symbol,
    run_start   AS down_start,
    run_end     AS down_end,
    run_min_price AS trough_price,
    LEAD(direction) OVER (PARTITION BY symbol ORDER BY run_start) AS next_dir,
    LEAD(run_start) OVER (PARTITION BY symbol ORDER BY run_start) AS up_start,
    LEAD(run_end)   OVER (PARTITION BY symbol ORDER BY run_start) AS up_end,
    LEAD(run_max_price) OVER (PARTITION BY symbol ORDER BY run_start) AS rebound_price
  FROM run_summary
  WHERE direction = 'DOWN'
)
SELECT
  symbol,
  down_start AS start_ts,
  up_end     AS end_ts,
  trough_price,
  rebound_price
FROM paired
WHERE next_dir = 'UP'
ORDER BY symbol, start_ts;

Step-by-step explanation.

flagged CTE — label each row with direction = DOWN / UP / FLAT relative to the previous row.
runs CTE — cumulative-sum a "direction changed" flag to produce run_id. Rows inside the same run share the id.
run_summary CTE — collapse each run into a summary row with direction, boundaries, and min / max prices.
paired CTE — pair consecutive runs by using LEAD to fetch the next run's direction and boundaries. A V-shape is a DOWN run immediately followed by an UP run.
Final SELECT — filter to direction = 'DOWN' AND next_dir = 'UP' and project the boundary columns.

Output. (Same as the Oracle version.)

Rule of thumb. The portable fallback for V-shape reversal is four CTEs + one LEAD chain. Cost is O(N log N) per partition — same as MR. The code is longer; the query is universally supported.

Senior interview question on dialect-portable anomaly detection

A senior interviewer might ask: "Your company runs analytics on Snowflake (batch, MR-capable) and BigQuery (batch, no MR). Design the anomaly-detection pipeline — V-shape reversals on a 100M-row daily price series — that produces the same output on both warehouses. Talk through the architecture and why you'd not use MR even on Snowflake."

Solution Using dbt macro with dialect dispatch + portable core

-- macros/detect_v_shape.sql — dispatches on target dialect
{% macro detect_v_shape(prices_relation, symbol_col='symbol', ts_col='ts', price_col='price') %}
  {%- if target.type == 'snowflake' -%}
    -- Snowflake: use MATCH_RECOGNIZE for readability + native plan
    SELECT
      {{ symbol_col }},
      start_ts,
      end_ts,
      trough_price,
      rebound_price
    FROM {{ prices_relation }}
    MATCH_RECOGNIZE (
      PARTITION BY {{ symbol_col }}
      ORDER BY {{ ts_col }}
      MEASURES
        STRT.{{ ts_col }}          AS start_ts,
        LAST(UP.{{ ts_col }})      AS end_ts,
        MIN(DOWN.{{ price_col }})  AS trough_price,
        LAST(UP.{{ price_col }})   AS rebound_price
      ONE ROW PER MATCH
      AFTER MATCH SKIP PAST LAST ROW
      PATTERN (STRT DOWN+ UP+)
      DEFINE
        DOWN AS {{ price_col }} < PREV({{ price_col }}),
        UP   AS {{ price_col }} > PREV({{ price_col }})
    )
  {%- else -%}
    -- Portable: LAG + SUM(new_group) OVER for BigQuery / Postgres / SQL Server / MySQL / Redshift / Databricks
    WITH flagged AS (
      SELECT
        {{ symbol_col }},
        {{ ts_col }},
        {{ price_col }},
        CASE
          WHEN {{ price_col }} < LAG({{ price_col }}) OVER (PARTITION BY {{ symbol_col }} ORDER BY {{ ts_col }}) THEN 'DOWN'
          WHEN {{ price_col }} > LAG({{ price_col }}) OVER (PARTITION BY {{ symbol_col }} ORDER BY {{ ts_col }}) THEN 'UP'
          ELSE 'FLAT'
        END AS direction
      FROM {{ prices_relation }}
    ),
    runs AS (
      SELECT
        *,
        SUM(CASE
              WHEN direction <> LAG(direction) OVER (PARTITION BY {{ symbol_col }} ORDER BY {{ ts_col }})
                OR LAG(direction) OVER (PARTITION BY {{ symbol_col }} ORDER BY {{ ts_col }}) IS NULL
              THEN 1 ELSE 0
            END) OVER (PARTITION BY {{ symbol_col }} ORDER BY {{ ts_col }}) AS run_id
      FROM flagged
    ),
    run_summary AS (
      SELECT
        {{ symbol_col }},
        run_id,
        direction,
        MIN({{ ts_col }})    AS run_start,
        MAX({{ ts_col }})    AS run_end,
        MIN({{ price_col }}) AS run_min_price,
        MAX({{ price_col }}) AS run_max_price
      FROM runs
      GROUP BY {{ symbol_col }}, run_id, direction
    ),
    paired AS (
      SELECT
        {{ symbol_col }},
        run_start   AS start_ts,
        run_min_price AS trough_price,
        LEAD(direction) OVER (PARTITION BY {{ symbol_col }} ORDER BY run_start) AS next_dir,
        LEAD(run_end)   OVER (PARTITION BY {{ symbol_col }} ORDER BY run_start) AS end_ts,
        LEAD(run_max_price) OVER (PARTITION BY {{ symbol_col }} ORDER BY run_start) AS rebound_price
      FROM run_summary
      WHERE direction = 'DOWN'
    )
    SELECT {{ symbol_col }}, start_ts, end_ts, trough_price, rebound_price
    FROM paired
    WHERE next_dir = 'UP'
  {%- endif -%}
{% endmacro %}

Step-by-step trace.

Dispatch branch	What runs	Why
Snowflake	MATCH_RECOGNIZE PATTERN (STRT DOWN+ UP+)	Native MR shorter, uses Snowflake's MR operator
BigQuery / Postgres / SQL Server / MySQL / Redshift / Databricks	LAG + SUM(new_group) OVER + LEAD pair	Portable fallback; universal coverage
Both	Same output columns (symbol, start_ts, end_ts, trough_price, rebound_price)	Downstream stays warehouse-agnostic

The macro dispatches on target.type at compile time. Snowflake models get the native MR query; every other target gets the portable version. Both branches produce the same output schema, so downstream consumers are unchanged.

Output:

symbol	start_ts	end_ts	trough_price	rebound_price
AAPL	2026-07-01	2026-07-05	178	195
AAPL	2026-07-08	2026-07-12	190	205
MSFT	2026-07-02	2026-07-06	415	430

Why this works — concept by concept:

Compile-time dispatch, not runtime dispatch — dbt evaluates the Jinja block at compile time, so the emitted SQL is either a MR query or a LAG-based query — never a runtime branch. Zero overhead at query time.
Same output columns across branches — both branches emit (symbol, start_ts, end_ts, trough_price, rebound_price). Downstream dashboards, reports, and joins don't need to know which warehouse produced the data.
MR is not always the right choice even where supported — some teams stick with the portable version even on Snowflake so the code review, the debug story, and the on-call ramp are the same everywhere. This macro gives the choice: opt into MR by adding a Snowflake branch, or ship portable everywhere.
Testability with the same fixture — write dbt tests against fixture data and run them on both warehouses. Same test file, same expected output — the macro guarantees output equivalence.
Cost — Snowflake's MR pass is O(N log N) per partition; the portable LAG + SUM pass is O(N log N) per partition. Same complexity class. On a 100M-row daily price table with 5000 symbols on Snowflake medium, both run in single-digit seconds; on BigQuery with the portable branch, similar (both use columnar storage + parallel window function operators). The dispatch macro adds zero runtime cost — the win is in maintainability, not performance.

SQL
Topic — window functions (SQL)
Window functions in SQL — full library

Practice →

SQL
Topic — SQL
SQL problem library — 450+ DE-focused questions

Practice →

Cheat sheet — MATCH_RECOGNIZE recipe list

Seven-clause skeleton. PARTITION BY (scope), ORDER BY (row order), MEASURES (per-match payload), ONE ROW PER MATCH vs ALL ROWS PER MATCH (output shape), AFTER MATCH SKIP … (overlap policy), PATTERN (...) (regex over row labels), DEFINE <label> AS <predicate> (label semantics). Memorise the order; every MR answer fills these seven slots.
PATTERN is a regex over row labels. Quantifiers: * (zero or more), + (one or more), ? (zero or one), {n} (exactly n), {n,m} (n to m). Alternation |. Grouping (...). Reluctant quantifiers +?, *?, ?? for shortest match.
DEFINE assigns predicates to labels. Predicates can reference PREV(col), NEXT(col), FIRST(label.col), LAST(label.col), aggregates over matched labels, and the current row's columns. Any label used in PATTERN but not defined defaults to TRUE.
PARTITION BY is per-entity scoping. Sessionization partitions by user_id; funnels partition by user_id; anomaly detection partitions by symbol. Omitting the partition matches across entity boundaries — a common wrong-answer bug.
ORDER BY is mandatory. Establishes what "previous" and "next" mean. Almost always the event time or a monotonic sequence. Missing ORDER BY is a compilation error.
ONE ROW PER MATCH for summary; ALL ROWS PER MATCH for enrichment. Session summaries use ONE ROW; per-event enrichment inside a match uses ALL ROWS. ALL ROWS PER MATCH WITH UNMATCHED ROWS also emits unmatched rows — critical for funnel drop-off attribution.
AFTER MATCH SKIP variants. SKIP PAST LAST ROW (default; no overlap; right for sessions, funnels, anomalies), SKIP TO NEXT ROW (allow overlap; right for "every possible occurrence" analytics), SKIP TO FIRST <label>, SKIP TO LAST <label> (skip to a labelled row inside the match).
Sessionization primitive. PATTERN (A B*) + DEFINE B AS DATEDIFF('minute', LAG(event_time), event_time) <= 30. One anchor + zero-or-more continuations inside the inactivity threshold. One match per session.
Funnel primitive. PATTERN (SIGNUP VIEW+ CART CHECKOUT) + one DEFINE per step. Strict left-to-right ordering; quantifiers on repeated steps. Encodes the funnel as regex; MR emits one row per completing user.
V-shape / W-shape anomaly primitive. PATTERN (STRT DOWN+ UP+) for V; PATTERN (STRT DOWN+ UP+ DOWN+ UP+) for W. DEFINE DOWN AS price < PREV(price), UP AS price > PREV(price). The classic Oracle documentation example — every financial-data interview loves this shape.
Fraud burst primitive. PATTERN (SPEND{5,}) + DEFINE SPEND AS event_time - FIRST(SPEND.event_time) <= INTERVAL '60' SECOND. Five or more spend events inside a 60-second window from the first.
CLASSIFIER() and MATCH_NUMBER(). CLASSIFIER() returns the label the current row was classified into; MATCH_NUMBER() is a monotonically-increasing per-partition match id. Project both into MEASURES when using ALL ROWS PER MATCH for downstream analytics.
PERMUTE for unordered patterns. PATTERN (PERMUTE(A, B, C)) matches A, B, C in any order — the engine expands to N! alternations under the hood. Oracle / Trino / Flink support PERMUTE; Snowflake requires manual alternation as of 2026.
WITH UNMATCHED ROWS for drop-off attribution. ALL ROWS PER MATCH WITH UNMATCHED ROWS emits both matched and unmatched rows. Unmatched rows have MATCH_NUMBER() = NULL and CLASSIFIER() = NULL — filter to those for drop-off events.
MR dialect matrix. YES: Oracle 12c+, Snowflake, Flink SQL (streaming CEP), Trino / Presto, RisingWave. NO: Postgres, BigQuery, SQL Server, MySQL, Redshift, Databricks SQL (as of 2026). Streaming semantics only on Flink SQL and RisingWave.
Portable fallback — LAG + SUM(new_group) OVER. For non-MR warehouses: LAG(col) for previous value, CASE WHEN col <> LAG(col) THEN 1 ELSE 0 END to flag change points, SUM(is_new) OVER (PARTITION BY entity ORDER BY ts) for run id, then GROUP BY run_id to aggregate. Same computational complexity, longer code.
Dbt macro dispatch pattern. Wrap MR in a Jinja macro with {% if target.type == 'snowflake' %} to emit the MR variant on Snowflake and the portable fallback everywhere else. Downstream consumers see identical output columns.
Flink streaming CEP. Same MR syntax, watermark-driven. Match emits when the watermark advances past the last matched row. Late data is dropped or routed to a side output. Great for real-time fraud detection and low-latency alerting.
When to use MR vs portable. MR when the pattern is genuinely multi-row and regex-like (funnels, V/W anomalies, PERMUTE variants) and the warehouse supports it. Portable for simple RLE, sessionization on multi-warehouse projects, and any codebase where dialect drift is a maintenance burden.
Cost model. MR compiles to sort + state-machine scan — O(N log N) per partition for the sort + O(N) for the state machine. Portable LAG + SUM is sort + two window passes + aggregate — same O(N log N) per partition. On billion-row inputs, both run in single-digit minutes on modern warehouses.

Frequently asked questions

What is SQL MATCH_RECOGNIZE and when do you use it?

sql match_recognize is the ANSI SQL/2016 row pattern matching clause — a mini regex engine that runs over ordered rows inside a partition. The seven-clause skeleton is PARTITION BY entity ORDER BY time MEASURES ... PATTERN (regex-over-labels) DEFINE label AS predicate ONE ROW PER MATCH AFTER MATCH SKIP …. Reach for it whenever you need to detect an ordered row sequence — sessionization (PATTERN (A B*) with an inactivity DEFINE), funnel conversion (PATTERN (SIGNUP VIEW+ CART CHECKOUT)), V-shape / W-shape stock anomaly (PATTERN (STRT DOWN+ UP+)), fraud bursts (PATTERN (SPEND{5,}) inside a 60-second window), or any streaming CEP task where a Flink SQL / RisingWave watermark closes matches as they complete. MR ships on Oracle 12c+, Snowflake, Flink SQL, Trino / Presto, and RisingWave; Postgres, BigQuery, SQL Server, MySQL, Redshift, and Databricks SQL don't ship it yet, so multi-warehouse code defaults to the portable LAG + SUM(new_group) OVER fallback.

How does MATCH_RECOGNIZE compare to gaps-and-islands with LAG and SUM?

Both express the same class of problems — sessionization, streaks, RLE, funnels, anomaly detection — but MATCH_RECOGNIZE reads as a regex over labelled row classes while gaps-and-islands cascades LAG, CASE, and SUM(is_new_group) OVER window functions. For simple RLE and sessionization, the two produce identical physical plans on Snowflake and roughly equivalent plans elsewhere; MR just reads more clearly once your team knows the anatomy. For complex multi-row patterns (V-shape / W-shape reversals, PATTERN (SIGNUP VIEW+ CART CHECKOUT) funnels, PERMUTE variants, fraud bursts inside a time window) MR is dramatically shorter — 10-15 lines vs 30-40 lines portable, with the gap growing as the pattern gets more regex-like. The strategic answer for a multi-warehouse codebase is a dbt macro that dispatches on target.type: emit MR on Snowflake / Oracle / Trino / Flink / RisingWave, emit the portable LAG + SUM version everywhere else, present the same output columns to downstream consumers.

What are the seven clauses of MATCH_RECOGNIZE?

The seven slots are, in canonical reading order: (1) PARTITION BY <cols> — scope the pattern per entity (user, symbol, device); (2) ORDER BY <cols> — establish row order (event_time or a monotonic sequence); (3) MEASURES <exprs> — per-match output columns using FIRST(label.col), LAST(label.col), COUNT(*), MATCH_NUMBER(), and CLASSIFIER(); (4) ONE ROW PER MATCH for summary output vs ALL ROWS PER MATCH (or ALL ROWS PER MATCH WITH UNMATCHED ROWS) for per-event enrichment; (5) AFTER MATCH SKIP … — the overlap policy (PAST LAST ROW for no overlap, TO NEXT ROW to allow overlap, TO FIRST/LAST <label> for label-anchored skips); (6) PATTERN (...) — the regex over row labels with quantifiers * + ? {n} {n,m}, alternation |, grouping (...), and PERMUTE(...); (7) DEFINE <label> AS <predicate> — the boolean predicate for each label, allowed to reference PREV, NEXT, FIRST, LAST, and aggregates over already-matched labels. Say them in order without prompting and you're already in the top 20% of MR fluency.

How do you write a funnel-conversion query with MATCH_RECOGNIZE?

funnel analysis sql with MATCH_RECOGNIZE reads like the funnel diagram itself: PATTERN (SIGNUP VIEW+ CART CHECKOUT) encodes the strict left-to-right step ordering, DEFINE SIGNUP AS event_type = 'signup', DEFINE VIEW AS event_type = 'view_product', DEFINE CART AS event_type = 'add_to_cart', DEFINE CHECKOUT AS event_type = 'checkout' assigns each label its event-type predicate, and MEASURES emits FIRST(SIGNUP.event_time) AS signup_ts, LAST(CHECKOUT.event_time) AS checkout_ts, DATEDIFF('minute', ...) AS time_to_convert_min, and COUNT(VIEW.*) AS view_count. Quantifiers control repetition (VIEW+ for one-or-more product views, CART? for optional add-to-cart, VIEW{2,} for at least two views). PERMUTE(A, B, C) handles unordered onboarding funnels on Oracle / Trino / Flink. ALL ROWS PER MATCH WITH UNMATCHED ROWS exposes drop-off events with MATCH_NUMBER() = NULL for the standard Sankey funnel report — one MR pass, both converters and drop-off attribution.

How do you detect V-shape or W-shape anomalies in SQL?

The classic oracle match_recognize documentation example: a V-shape stock reversal is PATTERN (STRT DOWN+ UP+) with DEFINE DOWN AS price < PREV(price) and DEFINE UP AS price > PREV(price) — one anchor row + one-or-more consecutive down candles + one-or-more consecutive up candles. STRT.ts is the reversal's start; LAST(UP.ts) is its end; MIN(DOWN.price) is the trough; LAST(UP.price) is the rebound. W-shape / double-bottom extends to PATTERN (STRT DOWN+ UP+ DOWN+ UP+) — two consecutive V-shapes in a row. The same pattern engine runs the fraud-burst detection — PATTERN (SPEND{5,}) with DEFINE SPEND AS event_time - FIRST(SPEND.event_time) <= INTERVAL '60' SECOND for a "5+ spends inside 60 seconds" alert. On flink sql pattern and RisingWave, the same syntax runs continuously against a live event stream with watermark-driven match completion — the same anomaly recipe, streaming semantics. Portable fallback for warehouses without MR: LAG + CASE (DOWN / UP / FLAT direction label) + SUM(is_new_group) OVER for run id + LEAD to pair consecutive runs.

Which SQL engines support MATCH_RECOGNIZE in 2026?

As of 2026, MATCH_RECOGNIZE ships natively on Oracle 12c+ (first to market in 2013, the reference implementation), Snowflake (batch, GA since 2021), Flink SQL (streaming CEP semantics with watermark-driven match completion), Trino / Presto (batch, since Trino 361 — inherited by AWS Athena, Dremio, Starburst, Ahana), and RisingWave (streaming, since 1.7, PostgreSQL-compatible). No MR support on Postgres 16, BigQuery, SQL Server 2022, MySQL 8, Redshift, or Databricks SQL — for those engines, use the portable LAG + SUM(new_group) OVER gaps-and-islands fallback. Streaming semantics only on Flink SQL and RisingWave; the other three (Oracle, Snowflake, Trino) are batch. PERMUTE is supported on Oracle, Trino, and Flink but not on Snowflake as of 2026. For a multi-warehouse codebase, wrap MR in a dbt macro with {% if target.type == 'snowflake' or target.type == 'trino' %} dispatch and fall back to the portable pattern everywhere else — same output columns, same tests, one macro.

Practice on PipeCode

Drill the pattern-matching practice library → for MATCH_RECOGNIZE anatomy, PATTERN quantifiers, DEFINE predicates, and AFTER MATCH SKIP semantics across Oracle, Snowflake, Trino, and Flink dialects.
Rehearse on gaps-and-islands problems → — the portable LAG + SUM(new_group) OVER fallback that every non-MR warehouse (Postgres, BigQuery, SQL Server, MySQL, Redshift, Databricks) requires.
Sharpen streaming drills → for the Flink SQL and RisingWave CEP variants — watermark-driven pattern completion, low-latency emission, side outputs for late data.
Push the difficulty ceiling with medium streaming problems → for real-time fraud detection, sessionization with watermarks, and multi-step funnel tracking.
Practise window-function drills → — the LAG / LEAD / FIRST_VALUE / LAST_VALUE primitives that both MR predicates and portable fallbacks lean on.
Sharpen the dialect-portable half with SQL window function problems → for Postgres, Snowflake, BigQuery, SQL Server, MySQL where portable is the default.
Layer user-funnel-analysis drills → for PATTERN (SIGNUP VIEW+ CART CHECKOUT), drop-off attribution via WITH UNMATCHED ROWS, and PERMUTE variants for unordered onboarding steps.
Practise regular-expression drills → — MATCH_RECOGNIZE's PATTERN clause is regex over rows; fluency with regex quantifiers transfers directly.
For general SQL sharpening, work through the SQL problem library → which contains 450+ DE-focused questions.
For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `sql match_recognize` recipe above ships with hands-on practice rooms where you write the seven-clause anatomy, wire `PATTERN (A B*)` sessionization, chase the `PATTERN (SIGNUP VIEW+ CART CHECKOUT)` funnel with WITH UNMATCHED ROWS drop-off attribution, detect V-shape and W-shape reversals, rehearse the Flink SQL streaming CEP variant, and translate to the portable LAG + SUM(new_group) OVER fallback against real graded inputs. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `row pattern matching sql` answer holds up under a senior interviewer's depth probes.

Practice pattern matching now →
User funnel analysis drills →

1. Why row pattern matching matters in 2026

The "regex over ordered rows" family — sessionization, funnels, anomalies, fraud bursts, and every other row-sequence question a senior interviewer might ask

Worked example — the "consecutive down candles" anomaly starter

Worked example — the LAG + SUM portable equivalent

Worked example — engine dialect quickstart

Senior interview question on the row-pattern mental model

Solution Using the seven-clause MATCH_RECOGNIZE skeleton

2. MATCH_RECOGNIZE anatomy

The seven-clause skeleton — PARTITION BY, ORDER BY, MEASURES, ONE ROW / ALL ROWS PER MATCH, AFTER MATCH SKIP, PATTERN, DEFINE

Worked example — consecutive-price-drop with ONE ROW PER MATCH

Worked example — same pattern with ALL ROWS PER MATCH + CLASSIFIER

Worked example — AFTER MATCH SKIP TO NEXT ROW for overlapping matches

Senior interview question on anatomy fluency

Solution Using the seven-clause skeleton with first-match selection

3. Sessionization with MR

sql pattern detection for event streams — the PATTERN ((A B*)+) sessionization primitive with per-user partitioning and inactivity DEFINE

Worked example — 30-minute sessionization on Snowflake

Worked example — the LAG + SUM portable equivalent

Worked example — MR sessionization with explicit sign-out

Senior interview question on MR sessionization at scale

Solution Using MR sessionization + per-session aggregate + forward-fill

4. Funnel & conversion patterns

funnel analysis sql reads like a regex — PATTERN (SIGNUP VIEW+ CART CHECKOUT) collapses a four-step funnel into one MR block

Worked example — signup → view → cart → checkout funnel

Worked example — funnel with drop-off attribution

Worked example — PERMUTE for onboarding step order

Senior interview question on multi-step funnel attribution

Solution Using MR + WITH UNMATCHED ROWS + last-step aggregate

5. Anomaly detection & dialect matrix

oracle match_recognize and snowflake match_recognize shine on anomalies — V-shape / W-shape reversals, fraud bursts, and the streaming-vs-batch dialect matrix

Worked example — V-shape reversal on Oracle / Snowflake

Worked example — fraud burst detection on Flink SQL

Worked example — portable LAG + SUM fallback on BigQuery

Senior interview question on dialect-portable anomaly detection

Solution Using dbt macro with dialect dispatch + portable core

Cheat sheet — MATCH_RECOGNIZE recipe list

Frequently asked questions

What is SQL MATCH_RECOGNIZE and when do you use it?

How does MATCH_RECOGNIZE compare to gaps-and-islands with LAG and SUM?

What are the seven clauses of MATCH_RECOGNIZE?

How do you write a funnel-conversion query with MATCH_RECOGNIZE?

How do you detect V-shape or W-shape anomalies in SQL?

Which SQL engines support MATCH_RECOGNIZE in 2026?

Practice on PipeCode

`sql pattern detection` for event streams — the `PATTERN ((A B*)+)` sessionization primitive with per-user partitioning and inactivity DEFINE

`funnel analysis sql` reads like a regex — `PATTERN (SIGNUP VIEW+ CART CHECKOUT)` collapses a four-step funnel into one MR block

`oracle match_recognize` and `snowflake match_recognize` shine on anomalies — V-shape / W-shape reversals, fraud bursts, and the streaming-vs-batch dialect matrix