Reputation: 15623
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
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
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