January 15, 2008 at 3:58 pm
I've been tasked with generating a dataset that returns the number of active customers per month, going back 4 years. I have a view in place that returns a customer id and order date for each order in the database. "Active customer" is defined as a customer who has placed an order within the last year.
So what I need to be able to do is go back month by month and find out how many customers were active (based on the above definition) during each month.
Can anyone help me with this?
Thanks!
January 15, 2008 at 9:37 pm
Try this..
SELECT MONTH(OrderDate), COUNT(CustomerID) As ActiveCustomers
FROM v_Customer -- VIEW
GROUP BY MONTH(OrderDate)
ORDER BY OrderDate
January 16, 2008 at 2:23 am
Raghs (1/15/2008)
Try this..SELECT MONTH(OrderDate), COUNT(CustomerID) As ActiveCustomers
FROM v_Customer -- VIEW
GROUP BY MONTH(OrderDate)
ORDER BY OrderDate
need to change it to something like this:
SELECT YEAR(OrderDate) as year, MONTH(OrderDate) as month, COUNT(DISTINCT CustomerID) As customers
FROM v_Customer -- VIEW
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY YEAR(OrderDate), MONTH(OrderDate)
so all 48 months are reported individually and customers are not over-stated.
January 16, 2008 at 2:56 am
Good answers, except I'm not sure they fit the definition. The last answer given correctly gives the number of unique customers that placed an order in that month.
However, your definition of an active customer talks about the customer having placed an order in the past year. Thus I assume that the figure for (as an example) October 2007 would need to show the count of distinct customers that had placed an order between November 2006 and October 2007 inclusive. Is that the case?
If so, you'd need to do some sort of a self join such as
SELECT YEAR(v1.OrderDate) as year, MONTH(v1.OrderDate) as month, COUNT(DISTINCT v1.CustomerID) As customers
FROM v_Customer v1 -- VIEW
INNER JOIN v_Customer vPast
on v1.orderDate > dateAdd(year, -1, v2.orderDate)
and v1.orderDate <= v2.orderDate
and v1.customerID = v2.customerID
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY YEAR(OrderDate), MONTH(OrderDate)
That's still not quite correct though as the date addition occurs down to the mid-month level. Instead it should round up to the end of the month and then subtract the year. I'll leave that to you 🙂
January 16, 2008 at 8:24 am
Thanks to all for the great advice!
Ian's assumption about my requirements for the query were correct - his answer was what I was looking for (sorry for not explaining myself better).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply