craftsman
craftsman

Reputation: 15623

How to write a query to return daily returning users?

I have a very simple table phone_calls with these columns:

id, timestamp, phone_number

How to write a query which returns a daily count of returning users? It should have two columns:

Date, Number of Returning Users

Returning user:

A returning user for any given day D is the one, who has called at least once before D.

A user who has called multiple times on D, but hasn't called before D won't be counted as a returning user.

UPDATE

So here is what I have tried:

SELECT DATE(timestamp) AS date, COUNT(DISTINCT phone_number) AS user_count
FROM phone_calls
WHERE phone_number IN (SELECT phone_number FROM phone_calls GROUP BY phone_number HAVING COUNT(consumer_id) > 1)
GROUP BY DATE(timestamp)

But it's not a correct solution, because it doesn't comply with definition of Returning User mentioned above.

What I am struggling with?

For any given date, how do I filter out those phone numbers from the count, who never dialed in before that day?

Upvotes: 0

Views: 332

Answers (2)

Kevin Anderson
Kevin Anderson

Reputation: 4592

SELECT 
    DATE(timestamp) AS date,
    COUNT(DISTINCT phone_number) AS     user_count
FROM 
    phone_calls pc
WHERE EXISTS (
    SELECT *
    FROM phone_calls pc1
    WHERE 
        pc1.phone_number = pc.phone_number AND
        DATE(pc1.timestamp) < DATE(pc.timestamp)
    )
GROUP BY DATE(pc.timestamp)

Upvotes: 2

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2998

Updated try this query

 Select date(pc.timestamp) AS RDate ,count(*)  
from phone_calls pc INNER JOIN phone_calls pcc
on pcc.phone_number=pc.phone_number 
and date(DATE_ADD(pcc.timestamp, INTERVAL -1 DAY))= DATE (pc.timestamp)  group by DATE (pc.timestamp);

Upvotes: 0

Related Questions