Advanced SQL Patterns for Production Analytics

Fabien Contaminard, Serge Rielau18 Jun 2026

Problem

Analysts often need to find ordered patterns of rows: a session boundary after a quiet gap, a checkout never followed by a purchase, a price curve that falls and then rises. In classic SQL these take self-joins or four chained CTEs with window functions. The queries are slow or hard to read, and when the business changes the definition, the query has to be rethought from scratch. Joining two event streams on timestamps adds a slow inequality join.

Example from the article: a click stream has no session ID, and the team wants to know what users do after beginning checkout when they do not buy. A related case: a customer's join of about 1 million trades to 10 million quotes took about four minutes.

Solution in brief

Use MATCH_RECOGNIZE (SQL:2016), which works like regular expressions but over ordered rows. Name row conditions in define, describe the sequence in pattern, and compute outputs in measures. One short query replaces the joins and CTE chains. When the business changes a definition, you usually edit only the pattern.

Questions this case answers
  • How to detect ordered patterns in event rows, such as a session boundary after a quiet gap or a sequence of steps that is never completed, in a single SQL pass
  • How to attach to each record the latest preceding record from another time-ordered stream without a slow inequality join
  • How to sessionize a clickstream that has no session ID using only SQL
  • How to find where users drop out of a funnel (checkout started, no purchase) in one SQL pass

Tools and methods

  • MATCH_RECOGNIZE — row pattern matching in SQL: partition by, order by, define, pattern, measures.
  • MATCH_NUMBER() and CLASSIFIER() — match ID (used as a session ID) and the pattern variable each row matched.
  • Window-function baselines — break detection with cumulative sum, Tabibitosan row-number difference.
  • Sankey diagram — display of what happens after checkout.
Continue reading — it's free

Google or a one-time email code. No password.