CODEWITHSUNDEEP
mysql
Fuzz
Fuzz

Reputation: 947

MySQL - Count unique users each day considering all previous days

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

Upvotes: 5

Views: 369

Answers (4)

vivek raj
vivek raj

Reputation: 1

select [Date],sum(case when rk = 1 then 1 else 0 end) as [new unique ids]
from 
(
select [Date],[id],rank() over(partition by [id] order by [Date]) rk 
from tbl) k
group by [Date]

--This works in SQL SERVER

Upvotes: 0

Luuk
Luuk

Reputation: 14999

Only count (and sum) the rows where the left join fails:

SELECT 
    m1.`DATE` , 
    sum(CASE WHEN m2.id is null THEN 1 ELSE 0 END) as C
FROM mytable m1
LEFT JOIN mytable m2 ON m2.`DATE`<m1.`DATE` AND m2.ID=m1.ID
GROUP BY m1.`DATE`

see: DBFIDDLE

Upvotes: 1

Akina
Akina

Reputation: 42844

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.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108839

Start with a subquery showing the earliest date where each id appears.

            SELECT MIN(`date`) `firstdate`, id
              FROM tbl
             GROUP BY id

Then do your count on that subquery. here.

SELECT firstdate, COUNT(*)
  FROM (
            SELECT MIN(`date`) `firstdate`, id
              FROM tbl
             GROUP BY id
       ) m
 GROUP BY firstdate

That gives you what you want.

But it doesn't have rows for the dates where no new user ids first appeared.

Upvotes: 1

Related Questions