3

How do I query for distinct customers? Here's the table I have..

CustID DATE PRODUCT ======================= 1 Aug-31 Orange 1 Aug-31 Orange 3 Aug-31 Apple 1 Sept-24 Apple 4 Sept-25 Orange

This is what I want.

# of New Customers DATE ======================================== 2 Aug-31 1 Sept-25

Thanks!

Improve this question
3
  • 1
    SELECT Date, COUNT(DISTINCT CustID) FROM YourTable GROUP BY Date? Commented May 13, 2014 at 21:18
  • Why no customer on Sept 24 and one on Sept 25 in your desired result? Commented May 13, 2014 at 21:19
  • well Sept 24 is a returning customer. So I don't care about repeat customers Commented May 13, 2014 at 21:23

2 Answers 2

Reset to default
6

This is a bit tricky. You want to count the first date a customer appears and then do the aggregation:

select mindate, count(*) as NumNew from (select CustId, min(Date) as mindate from table t group by CustId ) c group by mindate
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You could use a simple common table expression to find the first time a user id is used;

WITH cte AS ( SELECT date, ROW_NUMBER() OVER (PARTITION BY custid ORDER BY date) rn FROM customers ) SELECT COUNT(*)[# of New Customers], date FROM cte WHERE rn=1 GROUP BY date ORDER BY date

An SQLfiddle to test with.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.