Parin Parin - 5 months ago 10
MySQL Question

Mysql query to find hackers who made at least 1 submission each day

I want to find all the hackers who made at least 1 submission each day.

Table looks like: id, date.

1, 2016-01-01
2, 2016-01-01
3, 2016-01-01
1, 2016-01-02
2, 2016-01-02
1, 2016-01-03


So the output should be:
1
Since 1 has made submission on all the day.

What I have tried is:

Select id from table
group by id
having count(*)=3 /* Since number of days are 3 */


However this is incorrect because a hacker can make multiple submissions on a day.
Example where my query will fail:

1, 2016-01-01
2, 2016-01-01
3, 2016-01-01
3, 2016-01-01
3, 2016-01-01
1, 2016-01-02
2, 2016-01-02
1, 2016-01-03

Answer

I believe if you group by both id and date you'll get what you're looking for. i.e.:

SELECT id
FROM table 
GROUP BY id, `date`
HAVING COUNT(*) = 3 /* Since number of days are 3 */

Note that this assumes you are getting the number of days (3 in this case) based on some date range from somewhere else.