I would like to count how many new unique users the database gets each day for all days recorded.
There will not be any duplicate ids per day, but there will be duplicates over multiple days.
If my table looks like this :
ID | DATE
---------
1 | 2022-05-21
1 | 2022-05-22
2 | 2022-05-22
1 | 2022-05-23
2 | 2022-05-23
1 | 2022-05-24
2 | 2022-05-24
3 | 2022-05-24
I would like the results to look like this :
DATE | NEW UNIQUE IDs
---------------------------
2022-05-21 | 1
2022-05-22 | 1
2022-05-23 | 0
2022-05-24 | 1
A query such as :
SELECT `date` , COUNT( DISTINCT id)
FROM tbl
GROUP BY DATE( `date` )
Will return the count per day and will not take into account previous days.
Any assistance would be appreciated.
Edit : Using MySQL 8
>Solution :
The user is new when the date is the least date for this user.
So you need in something like
SELECT date, COUNT(new_users.id)
FROM calendar
LEFT JOIN ( SELECT id, MIN(date) date
FROM test
GROUP BY id ) new_users USING (date)
GROUP BY date
calendar
is either static or dynamically generated table with needed dates list. It can be even SELECT DISTINCT date FROM test
subquery.