title: Oracle SQL: Checking customer recency by month
body: <p>I have an order_list table for an eCommerce site from which i need to create a report on how many customers purchased in a month. Out of these customers, how many were first time customers, how many had placed an order within the last 90 days, how many within the last 180 days, etc. </p>
  
  <p>The finished report would be an excel grid that looked like this:</p>
  
  <p><a href="https://i.stack.imgur.com/RYnWU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RYnWU.png" alt="Recency Grid Example"></a></p>
  
  <p>This is the query i have right now:</p>
  
  <pre><code>SELECT months.month,
         count(distinct(case when months.month = Trunc (orders.order_date, 'MONTH') then orders.email_address else null end)) as total_monthly_orders,
         count(distinct(case when months.month = Trunc (orders.order_date, 'MONTH') and order_rank = 1 then orders.email_address else null end)) as monthly_first_purchasers,
  
         /* This is the logic thats not working right now*/
         /*count(distinct(case when (months.month - (Trunc (orders.order_date, 'MONTH'))) &lt;= 92 then orders.email_address else null end)) as less_than_3_month_purchasers,
         count(distinct(case when (months.month - (Trunc (orders.order_date, 'MONTH'))) &lt;= 181 then orders.email_address else null end)) as less_than_6_month_purchasers,
         count(distinct(case when (months.month - (Trunc (orders.order_date, 'MONTH'))) &lt;= 273 then orders.email_address else null end)) as less_than_9_month_purchasers,
         count(distinct(case when (months.month - (Trunc (orders.order_date, 'MONTH'))) &lt;= 365 then orders.email_address else null end)) as less_than_12_month_purchasers,
         count(distinct(case when (months.month - (Trunc (orders.order_date, 'MONTH'))) &gt; 365 then orders.email_address else null end)) as one_year_plus_purchasers*/
  
  
  FROM (  SELECT Trunc (TO_DATE (A.create_date), 'MONTH') AS month
              FROM ORDER_DETAIL A
             WHERE TO_DATE (A.create_date) &gt;= '1-Jan-2018'
          GROUP BY Trunc (TO_DATE (A.create_date), 'MONTH')
          ORDER BY Trunc (TO_DATE (A.create_date), 'MONTH') ASC) months
  INNER JOIN (SELECT A.EMAIL_ADDRESS,
                 A.DEMANDWARE_ORDER_NUMBER AS order_id,
                 TO_DATE (A.CREATE_DATE) AS order_date,
                 RANK ()
                 OVER (PARTITION BY A.EMAIL_ADDRESS
                       ORDER BY A.DEMANDWARE_ORDER_NUMBER ASC)
                    AS Order_rank
            FROM ORDER_DETAIL A
           WHERE A.email_address IS NOT NULL) orders
            ON months.month &gt;= Trunc (orders.order_date, 'MONTH')
  
  Group by months.month
  Order by months.month ASC 
  </code></pre>
  
  <p>I tested the query and its correctly pulling the total customers and first time customers. But i can't figure out how to pull the customers whose last purchase would be within the last 3 months / 6 months / etc.</p>
  
  <p>Can anyone help me out with this?</p>
  
  <p>Edit: Adding sample data
  <a href="https://i.stack.imgur.com/Pznsf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Pznsf.png" alt="Current Query Output"></a></p>
  
  <p>I've checked against other queries and the values in the total customer &amp; first time customer columns are correct. But the values in the other columns are incorrect. Essentially, to test this, the sum of the customers in every bucket should be equal to the number of total customers. </p>
  
  <p>For example: if we had 34,016 customers in total in Jan, we couldn't have 166,148 customers in the &lt;3 Months bucket</p>