Count Unique Users adds every month

Question

I am trying to get a SQL query to count unique customer for a given month, count only if the Customer was not part of any previous months.

date|       Customer_name
---------------------------------
2016/01/01  John
2016/01/01  Jane
2016/02/01  Joe
2016/02/01  Jill
2016/02/01  John
2016/03/01  Jane
2016/03/01  Jill
2016/03/01  Jay
2016/04/01  Jude

And I want to count the customer only if the Customer was not part of any previous months.

DATE       NEW_CUSTOMER_ADDS
-------------------------------------------------------
2016/01/01      2
2016/02/01      2
2016/03/01      1
2016/04/01      1

Dai · Answer 1 · 2018-10-22T22:16:58.307

First, let's get the first visit date for each customer:

SELECT
    Customer_name,
    MIN( [date] ) AS firstDate
FROM
    [TheTable]
GROUP BY
    Customer_name

Then let's get the month information for those firstDate values (see How can I select the first day of a month in SQL? ):

SELECT
    Customer_name,
    DATEFROMPARTS( YEAR( MIN( [date] ) ), MONTH( MIN( [date] ) ), 1 ) AS monthOfFirstDate
FROM
    [TheTable]
GROUP BY
    Customer_name

Then we can aggregate this data to get the number of customers who share the same first-date month (and remove the Customer_name column from the SELECT which we don't need anymore):

SELECT
    monthOfFirstDate,
    COUNT( * ) AS newCustomers
FROM
    (
        SELECT
            DATEFROMPARTS( YEAR( MIN( [date] ) ), MONTH( MIN( [date] ) ), 1 ) AS monthOfFirstDate
        FROM
            [TheTable]
        GROUP BY
            Customer_name
    ) AS customersFirstDateMonths
ORDER BY
    monthOfFirstDate

score 0 · Accepted Answer · edited Oct 22 '18 at 22:18

SELECT
    COUNT(*),
    DATEADD( MONTH, DATEDIFF( MONTH, 0, [date] ), 0) AS [month]
FROM
    (
        SELECT
            Customer_name,
            [date]
        FROM
            (
                SELECT
                    Customer_name,
                    [date],
                    Row_number() OVER( PARTITION BY Customer_name ORDER BY [date] ) AS rn
                FROM
                    YourTable
            ) AS t
        WHERE
            rn = 1
    ) AS d
GROUP BY
    DATEADD( MONTH, DATEDIFF( MONTH, 0, [date] ), 0 ) 
ORDER BY
    [month]

score -4 · Answer 3 · answered Oct 22 '18 at 19:35

-4

Since you haven't posted any attempt to solve this yourself, I assume you first need to be pointed in the general direction of a solution.

Try using a WHERE NOT EXISTS() filter.

answered Oct 22 '18 at 19:35

Tab Alleman

31,483
7
36
52

Could have used the comment function for this instead. – Salman A Oct 22 '18 at 19:55
No, comments are for asking for clarification. – Tab Alleman Oct 22 '18 at 20:05
Hello @TabAlleman, thank you for your comment. I forgot to post my query, here is the query I wrote to begin with SELECT Customer_name , row_number() over(partition by Customer_name order by Year([Prod_DT]), Month([Prod_DT]) DESC) as rn , Year([Prod_DT]) AS Years , Month([Prod_DT]) AS Months FROM MY_TABLE WHERE Prod_DT <='6/1/2018' GROUP BY Year([Prod_DT]), Month([Prod_DT]), Customer_name ORDER BY Year([Prod_DT]), Month([Prod_DT]), Customer_name – G Mohan Oct 22 '18 at 22:12

Count Unique Users adds every month

3 Answers3