MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (id INT, valid INT, user_id INT, created_at TIMESTAMP);
INSERT INTO test VALUES
(1,      1,       1, '2018-01-01 12:55:39'),
(2,      0,       1, '2019-01-02 11:23:33'),
(3,      0,       1, '2019-01-03 16:02:13'),
(4,      1,       1, '2019-01-01 01:12:34'),
(5,      1,       1, '2019-01-02 16:43:58'),
(6,      0,       1, '2019-01-03 16:32:24'),
(7,      1,       1, '2019-01-01 06:54:54'),
(8,      1,       1, '2019-01-02 21:07:08'),
(9,      0,       1, '2019-01-03 03:23:19'),
(10,     1,       2, '2019-01-01 16:43:18'),
(11,     1,       3, '2019-01-02 16:15:29'),
(12,     0,       2, '2019-01-03 06:50:30'),
(13,     0,       2, '2019-01-01 23:03:36'),
(14,     0,       2, '2019-01-02 16:12:45'),
(15,     0,       3, '2019-01-03 22:55:58'),
(16,     1,       2, '2019-01-01 06:55:33'),
(17,     1,       2, '2019-01-02 06:47:16'),
(18,     0,       3, '2019-01-03 16:25:49'),
(19,     1,       3, '2019-01-01 20:05:57'),
(20,     1,       3, '2019-01-01 16:48:19');
SELECT user_id, DATE(created_at) created_at, SUM(valid=1) valid, 100*SUM(valid=1)/COUNT(*) pvalid, SUM(valid=0) invalid, 100*SUM(valid=0)/COUNT(*) pinvalid
FROM test
GROUP BY user_id, DATE(created_at)
ORDER BY user_id, DATE(created_at);


user_id
created_at
valid
pvalid
invalid
pinvalid


1
2018-01-01
1
100.0000
0
0.0000

1
2019-01-01
2
100.0000
0
0.0000

1
2019-01-02
2
66.6667
1
33.3333

1
2019-01-03
0
0.0000
3
100.0000

2
2019-01-01
2
66.6667
1
33.3333

2
2019-01-02
1
50.0000
1
50.0000

2
2019-01-03
0
0.0000
1
100.0000

3
2019-01-01
2
100.0000
0
0.0000

3
2019-01-02
1
100.0000
0
0.0000

3
2019-01-03
0
0.0000
2
100.0000

user_id	created_at	valid	pvalid	invalid	pinvalid
1	2018-01-01	1	100.0000	0	0.0000
1	2019-01-01	2	100.0000	0	0.0000
1	2019-01-02	2	66.6667	1	33.3333
1	2019-01-03	0	0.0000	3	100.0000
2	2019-01-01	2	66.6667	1	33.3333
2	2019-01-02	1	50.0000	1	50.0000
2	2019-01-03	0	0.0000	1	100.0000
3	2019-01-01	2	100.0000	0	0.0000
3	2019-01-02	1	100.0000	0	0.0000
3	2019-01-03	0	0.0000	2	100.0000