SQL Query for the Last 6 Months of users registered

Question

I have created a query that shows the last 3 months of users registered to my site and currently have it grouped by day to show on a line graph. I would prefer to show it Grouped by Week on the line graph but have been unable to change my query successfully.

Here is what I have written so far:

SELECT 
DATE(user_registered), COUNT(user_id)
FROM   wp_users
     JOIN wp_usermeta
     ON wp_usermeta.user_id = wp_users.id
WHERE (meta_value  LIKE '%subscriber%')
AND   (DATE(user_registered) > date_sub(curdate(), INTERVAL 3 MONTH) AND user_registered < now())
GROUP BY DATE(user_registered)

without knowledge of table structure and sample data no one can answer. please share scripts for table, sample data. — Amit Verma, Aug 19 '22 at 21:55
select and group by `WEEK(user_registered)` instead of `DATE(user_registered)` — Barmar, Aug 19 '22 at 21:56
Do you really need `AND user_registered < now()`? Is it possible for a user to register in the future? — Barmar, Aug 19 '22 at 21:56
If a user has multiple meta values containing `subscriber` you'll count them all separately. Use `COUNT(DISTINCT user_id)` so you only count each user once. — Barmar, Aug 19 '22 at 21:58

score 0 · Accepted Answer · answered Aug 19 '22 at 22:10

You can GROUP BY WEEK and as nobody can register in the future the condition is obsolete

SELECT 
WEEK(user_registered), COUNT(user_id)
FROM   wp_users
     JOIN wp_usermeta
     ON wp_usermeta.user_id = wp_users.id
WHERE (meta_value  LIKE '%subscriber%')
AND   (user_registered > date_sub(curdate(), INTERVAL 6 MONTH) )
GROUP BY WEEK(user_registered)

SQL Query for the Last 6 Months of users registered

1 Answers1