title: Window function (LEAD/LAG) with where clause?
body: <p>So I have a table of page hits on a website and for each page of a specific type (<code>marketing_page</code>), I am trying to identify the next page a customer hits. So my query would probably look something like this</p>
  
  <pre><code>Select * from 
  (
  Select page_id
  , hit_time
  , customer_id
  , session_id
  , page_type
  , LEAD(page_id, 1) over (PARTITION BY customer_id, session_id ORDER BY hit_time) as next_page_id
  FROM page_hits
  )
  WHERE page_type = 'marketing_page'
  </code></pre>
  
  <p>The problem with this approach is that the sub-query becomes HUGE if I keep the <code>WHERE</code> clause outside the sub-query. Ideally I'd like to be able to do something like:</p>
  
  <pre><code>Select page_id
  , hit_time
  , customer_id
  , session_id
  , page_type
  , LEAD(page_id, 1) over (PARTITION BY customer_id, session_id ORDER BY hit_time) as next_page_id
  FROM page_hits
  WHERE page_type = 'marketing_page'
  </code></pre>
  
  <p>but have it still account for pages outside the <code>WHERE</code> clause when doing the <code>LEAD</code> function. I understand that the <code>LEAD</code> function gets evaluated after the <code>WHERE</code> so this is not possible.</p>
  
  <p>I would also like to avoid a self join because of the efficiency issue. Is there a fast/simple way to achieve this?</p>
  
  <p>Thanks!</p>