Geek Answers Handbook

SQL query to count registered users per day

I was almost pissed off trying to solve this problem:

In my application, users can register and delete themselves. Creation date and deletion date are stored in the database as a time stamp. I need to know every day for several days how many registered and non-deleted users existed that day.

So, if I have 10 existing users in 2012-02-01, one user who deleted the account in 2012-02-03, three users registered in 2012-02-04, and two users deleted in 2012-02 -06, and request the total number of registered users from 2012-02-01 to 2012-02-07 I would like to receive the following result:

day total_users
2012-02-01 10
2012-02-02 10
2012-02-03 9
2012-02-04 12
2012-02-05 12
2012-02-06 10
2012-02-07 10

Here's what my simplified user table looks like:

user_id, user_name, created_at, deleted_at

There is no problem to get the number of registered and non-deleted users for one specific day (here 2012-02-01, which will get me 10 in my example):

select application.application_id as application_id, count(user.user_id) as user_total
from application, user
where application.application_id = user.application_id
and date(user.created_at) <= '2012-02-01'
and ((date(user.deleted_at) > '2012-02-01') or (user.deleted_at is null))

Who has a key, how can I create a query (without a cursor) that will have the expected result? My database is MySQL 5.0.51 on Debian.

Thanks in advance Marcus

+5
source share
2 answers

If you have a table with a list of dates in it (called something like a calendar), you can use this query:

select c.calendar_date, count(*) user_total
from calendar c
left join user u
       on date(u.created_at) <= c.calendar_date and 
          (date(u.deleted_at) > c.calendar_date or u.deleted_at is null)
group by c.calendar_date

If you do not have a table with a list of dates in it, you can simulate one with the following query:

select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) calendar_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where calendar_date between ? /*start of date range*/ and ? /*end of date range*/
+2
SELECT date(user.created_at) as "creation_date", count(user.user_id) as "user_total"
FROM application a
INNER JOIN user u ON a.application_id = u.application_id 
WHERE date(user.created_at) <= '2012-02-01'
AND ((date(user.deleted_at) > '2012-02-01') or (user.deleted_at is null))
GROUP BY date(user.created_at)
ORDER BY date(user.created_at) ASC
+2

More articles:


All Articles