Query Optimization in Snowflake (QOSF)

MATCH_RECOGNIZE

Pattern Matching is key to Funnel Analytics. Snowflake’s MATCH_RECOGNIZE clause can perform Pattern Matching over a set of rows. MATCH_RECOGNIZE does this by assigning labels to events, finding the events within an ordered partition, and pulling out any sequences that match the given pattern. The following query uses MATCH_RECOGNIZE to sessionize clickstream data to identify sessions that begin with an ENTRY to the site, then have a series of ONSITE page views, and end with a CHECKOUT_START.

select 
  post_visid_high || ':' || post_visid_low as visitor_id  
  , date_time, referrer, page_url
from clickstream_data 
match_recognize (
partition by post_visid_high || ':' || post_visid_low
order by date_time
all rows per match
pattern (entry onsite+ checkout_start)
define 
  entry as referrer is null or referrer not like '%buy.com%',
  onsite as page_url like '%buy.com%' 
    and page_url not like 'https://buy.com/checkout/%',
  checkout_start as page_url like 'https://buy.com/checkout/%'
);

See also:

• A/B Analysis on Streaming Data using MATCH_RECOGNIZE
• Applied overview of MATCH_RECOGNIZE clause
• Correlated pattern definitions and Snowflake’s MATCH_RECOGNIZE
• How to Predict Customer Churn Using SQL Pattern Detection - MATCH_RECOGNIZE
• Association Rule Mining using MATCH_RECOGNIZE
• MATCH_RECOGNIZE
• Stock Analysis using MATCH_RECOGNIZE
• MATCH_RECOGNIZE and Data Vault Effectivity Satellite - Part II
• MATCH_RECOGNIZE and Data Vault Effectivity Satellite
• What’s eating up your Snowflake Virtual Warehouse - Part II featuring MATCH_RECOGNIZE

This site is open source. Improve this page.