MySQL 8.0

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

CREATE TABLE person (
  id    INT(11)
)
INSERT INTO
  person
VALUES
  (3841),
  (3842),
  (3843),
  (3844)

Records: 4  Duplicates: 0  Warnings: 0


CREATE TABLE `my_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_id` int(11) DEFAULT NULL,
  `action` varchar(24) DEFAULT NULL,
  `when_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
)
INSERT INTO `my_history` ( `person_id`, `action`, `when_created`)
VALUES
    ( 3842, 'checked_in', '2022-12-15 08:00:00'),
    ( 3842, 'checked_out', '2022-12-15 09:30:00'),
    ( 3842, 'checked_in', '2022-12-17 09:30:00'),
    ( 3843, 'checked_in', '2022-12-17 08:00:00'),
    ( 3843, 'checked_out', '2022-12-17 09:30:00'),
    ( 3843, 'checked_in', '2022-12-17 11:00:00'),
    ( 3843, 'checked_out', '2022-12-17 13:30:00'),
    ( 3841, 'checked_in', '2022-12-17 08:00:00'),
    ( 3841,  'checked_out', '2022-12-17 17:42:00'),
    ( 3844, 'checked_in', '2022-12-17 22:00:00'),
    ( 3844,  'checked_out', '2022-12-18 06:40:00');
 

Records: 11  Duplicates: 0  Warnings: 0


SELECT * FROM my_history;


id
person_id
action
when_created


1
3842
checked_in
2022-12-15 08:00:00

2
3842
checked_out
2022-12-15 09:30:00

3
3842
checked_in
2022-12-17 09:30:00

4
3843
checked_in
2022-12-17 08:00:00

5
3843
checked_out
2022-12-17 09:30:00

6
3843
checked_in
2022-12-17 11:00:00

7
3843
checked_out
2022-12-17 13:30:00

8
3841
checked_in
2022-12-17 08:00:00

9
3841
checked_out
2022-12-17 17:42:00

10
3844
checked_in
2022-12-17 22:00:00

11
3844
checked_out
2022-12-18 06:40:00


SELECT
  param.check_date,
  COUNT(DISTINCT p.id)
FROM
(
  SELECT timestamp '2022-12-15 00:00'   AS check_date
  UNION ALL
  SELECT timestamp '2022-12-16 00:00'   AS check_date
  UNION ALL
  SELECT timestamp '2022-12-17 00:00'   AS check_date
  UNION ALL
  SELECT timestamp '2022-12-18 00:00'   AS check_date  
)
  AS param
CROSS JOIN
  person       AS p
INNER JOIN
  my_history   AS h
    ON
      h.person_id = p.id
    AND
      h.when_created
      >=
      COALESCE(
        (
          SELECT when_created
            FROM my_history
           WHERE person_id = p.id
             AND when_created <= check_date
        ORDER BY when_created DESC
           LIMIT 1
        ),
        check_date
      )
    AND
      h.when_created < check_date + INTERVAL 1 DAY
    AND
      h.action = 'checked_in'
GROUP BY
  param.check_date


check_date
COUNT(DISTINCT p.id)


2022-12-15 00:00:00
1

2022-12-17 00:00:00
4

2022-12-18 00:00:00
2


SELECT
  param.check_date,
  COUNT(p.id)
FROM
(
  SELECT timestamp '2022-12-15 00:00'   AS check_date
  UNION ALL
  SELECT timestamp '2022-12-16 00:00'   AS check_date
  UNION ALL
  SELECT timestamp '2022-12-17 00:00'   AS check_date
  UNION ALL
  SELECT timestamp '2022-12-18 00:00'   AS check_date  
)
  AS param
LEFT JOIN
  person       AS p
    ON EXISTS (
      SELECT *
        FROM my_history   AS h
       WHERE h.person_id = p.id
         AND h.action = 'checked_in'
         AND h.when_created < check_date + INTERVAL 1 DAY
         AND h.when_created
             >=
           COALESCE(
             (
               SELECT when_created
                 FROM my_history
                WHERE person_id = p.id
                  AND when_created <= check_date
             ORDER BY when_created DESC
                LIMIT 1
             ),
             check_date
           )
      )
GROUP BY
  param.check_date


check_date
COUNT(p.id)


2022-12-15 00:00:00
1

2022-12-16 00:00:00
0

2022-12-17 00:00:00
4

2022-12-18 00:00:00
2


SELECT
  param.check_date,
  COUNT(p.id)
FROM
(
  SELECT timestamp '2022-12-15 00:00'   AS check_date
  UNION ALL
  SELECT timestamp '2022-12-16 00:00'   AS check_date
  UNION ALL
  SELECT timestamp '2022-12-17 00:00'   AS check_date
  UNION ALL
  SELECT timestamp '2022-12-18 00:00'   AS check_date  
)
  AS param
LEFT JOIN
  person       AS p
    ON
      EXISTS (
        SELECT *
          FROM my_history
         WHERE person_id = p.id
           AND when_created >= check_date
           AND when_created <  check_date + INTERVAL 1 DAY
      )
    OR
      'checked_in' = (
        SELECT action
          FROM my_history
         WHERE person_id = p.id
           AND when_created <= check_date
      ORDER BY when_created DESC
         LIMIT 1
      )
GROUP BY
  param.check_date


check_date
COUNT(p.id)


2022-12-15 00:00:00
1

2022-12-16 00:00:00
0

2022-12-17 00:00:00
4

2022-12-18 00:00:00
2

id	person_id	action	when_created
1	3842	checked_in	2022-12-15 08:00:00
2	3842	checked_out	2022-12-15 09:30:00
3	3842	checked_in	2022-12-17 09:30:00
4	3843	checked_in	2022-12-17 08:00:00
5	3843	checked_out	2022-12-17 09:30:00
6	3843	checked_in	2022-12-17 11:00:00
7	3843	checked_out	2022-12-17 13:30:00
8	3841	checked_in	2022-12-17 08:00:00
9	3841	checked_out	2022-12-17 17:42:00
10	3844	checked_in	2022-12-17 22:00:00
11	3844	checked_out	2022-12-18 06:40:00

check_date	COUNT(DISTINCT p.id)
2022-12-15 00:00:00	1
2022-12-17 00:00:00	4
2022-12-18 00:00:00	2

check_date	COUNT(p.id)
2022-12-15 00:00:00	1
2022-12-16 00:00:00	0
2022-12-17 00:00:00	4
2022-12-18 00:00:00	2