To find all the active users in a bucket of 15 days in SQL

Question

I have two tables: users, user_source_history.

In table users, all the users who have registered are saved. And, in table user_source_history, their activities are stored. These activities may include log in to the system, ordering something. So one user can exist multiple times in user_source_history.

Now the problem is I need to find all the active users in the bucket of 15 days.

So, for example, a user purchased something on 2014-12-03. Now, this user will appear active user till 2014-12-18.

I need to find all the active users in date range.

So, for example, in this date range-

2014-12-03 - 10 users are active.
2014-12-04 - 10(active on 2014-12-03) + (new users for this date)
2014-12-05 - Number on active user on this date + all users in 15 day bucket 
2014-12-06 - Number on active user on this date + all users in 15 day bucket

The query:

SELECT CAST(`user_last_interaction_date` as Date)
FROM `users` U
    LEFT JOIN `user_source_history` USH on U.user_id = USH.user_id
WHERE  `user_last_interaction_date` > 
    (SELECT DATE_ADD(`user_last_interaction_date`, INTERVAL -15 DAY) 
     FROM `users`
     GROUP BY CAST(`user_last_interaction_date` as Date)
    )
GROUP BY CAST(`user_last_interaction_date` as Date)

I tried this query, but SQL says Subquery returns more than one row

How can I split the query to run for more than one row??

score 1 · Accepted Answer · answered May 08 '15 at 08:24

Here is the query to find no active users from '2015-05-03' to '2015-05-18'

You would need to generate all the dates using calendar_date, which can be your date field in your database schema.

SELECT c.calendar_date, count(*) active_users  
FROM `users` U 
LEFT JOIN `user_source_history` USH 
 on( U.user_id = USH.user_id )
CROSS JOIN (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 '2015-05-03' and '2015-05-18') as c 
on 
(date(USH.user_last_interaction_date) < c.calendar_date
and date(USH.user_last_interaction_date) >= (SELECT DATE_ADD(c.calendar_date, INTERVAL -15 DAY)))
group by c.calendar_date

score 0 · Answer 2 · answered May 07 '15 at 10:35

0

Here's an example query that looks for users that were active in the last 15 days:

select  distinct name
from    users u
join    user_source_history uh
on      uh.user_id = u.user_id
where   user_last_interaction_date between
            now() and now() - interval 15 day

answered May 07 '15 at 10:35

Andomar

232,371
49
380
404

I tried this but this query is giving me users for active users from now. But i want to find active users for all the date range that is active users/ perday – nirvair May 07 '15 at 11:37

To find all the active users in a bucket of 15 days in SQL

2 Answers2