MySQL 8.0

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

CREATE TABLE mytable
(amount_signed_in INT,
amount_registered INT, 
created_at DATETIME,
updated_at DATETIME);

INSERT INTO mytable VALUES
(1 ,0,'2021-07-07 09:23:09','2021-07-07 09:23:09'),
(1 ,1,'2021-07-07 09:33:07','2021-07-07 09:33:07'),
(2 ,1,'2021-07-07 09:33:07','2021-07-07 09:33:07'),
(2 ,1,'2021-07-07 13:50:47','2021-07-07 13:50:47'),
(3 ,1,'2021-07-07 13:57:12','2021-07-07 13:57:12'),
(4 ,1,'2021-07-07 13:58:42','2021-07-07 13:58:42'),
(4 ,2,'2021-07-07 15:20:29','2021-07-07 15:20:29'),
(10,2,'2021-07-07 20:18:25','2021-07-07 20:18:25'),
(1,0,'2021-07-08 13:04:27','2021-07-08 13:04:27');
SELECT amount_signed_in, amount_registered, created_at, updated_at, rnum 
FROM
(SELECT *,
       ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at DESC) AS rnum
FROM mytable) A
WHERE rnum=1;


amount_signed_in
amount_registered
created_at
updated_at
rnum


10
2
2021-07-07 20:18:25
2021-07-07 20:18:25
1

1
0
2021-07-08 13:04:27
2021-07-08 13:04:27
1


SELECT amount_signed_in, amount_registered, created_at, updated_at, rnum 
FROM
(SELECT *,
       @rownumber:=CASE
        WHEN @date = DATE(created_at) THEN @rownumber + 1
          ELSE 1 END AS rnum,
    @date:=DATE(created_at)
FROM mytable
CROSS JOIN (SELECT @date := 0, @rownumber := 0) AS N 
ORDER BY created_at DESC) A
WHERE rnum=1
ORDER BY created_at;


amount_signed_in
amount_registered
created_at
updated_at
rnum


10
2
2021-07-07 20:18:25
2021-07-07 20:18:25
1

1
0
2021-07-08 13:04:27
2021-07-08 13:04:27
1

amount_signed_in	amount_registered	created_at	updated_at	rnum
10	2	2021-07-07 20:18:25	2021-07-07 20:18:25	1
1	0	2021-07-08 13:04:27	2021-07-08 13:04:27	1