SQL - Trying to get Customers who ordered a product for the first time during a month and...

Question

I need to get a list of customer who ordered a particular product for the first time in a particular month and then sum how much of that items they have ordered since then. I started working on this but feel like I am going in the wrong direction - SQL is not my strong point.

So sample result data would be something like:

              Num of Customers   |  Num of Orders | Total Revenue | Total Gross Profit 
--------------------------------------------------------------------------
January       100                   135              $1350           $725

Here are my tables (excluded columns not needed):

Customer

CustomerID

CustomerOrder

CustomerOrderID
CustomerID
OrderID
OrderDate

CustomerOrderItem

CustomerOrderItemID
OrderID
SKU
Price
Cost

Sample SKU would be something like 'ESK-1MVV' and profit is simply Price - Cost

Answer 1

select count(c.CustomerID), sum(Price), sum((Price-Cost)) 
from  
(
  select CustomerID, min(OrderDate) as firstDate
  From CustomerOrder a
  inner join CustomerOrderItem b
  on a.OrderID = b.OrderID
  where SKU='ESK-1MVV'
  group by CustomerID
) as firstSale
inner join
CustomerOrder c
on firstSale.CustomerID = c.CustomerID
inner join CustomerOrderItem d
on c.OrderID = d.OrderID
where month(firstSale.firstDate) = 1
and year(firstSale.firstDate) = 2009
and d.SKU='ESK-1MVV'

Limit to the customers who bought the item, and get the date they first bought it. In the outer where, limit to ones who bought it first in the desired month. Add "and OrderDate > firstDate" to exclude the first sale, if that's what you wanted