CODEWITHSUNDEEP
phpmysqlsqldatabase
Sankar V
Sankar V

Reputation: 4128

Mysql - Users Analysis with in a date range

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).

enter image description here

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.

Upvotes: 0

Views: 57

Answers (2)

Sankar V
Sankar V

Reputation: 4128

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

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

try this query

    select CASE WHEN dates_of_activity>16 THEN count(*) ELSE
           CASE WHEN dates_of_activity>6 and dates_of_activity<17 THEN count(*) ELSE CASE WHEN dates_of_activity>0 and dates_of_activity<7 THEN count(*) ELSE count(*)
            END
            END END as count,
CASE WHEN dates_of_activity>16 THEN 'Above 16' ELSE
           CASE WHEN dates_of_activity>6 and dates_of_activity<17 THEN ' >6 and<16' ELSE CASE WHEN dates_of_activity>0 and dates_of_activity<7 THEN '>0 and <6' ELSE 'never use'
            END
            END END as typeday
 from (SELECT COUNT( DISTINCT date( date_Added ) ) AS dates_of_activity
           , user_id 
    FROM activity_tracker 
    WHERE date_added >= DATE( NOW( ) ) - INTERVAL 28 DAY 
     GROUP BY user_id)a
     LIMIT 0 , 30

Upvotes: 0

Related Questions