How to group by multiple rows and column

1. For each user 3 codes are there(10,20,30)
2. if all codes(10,20,30) are true then it has to JOINED
3. if any codes(10,20,30) are False then it has to NEGLECTED
4. WE have to check max(date)

Table is below:

+-------+------+-------+------------+
| user  | code | bool  |    date    |
+-------+------+-------+------------+
| user1 |  10  | True  | 31/10/2019 |
| user1 |  20  | True  | 31/10/2019 |
| user1 |  30  | False | 31/10/2019 |
| user2 |  10  | True  | 31/10/2019 |
| user2 |  20  | True  | 31/10/2019 |
| user2 |  30  | True  | 31/10/2019 |
| user3 |  10  | True  | 31/10/2019 |
| user3 |  20  | True  | 31/10/2019 |
| user3 |  30  | True  | 31/10/2019 |
+-------+------+-------+------------+

Output user2 and user3 is JOINED, user1 is NEGLECTED

+------------+--------+-----------+
|    DATE    | JOINED | NEGLECTED |
+------------+--------+-----------+
| 31/10/2019 |    2   |    1      |
+------------+--------+-----------+

Answer

I assume that the column bool is of data type BOOL, so you can do it in 2 levels of aggregation:

select t.date,
  sum(total = 3) joined,
  sum(total <> 3) neglected
from (
  select date, user, sum(bool) total
  from tablename
  group by date, user
) t
group by t.date

See the demo.
Results:

| date       | joined | neglected |
| ---------- | ------ | --------- |
| 31/10/2019 | 2      | 1         |

If the column’s bool data type is VARCHAR then change:

sum(bool) total

to:

sum(bool = 'True') total