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)
Advertisement
Answer
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)