cswork / libraryOpen practice workspace

Articles

MATCH_RECOGNIZE Interview Questions: Event Patterns, Greedy Matches, and Skipped Rows

Practice MATCH_RECOGNIZE interview questions with event traces, greedy and reluctant matches, skip rules, unmatched rows, and clear output-grain checks.

Five failures in a row can produce one alert or three overlapping alerts with the same SQL pattern. A checkout sequence can end at the first payment or the second. In a MATCH_RECOGNIZE interview, the decisive answer explains the ordered input, the selected complete match, the restart position, and the output grain.

This article is for SQL and data-engineering candidates. The worked SQL uses Snowflake syntax. Official facts are attributed to database documentation; the fixtures, expected traces, and interview recommendations are original practice. No candidate reports establish how often employers ask these questions.

What does MATCH_RECOGNIZE actually recognize?

MATCH_RECOGNIZE searches ordered rows for a specified row pattern. Official Snowflake guidance describes partitioning related events, ordering them, assigning pattern variables through predicates, and reporting successful sequences. Its pattern-recognition introduction supplies the language foundation.

The useful interview distinction is that this is a sequence problem. A filter can identify every payment; it cannot by itself establish that a particular start and intervening additions form one checkout. Likewise, grouping a consecutive failure run is different from deciding which overlapping three-failure windows count as alerts.

Treat the answer as four decisions: which rows belong together, which order defines adjacency, what constitutes a complete match, and what one output row represents. Changing any decision can change the result without changing the source records. Explain those choices before optimizing a query.

Define the sequence before defining the symbols

Our checkout contract partitions by tenant_id, user_id and orders by event_ts, event_id. Event IDs are unique within each partition. The timestamp represents the agreed event clock; the ID is a declared tie-breaker. The output should summarize strict consecutive sequences, including their starting and ending event IDs.

These are exercise assumptions, not universal business rules. If event IDs do not express the intended order at tied timestamps, choosing them merely makes an arbitrary interpretation repeatable. Ask whether source sequence numbers or another business rule should determine order instead.

Do not filter unwanted event types away casually. Removing a cancellation could turn an interrupted checkout into an apparently consecutive one. If the requirement permits intervening events, model that permission explicitly. Input filtering and skipping after a successful match solve different problems.

Compare the neighboring gaps-and-islands explanation: it groups runs and sessions. Here, the central question is which complete patterns survive ambiguity and where another search starts.

Trace a checkout with overlapping predicates

Use this original seven-row fixture. All rows belong to tenant T1 and user U1; times increase with event ID.

Event IDEventTime
1START10:00
2ADD10:01
3PAY10:02
4PAY10:03
5START10:04
6NOISE10:05
7PAY10:06

For this teaching example, B deliberately accepts either ADD or PAY. C accepts PAY. A requires START. That overlap creates a meaningful quantifier choice; it is not a recommendation to treat payments as additions in a real checkout model.

SELECT *
FROM events
MATCH_RECOGNIZE (
  PARTITION BY tenant_id, user_id
  ORDER BY event_ts, event_id
  MEASURES
    MATCH_NUMBER() AS match_no,
    FIRST(A.event_id) AS start_id,
    LAST(C.event_id) AS end_id,
    COUNT(*) AS matched_rows
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (A B+ C)
  DEFINE
    A AS event_type = 'START',
    B AS event_type IN ('ADD', 'PAY'),
    C AS event_type = 'PAY'
)
ORDER BY tenant_id, user_id, match_no;

The expected greedy match starts at 1 and ends at 4, with four matched rows. B can initially consume rows 2, 3, and 4. That leaves no following PAY for C, so completing the pattern requires B to relinquish row 4. The selected assignment is A at 1, B at 2–3, C at 4.

The attempt beginning at 5 fails because NOISE at 6 cannot satisfy B. Row 7 does not rescue a strict consecutive pattern. The important answer includes that rejected attempt, rather than reporting only the successful sequence.

Does greedy mean the longest possible answer?

Official semantics: Snowflake quantifiers prefer more repetitions by default; adding ? makes a quantifier reluctant. Matching can backtrack to complete the pattern. See the syntax reference. Greedy is a preference inside pattern evaluation, not a promise that every row passing a predicate will appear in the final match.

Change only the fixture's pattern to A B+? C. B first accepts row 2, and C can accept row 3. The expected match is now 1–3, with three rows. Both answers satisfy their respective patterns. Neither tells you which interpretation the business wanted.

A candidate should narrate the attempt, the repetition choice, and the point where the remaining pattern succeeds. Saying “greedy takes everything” misses the terminal C requirement. Saying “reluctant means exactly one B” also fails: if completing C requires more B rows, the reluctant quantifier can expand.

For a follow-up, replace row 3 with ADD. Both variants now need C at 4. This small change separates understanding of preference from memorizing the first output. Predict both results before executing a query in the target engine.

Where does searching restart after a match?

