MySQL - 选择时间范围内的行分组日期和ID

Question

I'm having trouble with a query I need to get some data from my db.

I have a table with the following data:

id    user_id     game_id    date
1     1           1          2015-08-19
2     1           2          2015-08-19
3     1           3          2015-08-19
4     2           1          2015-08-20
5     2           2          2015-08-20
6     1           1          2015-08-20
7     1           1          2015-08-20
8     3           1          2015-08-20
9     2           1          2015-08-21
10    1           1          2015-08-21
11    1           2          2015-08-21
12    2           1          2015-08-21

I need to get the total amount of users that "played" on a date range, i.e 2015-08-10 to 2015-08-22, grouped by date and user_id. For example, with this data given the result should be:

total_players    date
1                2015-08-19
3                2015-08-20
2                2015-08-21

I've tried this the following query but it doesn't group correctly:

SELECT date, COUNT(*) AS total_players 
FROM plays 
WHERE date BETWEEN '$start_date' AND '$end_date' 
GROUP BY date, user_id

Can anybody help me with the correct query? Thanks everyone in advance.

Answer 1

SELECT date, COUNT(DISTINCT user_id) 
FROM plays
WHERE date BETWEEN '$start_date' AND '$end_date' 
GROUP BY date

Tested locally on test database with your data

total_players    date
1                2015-08-19
3                2015-08-20
2                2015-08-21