MySQL – Count unique users each day considering all previous days

May 26, 2022

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.