Tricky SQL SELECT statement
I have a MySQL table that contains userID's and dates. A row is created each time a user logs in, with the current date added.
I then end up with several rows with the same userID, and either the same or different dates, depending on if they logged in multiple times on the same day or on different days.
I need to find out how many users are logging in (i.e. active users) per week. I can create a select statement to give me a result for the number of active users in the last 7 days for example by doing:
SELECT COUNT(DISTINCT USER_ID) AS NUMENTRIES FROM $table WHERE CREATED_DATE > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
However, if I want to get this info but for every week since the logging began, I don't want to have to do a separate query for each week as this will take forever and bog down my server...Some of the tables that this has to work with have over a million records !!
What I need is a single select statement that will give me a total number of unique users that logged in each week. I have been looking into the GROUP BY feature, but am not sure as to how this can be integrated to give me a result set that is basically an array of totals for each week.
Hope this makes sense. Anyone got any ideas.