title: Funnel Analytics using SQL in Snowflake
body: <p>I am working on building a query to track the life cycle of an user through the platform via events. The table <code>EVENTS</code> has 3 columns <code>USER_ID</code>, <code>DATE_TIME</code> and <code>EVENT_NAME</code>. Below is a snapshot of the table,</p>
  <p><a href="https://i.stack.imgur.com/jeRnP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jeRnP.png" alt="enter image description here" /></a></p>
  <p>Below is my query,</p>
  <pre><code>SELECT * FROM EVENTS
  MATCH_RECOGNIZE
  (   PARTITION BY USER_ID
      ORDER BY DATE_TIME
      MEASURES MIN(IFF(EVENT_NAME = 'registration new', DATE_TIME, NULL)) AS REGISTRATION_NEW_TIMESTAMP,
               MIN(IFF(EVENT_NAME = 'registration pending confirm', DATE_TIME, NULL)) AS REGISTRATION_PENDING_CONFIRM_TIMESTAMP,
               MIN(IFF(EVENT_NAME = 'your business information', DATE_TIME, NULL)) AS YOUR_BUSINESS_INFORMATION_TIMESTAMP,
               MIN(IFF(EVENT_NAME = 'your personal information', DATE_TIME, NULL)) AS YOUR_PERSONAL_INFORMATION_TIMESTAMP,
               MIN(IFF(EVENT_NAME = 'qualified', DATE_TIME, NULL)) AS QUALIFIED_TIMESTAMP
    ONE ROW PER MATCH
    PATTERN(STEP_1 ANYTHING* STEP_5)
    DEFINE
          STEP_1 AS EVENT_NAME = 'registration new',
          STEP_2 AS EVENT_NAME = 'registration pending confirm',
          STEP_3 AS EVENT_NAME = 'your business information',
          STEP_4 AS EVENT_NAME = 'your personal information',
          STEP_5 AS EVENT_NAME = 'qualified'
  )
  </code></pre>
  <p>My expected result,</p>
  <p><a href="https://i.stack.imgur.com/OMWl5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/OMWl5.png" alt="enter image description here" /></a>
  <a href="https://i.stack.imgur.com/5wlpI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5wlpI.png" alt="enter image description here" /></a>
  <a href="https://i.stack.imgur.com/pfapG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pfapG.png" alt="enter image description here" /></a></p>
  <p>What I am getting right now,</p>
  <p><a href="https://i.stack.imgur.com/5TQmh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5TQmh.png" alt="enter image description here" /></a>
  <a href="https://i.stack.imgur.com/pdyBZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pdyBZ.png" alt="enter image description here" /></a>
  <a href="https://i.stack.imgur.com/ePv2j.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ePv2j.png" alt="enter image description here" /></a></p>
  <p>Below are my requirements/caveats,</p>
  <ul>
  <li>The timestamp of the following event should be greater than or equal to the timestamp of the previous event (whichever comes first and so that the timestamps are equal or keep increasing for the events through the funnel). A good example of this logic can explained by the difference in the current and expected result i.e. with the values in the <code>REGISTRATION_PENDING_CONFIRM_TIMESTAMP</code> and <code>QUALIFIED_TIMESTAMP</code> column.</li>
  <li>Not all users have all these 5 events, for example if <code>USER_ID</code> 54321 doesn't have/skips the event 'your personal information' the result must have the data for the rest of the steps (right now if the user doesn't have/skips any event in the funnel no data is returned by the query). I feel that this is because the pattern search fails when the event defined as a measure is missing in the user flow.</li>
  </ul>
  <p><strong>The order of events is not consistent in the table so I  have defined the events in order in the measures section as per the business/funnel logic</strong></p>