How to filter out from count distinct query

Question

I am trying to calculate numbers of customers whom are active in the past 3 and 6 months.

SELECT COUNT (DISTINCT CustomerNo)
FROM SalesDetail
WHERE InvoiceDate > (GETDATE() - 180) AND InvoiceDate < (GETDATE() - 90)

SELECT COUNT (DISTINCT CustomerNo)
FROM SalesDetail
WHERE InvoiceDate > (GETDATE() - 90)

However, based on above query, I'll get count Customers which has been active for both in the last 3 months and the last 6 months, even if there are duplicates like this.

• Customer A bought once in past 3 months
• Customer A bought once in past 6 months too

How do I filter out the customers, so that if customer A has been active in both past 3 and 6 months, he/she will only be counted in the 'active in past 3 months' query and not in the 'active in past 6 months' too.

Answer 1

I solve this problem this way Let us consider you have following table. You might have more columns but for the result you want, we only require customer_id and date they bought something on.

CREATE TABLE [dbo].[customer_invoice](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [customer_id] [int] NULL,
    [date] [date] NULL,
 CONSTRAINT [PK_customer_invoice] PRIMARY KEY([id]);

I created this sample data on this table

INSERT INTO [dbo].[customer_invoice]
   ([customer_id]
   ,[date])
 VALUES
   (1,convert(date,'2019-12-01')),
   (2,convert(date,'2019-11-05')),
   (2,convert(date,'2019-8-01')),
   (3,convert(date,'2019-7-01')),
   (4,convert(date,'2019-4-01'));

Lets not try to jump directly on the final solution directly but take a single leap each time.

SELECT customer_id, MIN(DATEDIFF(DAY,date,GETDATE())) AS lastActiveDays
FROM customer_invoice GROUP BY customer_id;

The above query gives you the number of days before each customer was active

customer_id lastActiveDays
   1         15
   2         41
   3         168
   4         259

Now We will use this query as subquery and Add a new column ActiveWithinCategory so that in later step we can group our data by the column.

SELECT customer_id, lastActiveDays,
    CASE WHEN lastActiveDays<90 THEN 'active within 3 months'
         WHEN lastActiveDays<180 THEN 'active within 6 months'
         ELSE 'not active' END AS ActiveWithinCategory 
FROM(
    SELECT customer_id, MIN(DATEDIFF(DAY,date,GETDATE())) AS lastActiveDays
    FROM customer_invoice GROUP BY customer_id
)AS temptable;

This query gives you the the following result

customer_id lastActiveDays  ActiveWithinCategory
   1              15        active within 3 months
   2              41        active within 3 months
   3             168        active within 6 months
   4             259        not active

Now use the above whole thing as subquery and Group the data using ActiveWithinCategory

SELECT ActiveWithinCategory, COUNT(*) AS NumberofCustomers FROM (
    SELECT customer_id, lastActiveDays,
        CASE WHEN lastActiveDays<90 THEN 'active within 3 months'
             WHEN lastActiveDays<180 THEN 'active within 6 months'
             ELSE 'not active' END AS ActiveWithinCategory 
    FROM(
        SELECT customer_id, MIN(DATEDIFF(DAY,date,GETDATE())) AS lastActiveDays
        FROM customer_invoice GROUP BY customer_id
    )AS temptable 
) AS FinalResult GROUP BY ActiveWithinCategory;

And Here is your final result

ActiveWithinCategory    NumberofEmployee
active within 3 months      2
active within 6 months      1
not active                  1

If you want to achieve same thing is MySQL Database Here is the final Query

SELECT ActiveWithinCategory, count(*) NumberofCustomers FROM(
    SELECT MIN(DATEDIFF(curdate(),date)) AS lastActiveBefore,
           IF(MIN(DATEDIFF(curdate(),date))<90,
              'active within 3 months',
              IF(MIN(DATEDIFF(curdate(),date))<180,'active within 6 months','not active')
            ) ActiveWithinCategory
    FROM customer_invoice GROUP BY customer_id
) AS FinalResult GROUP BY ActiveWithinCategory;