Official Snowflake behavior: PAST LAST ROW restarts beyond the completed match; TO NEXT ROW restarts beyond its first row. A symbol-directed skip targets a matched occurrence and must advance. These choices are documented in AFTER MATCH SKIP.

Consider five failures, numbered 1 through 5, and the fixed pattern A A A, with A meaning failure. Under PAST LAST ROW, the first match is 1–3. Searching resumes at 4, where only two rows remain. Under TO NEXT ROW, matches are 1–3, 2–4, and 3–5.

Now change the pattern to A+. Greedy matching with PAST LAST ROW yields one match covering 1–5. With TO NEXT ROW, the successive matches cover 1–5, 2–5, 3–5, 4–5, and 5–5. The pattern and restart policy together explain why the same physical row can belong to multiple matches.

With reluctant A+?, each successful match contains one row, so both restart choices yield five single-row matches in this fixture. This is a counterexample to “TO NEXT ROW always creates overlaps.” It enables overlaps; the actual pattern must produce them.

Are skipped, unmatched, and excluded rows equivalent?

They describe different stages. Our original traces distinguish a row outside a successful match, a row unavailable as a new starting point under the restart policy, and a matched row intentionally omitted from emitted details.

Official Snowflake output options allow unmatched rows with ALL ROWS PER MATCH WITH UNMATCHED ROWS; pattern exclusions suppress matched output rows while retaining them in measure evaluation. Exclusions cannot be combined with that unmatched-row option. See the output clauses.

In the greedy checkout, ordinary detailed output would include events 1–4. If the pattern becomes A {- B+ -} C, details expose A at 1 and C at 4; B rows 2 and 3 still participated in the match. Counting the two displayed details would therefore answer a different question from counting the four participating rows.

Events 5–7 are unmatched under the baseline contract. Do not describe every absent row as “skipped,” then try to repair the result with a single clause. First identify whether the absence comes from input filtering, match failure, restart behavior, exclusion, or summary output.

How does output grain change measures?

The fixture's summary grain is one successful match. Its identifiers and total row count describe the completed checkout. A downstream join should preserve that grain, using partition identity and match identity together rather than assuming match number is globally unique.

Official Trino documentation also distinguishes running and final measure semantics and summary versus detailed output. Its MATCH_RECOGNIZE reference is useful comparative reading; dialect-specific functions and restrictions still require the target engine's reference.

For our checkout, a running count conceptually advances through 1, 2, 3, and 4 as details are emitted. A final count is four throughout that successful match. Those are different features: “progress so far” and “completed size.” Attaching the completed size to an earlier predictive decision could introduce knowledge that was unavailable then.

Do not silently switch from match-level metrics to row-level metrics when changing ONE ROW to ALL ROWS. If overlapping failure matches emit three details each, three matches produce nine memberships from five source events. Nine is not a count of distinct failed events. Preserve event IDs when validating that distinction.

Which changes make a convincing test set?

Our local verification executes a restricted Python trace of these fixtures. It checks greedy and reluctant checkout endpoints, the interrupted sequence, fixed-length overlapping windows, and variable-length restart results. It does not execute the Snowflake SQL or implement a general row-pattern engine. The expected SQL remains documentation-reviewed until run in Snowflake.

Useful adversarial changes include moving an ADD between tenants, inserting NOISE between START and ADD, removing the final PAY, and creating tied timestamps with a defined sequence rule. Each change should have an independently predicted effect. A test that only checks a nonempty result cannot distinguish most wrong implementations.

Also reconcile physical events, matched memberships, and summarized matches separately. For the three overlapping failure windows, expect five distinct events, nine memberships, and three matches. A downstream dashboard can be wrong even when the pattern trace is correct if it sums memberships as unique incidents.

For performance, measure representative partitions, especially a long sequence that nearly matches but fails at its end. Snowflake warns that broad unbounded patterns can increase backtracking work in its pattern usage guidance. Restrict repetition only when the business contract permits that limit; changing semantics to make a benchmark faster is not an optimization of the same query.

Practice the reasoning, not a memorized pattern

QuestionFollow-up to explain
Count super-streak segments in an event streamDistinguish a run from overlapping matched windows.
Implement User Sessionization From Event StreamDefine partitions, event order, and interruption rules.
Solve window-function SQL without joinsExplain why row order belongs in the contract.
Query conversion and retention with SQL windowsPreserve the denominator when one event has several memberships.
Write SQL and pandas for shopping eventsSeparate permitted intervening events from filtering them away.

A clear interview answer can begin: “I will define the ordered stream and output grain, trace a complete match, then state exactly where the next attempt starts.” Attempt one data-engineering practice question, write the expected event IDs first, and change one pattern or restart choice. The explanation should make the changed output inevitable.

Sources and Further Reading

Evidence checked October 3, 2026. The examples are synthetic; local traces do not establish Snowflake execution or employer interview frequency.