title: Window function or Recursive query in Redshift
body: <p>I try to classify customer based on monthly sales. Logic for calculation:</p>
  <p><strong>new customer</strong> – it has sales and appears for first time or has sales and returned after being lost (after 4 month period, based on sales_date)</p>
  <p><strong>active customer</strong> - not new and not lost.</p>
  <p><strong>lost customer</strong> - no sales and period (based on sales_date) more than 4 months</p>
  <p><a href="https://i.stack.imgur.com/3JvOy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3JvOy.png" alt="dataset" /></a></p>
  <p>This is the desired output I'm trying to achieve:</p>
  <p><a href="https://i.stack.imgur.com/tffSX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tffSX.png" alt="enter image description here" /></a></p>
  <p>The below Window function in Redshift classify however it is not correct.</p>
  <p>It classified lost when difference between month &gt; 4  in one row, however it did not classify lost if it was previous lost and revenue 0 until  new status appear. How it can be updated?</p>
  <pre><code>with customer_status (
          select customer_id,customer_name,sales_date,sum(sales_amount) as sales_amount_calc,
          nvl(DATEDIFF(month, LAG(reporting_date) OVER (PARTITION BY customer_id ORDER by sales_date ASC), sales_date),0) AS months_between_sales
       from customer
       GROUP BY customer_id,customer_name,sales_date
      )
  
  select *,
  case     
          WHEN months_between_sales = 0 THEN 'New'
          WHEN months_between_sales &gt; 0 THEN 'Active'
          WHEN months_between_sales &gt; 0  AND months_between_sales &lt;= 4   and sales_amount_calc = 0   THEN 'Active'
          WHEN /* months_between_sales &gt; 0 and */ months_between_sales &gt; 4  and  sales_amount_calc = 0   THEN 'Lost'
          ELSE 'Unknown'
           END AS status
  
  from customer_status
  </code></pre>
  <p>One way to solve to get cumulative sales amount partitioned on subset of potentially lost customers ( sales_amount = 0).</p>
  <p>Cumulative amount for the customer partitioned</p>
  <pre><code>sum(months_between_sales) over (PARTITION BY customer_id ORDER by sales_date ASC rows unbounded preceding) as cumulative_amount,
  </code></pre>
  <p>How can it be changed to get sub-partitioned, for example when sales amount= 0 , in order to get lost correctly?</p>
  <p>Does someone have ideas to translate the above logic into an
  recursive query in Redshift?</p>
  <p>Thank you</p>