title: SQL to find new vs repeat customers that ordered particular product in a given month
body: <pre><code>SELECT
  DATEPART(mm, o.OrderDate) AS Month,
  c.EmailAddress,
  od.productcode AS SKU,
  SUM(od.Quantity) as Quantity,
  SUM((od.ProductPrice) * (od.Quantity) ) AS REVENUE,
  (select count(o.OrderID) FROM Orders o WHERE c.CustomerID=o.CustomerID AND o.OrderDate BETWEEN  '1/1/2010 00:00' AND getdate()) AS NO_OF_ORDERS,
  (CASE WHEN exists (Select * from Orders 
         where CustomerID = c.CustomerId
            and orderDate &lt; o.orderDate) 
         then 'Repeat' else 'New' end ) AS Customers
  FROM Customers c
      join (Orders o join OrderDetails od
              on od.OrderID = o.OrderID)
          on o.CustomerID = c.CustomerID 
  WHERE
  o.OrderDate BETWEEN '1/1/17 00:00' AND '01/31/17 23:59'
          AND o.OrderStatus &lt;&gt; 'Cancelled'
          AND od.ProductPrice &lt;&gt; 0
          AND od.ProductCode = 'CBTN850'
          AND od.Quantity NOT LIKE 0
  GROUP BY DATEPART(mm,o.OrderDate),c.EmailAddress, od.productcode,c.CustomerID,o.OrderDate
  </code></pre>
  
  <p>working on this right now</p>
  
  <pre><code> select Customers.EmailAddress,
      case when( c1.EmailAddress=Customers.EmailAddress then new  end) as customers
      from
      (SELECT
      EmailAddress,
      MIN(Orders.OrderDate) as FirstOrderdate
      FROM
      Customers
      INNER JOIN
      Orders
      ON Orders.CustomerID = Customers.CustomerID
      JOIN OrderDetails
      ON OrderDetails.OrderID = Orders.OrderID
      WHERE Orders.OrderDate &gt; '01/01/2017 00:00:00' 
       AND Orders.OrderDate &lt;  '01/31/2017 00:00:00'
       AND Orders.OrderStatus &lt;&gt; 'Cancelled'
              AND OrderDetails.ProductPrice &lt;&gt; 0
              AND OrderDetails.ProductCode = 'CBTN850'
              AND OrderDetails.Quantity NOT LIKE 0
            AND Customers.EmailAddress NOT IN (
                SELECT Customers.EmailAddress FROM Customers
                 INNER JOIN
                 Orders
                 ON Orders.CustomerID = Customers.CustomerID
          WHERE Orders.OrderDate&lt; '01/01/2017 00:00:00')c1 AS c1.EmailAddress=Customers.EmailAddress
      GROUP BY  Customers.EmailAddress
  </code></pre>
  
  <p>Trying to do a SQL query which shows new vs returning customer for a given month. New customer is that who has never ordered before.</p>
  
  <pre><code>    SELECT
          c.EmailAddress,
          od.productcode AS SKU,
          COUNT(o.OrderID) AS number_of_Orders,
          DATEPART(mm, o.OrderDate) AS Month
      FROM
          Customers c
          JOIN Orders o
              ON c.CustomerID=o.CustomerID
          JOIN OrderDetails od
              ON od.OrderID = o.OrderID
      WHERE
          o.OrderDate BETWEEN '1/1/17 00:00' AND '04/30/17 23:59'
              AND o.OrderStatus &lt;&gt; 'Cancelled'
              AND od.ProductPrice &lt;&gt; 0
              AND od.ProductCode = 'ABC01'
              AND od.Quantity NOT LIKE 0
      GROUP BY 
          DATEPART(mm,o.OrderDate),
          c.EmailAddress,
          od.productcode
  </code></pre>
  
  <p>I wrote this code. But this is not how I want it to work. I want to see 
  product code, New  customers and Repeat Customers as output. Can we do it by making two cases for new and repeat customer?</p>