CODEWITHSUNDEEP
sqlsql-server
Patthebug
Patthebug

Reputation: 4787

Get new customers every week in SQL Server

I have some data related to customers and the number of transactions they perform every day. I would like to see how many "new" customers do we get each week. The data looks like the following:

Custnum    Created    Revenue
1    2014/10/23    30
4    2014/10/23    20
5    2014/10/23    40
2    2014/10/30    13
3    2014/10/30    45
1    2014/10/30    56

In the above (sample) data, we can see that customer with custnum 1 has transactions in consecutive weeks, I want only the new customers for the next week, ones that have never done business with us in the past. In other words, I want the number of totally new customers for each week. So the result should be:

CustCount    Created
3    2014/10/23
2    2014/10/30

I tried using the following query:

select 
    count(distinct custnum), 
    DATEADD(wk, DATEDIFF(wk, 0, created), 0) as Date
from ORDERS
where created > '2013-01-01'
group by 
    DATEADD(wk, DATEDIFF(wk, 0, created), 0)
order by
    DATEADD(wk, DATEDIFF(wk, 0, created), 0)

But this query gives me the number of unique customers each week, I want the number of new customers every week.

Any help will be much appreciated.

Upvotes: 2

Views: 4657

Answers (4)

surfmuggle
surfmuggle

Reputation: 5944

My understanding of your question

I took these statements:

  • I would like to see how many "new" customers do we get each week.
  • [customers] that have never done business with us in the past
  • I want the number of totally new customers for each week

Do you want

 CustCount    Created
 2            week1    -- customer 1 and 2
 1            week2    -- customer 3
 2            week3    -- customer 4 and 5
 -- Option A
 1            week4    -- customer 6 is new and 2 was not counted 
 -- or Option B
 2            week4    -- customer 6 and 2; 
                       -- since customer 2 did not order anything in week3

Option A

This query SELECT Custnum, DATEPART ( week , created) as WeekNumber from Revenues Order by Custnum returns this output for the provided sample data

Custnum    WeekNumber 
1          31       -- counts
1          44       -- does not count, since customer already ordered once
2          36       -- counts
3          36       -- counts
3          44       -- does not count
4          43       -- counts
5          43       -- counts
5          45       -- does not count

First step: filter down the records

To get only the first record for a customer (the new customer) you can do this:

SELECT Distinct Custnum, Min(Created) as Min_Created 
FROM Revenues 
GROUP BY Custnum

Second step: Counting and Grouping by week

First i used the sql from grouping customer orders by week, which you can find at the old sqlfiddle . But then i decided to use

Select Count(Custnum) as CountCust 
    , DATEPART(week, Min_Created) as Week_Min_Created 
FROM (
        SELECT Distinct Custnum, Min(Created) as Min_Created  
        FROM Revenues Group By Custnum
    ) sq Group by DATEPART(week, Min_Created)

On my this returns

CountCust  Week_Min_Created
1          31    -- only customer 1
2          36    -- customer 2 and 3
2          43    -- customer 4 and 5
-- nothing for week 45 since customer 5 was already counted

Some sample data

This is the sample data i used 

CREATE TABLE Revenues 
(
   Custnum int , 
   Created datetime,
   Revenue int 
);

INSERT INTO Revenues (Custnum, Created, Revenue)
VALUES
  (1, '20140801', 30),
  (2, '20140905', 13), (3, '20140905', 45),
  (4, '20141023', 20), (5, '20141023', 40),
  (3, '20141030', 45), (1, '20141030', 56),
  (5, '20141106', 60);

Upvotes: 4

CRUZER45
CRUZER45

Reputation: 3

A better design would be to store the date the customer signed up along with the rest of their information. Once that is done you can use the solution in the link below.

http://sqlfiddle.com/#!12/2caaa/1

Upvotes: 0

Bill
Bill

Reputation: 4585

Sounds like you want to work with a subset of ORDERS that only has each customer's first order date.

select 
    count(custnum), 
    DATEADD(wk, DATEDIFF(wk, 0, created), 0) as Date
from
    (Select custnum, min(created) as created From Orders Group by custnum) o
where created > '2013-01-01'
group by 
    DATEADD(wk, DATEDIFF(wk, 0, created), 0)
order by
    DATEADD(wk, DATEDIFF(wk, 0, created), 0)

Upvotes: 3

6ton
6ton

Reputation: 4214

You can add a where clause such that the custnum does not exist in before a week, something like 

custnum not in (select custnum from orders where created < (start date of week))

Pseudo code since I am not really familiar with sql server functions

Upvotes: 0

Related Questions