title: MATCH_RECOGNIZE with CTE in Snowflake
body: <p>I am using <code>MATCH_RECOGNIZE</code> function in a query with a few CTEs. When I run the query, I got the following error:</p>
  <blockquote>
  <p>SQL compilation error: MATCH_RECOGNIZE not supported in this context.</p>
  </blockquote>
  <p>In my query, there are several CTEs before and after the MATCH_RECOGNIZE partially as below.</p>
  <pre><code>WITH cte1 AS (
  SELECT *
  FROM dataset
  WHERE ID IS NOT NULL AND STATUS IS NOT NULL ),
  
  cte2 AS (
  SELECT *
  FROM cte1
  QUALIFY FIRST_VALUE(STATUS) OVER (PARTITION BY ID ORDER BY CREATED_AT) = 'created' )
  
  mr as (
  SELECT *
      FROM cte2
      MATCH_RECOGNIZE (
        PARTITION BY ID
        ORDER BY CREATED_AT
        MEASURES MATCH_NUMBER() AS mn,
                 MATCH_SEQUENCE_NUMBER AS msn
        ALL ROWS PER MATCH
        PATTERN (c+m+)
        DEFINE
           c AS status='created'
          ,m AS status='missing_info'
          ,p AS status='pending'
      ) m1
      QUALIFY (ROW_NUMBER() OVER(PARTITION BY mn, ID ORDER BY msn) = 1)
            OR(ROW_NUMBER() OVER(PARTITION BY mn, ID ORDER BY msn DESC)=1)
      ORDER BY ID, CREATED_AT ),
  
  cte3 as (
  SELECT *
  FROM mr
  -- some other operations
  )
  </code></pre>
  <p>What would be the ideal approach to solve this? e.g. creating a regular view, a materialized view, or a temp table, etc. I tried to create a view but got an error, not sure if it is supported either.
  How can I use the result of the <code>MATCH_RECOGNIZE</code> in other later CTEs?</p>
  <p>When I add the following, it gives this error:</p>
  <blockquote>
  <p>syntax error line xx at position 0 unexpected 'create'.</p>
  </blockquote>
  <pre><code>create view filtered_idents AS
  
  SELECT *
  FROM cte2
  MATCH_RECOGNIZE (
  
  )
  </code></pre>