Mysql - 日期范围内的用户分析

Question

I've an activity tracker table with activity_id (Primary key, auto increment), user_id, api_function and date_added fields (please find the screenshot attached).

By using the below query I was able to count the number of entries per user in the last 28 days:

SELECT COUNT( DISTINCT date(date_Added) ) AS day_of_activity, user_id
       FROM activity_tracker
       WHERE date_added >= DATE( NOW() ) - INTERVAL 28 DAY
       GROUP BY user_id
       LIMIT 0 , 30

like:

days_of_activity    user_id
34  1
1   3
13  9
2  10
1  11
8  12

I need to track the count of users who have:

 more than 16 entries in the past 28 days
 between 6 to 16 in the past 28 days, 
 1 to 6 in the past 28 days, 
 no entries in the past 30 days, 
 no entries in the past 90 days and 
 no entries in the past 180 days. 

Is it possible to do this in single mysql query?

Please help me. Thanks in advance.

Answer 1

Please find the answer below:

SELECT 

SUM(
CASE WHEN day_of_activity>16  AND last_activity_date >= DATE(NOW()) - INTERVAL 28 DAY  
THEN 1 ELSE 0
END)
as daily_users_count,

SUM(
CASE WHEN day_of_activity>6 AND  day_of_activity <=16 AND last_activity_date >= DATE(NOW()) - INTERVAL 28 DAY 
THEN 1 ELSE 0
END)
as weekly_users_count,

SUM(
CASE WHEN day_of_activity>=1 AND  day_of_activity <=6 AND last_activity_date >= DATE(NOW()) - INTERVAL 28 DAY 
THEN 1 ELSE 0
END)
as monthly_users_count,

SUM(
CASE WHEN DATE_SUB(CURDATE(),INTERVAL 30 DAY) >= last_activity_date OR last_activity_date IS NULL 
THEN 1 ELSE 0
END)
as not_in_30,

SUM(
CASE WHEN DATE_SUB(CURDATE(),INTERVAL 90 DAY) >= last_activity_date OR last_activity_date IS NULL 
THEN 1 ELSE 0
END)
as not_in_90,

SUM(
CASE WHEN DATE_SUB(CURDATE(),INTERVAL 180 DAY) >= last_activity_date OR last_activity_date IS NULL  
THEN 1 ELSE 0
END)
as not_in_180

FROM (
SELECT COUNT(DISTINCT date(at.date_added)) as day_of_activity, a.user_id, max(at.date_added) as last_activity_date
FROM accounts a 
LEFT JOIN activity_tracker at ON a.user_id = at.user_id
WHERE a.user_role_id = 2
GROUP BY a.user_id
)temp