MariaDB 10.9

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE bookings (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `bookingDate` date NOT NULL
);

/* Add missing index on bookings */
ALTER TABLE bookings ADD INDEX idx_booking_date (bookingDate);

INSERT INTO bookings (bookingDate) VALUES
  /* five bookings for 2023-09-01, so not output */
  ('2023-09-01'), ('2023-09-01'), ('2023-09-01'), ('2023-09-01'), ('2023-09-01'),

  /* four bookings for 2023-09-02 so will be output */
  ('2023-09-02'), ('2023-09-02'), ('2023-09-02'), ('2023-09-02'),

  /* two bookings for 2023-09-16 so will be output */
  ('2023-09-16'), ('2023-09-16'),

  /* six bookings for 2023-09-28 so not output */
  ('2023-09-28'), ('2023-09-28'), ('2023-09-28'), ('2023-09-28'), ('2023-09-28'), ('2023-09-28');

Records: 0  Duplicates: 0  Warnings: 0

Records: 17  Duplicates: 0  Warnings: 0

/* This does not return dates with zero bookings */
SELECT bookingDate
FROM bookings
GROUP BY bookingDate
HAVING COUNT(*) < 5;

/* Doing the inverse just feels wrong, to me at least */
SELECT bookingDate
FROM bookings
GROUP BY bookingDate
HAVING COUNT(*) >= 5;

bookingDate

2023-09-02

2023-09-16

bookingDate

2023-09-01

2023-09-28

/*
 * Use a sequence to create the full list of days
 * passing in the first day of the month and altering
 * the seq based on number of days in month
 */
SELECT '2023-09-01' + INTERVAL (seq - 1) DAY AS dt
FROM seq_1_to_30;

SELECT '2023-02-01' + INTERVAL (seq - 1) DAY AS dt
FROM seq_1_to_28;

/* Or if always for CURRENT_MONTH */
SELECT CURRENT_DATE - INTERVAL (DAY(CURRENT_DATE) - 1) DAY + INTERVAL (seq - 1) DAY AS dt
FROM seq_1_to_31
WHERE seq <= DAY(LAST_DAY(CURRENT_DATE));

dt

2023-09-01

2023-09-02

2023-09-03

2023-09-04

2023-09-05

2023-09-06

2023-09-07

2023-09-08

2023-09-09

2023-09-10

2023-09-11

2023-09-12

2023-09-13

2023-09-14

2023-09-15

2023-09-16

2023-09-17

2023-09-18

2023-09-19

2023-09-20

2023-09-21

2023-09-22

2023-09-23

2023-09-24

2023-09-25

2023-09-26

2023-09-27

2023-09-28

2023-09-29

2023-09-30

dt

2023-02-01

2023-02-02

2023-02-03

2023-02-04

2023-02-05

2023-02-06

2023-02-07

2023-02-08

2023-02-09

2023-02-10

2023-02-11

2023-02-12

2023-02-13

2023-02-14

2023-02-15

2023-02-16

2023-02-17

2023-02-18

2023-02-19

2023-02-20

2023-02-21

2023-02-22

2023-02-23

2023-02-24

2023-02-25

2023-02-26

2023-02-27

2023-02-28

dt

2023-09-01

2023-09-02

2023-09-03

2023-09-04

2023-09-05

2023-09-06

2023-09-07

2023-09-08

2023-09-09

2023-09-10

2023-09-11

2023-09-12

2023-09-13

2023-09-14

2023-09-15

2023-09-16

2023-09-17

2023-09-18

2023-09-19

2023-09-20

2023-09-21

2023-09-22

2023-09-23

2023-09-24

2023-09-25

2023-09-26

2023-09-27

2023-09-28

2023-09-29

2023-09-30

SELECT s.dt, COUNT(b.id) AS num_bookings
FROM (
    SELECT '2023-09-01' + INTERVAL (seq - 1) DAY AS dt
    FROM seq_1_to_30
) s
LEFT JOIN bookings b
    ON s.dt = b.bookingDate
GROUP BY s.dt
HAVING num_bookings < 5;

dt
num_bookings

2023-09-02
4

2023-09-03
0

2023-09-04
0

2023-09-05
0

2023-09-06
0

2023-09-07
0

2023-09-08
0

2023-09-09
0

2023-09-10
0

2023-09-11
0

2023-09-12
0

2023-09-13
0

2023-09-14
0

2023-09-15
0

2023-09-16
2

2023-09-17
0

2023-09-18
0

2023-09-19
0

2023-09-20
0

2023-09-21
0

2023-09-22
0

2023-09-23
0

2023-09-24
0

2023-09-25
0

2023-09-26
0

2023-09-27
0

2023-09-29
0

2023-09-30
0

/* Aggregate bookings in subquery - slightly more efficient but
 * only worthwhile with large dataset */
SELECT s.dt
FROM (
    SELECT '2023-09-01' + INTERVAL (seq - 1) DAY AS dt
    FROM seq_1_to_30
) s
LEFT JOIN (
    SELECT bookingDate
    FROM bookings
    WHERE bookingDate BETWEEN '2023-09-01' AND '2023-09-30'
    GROUP BY bookingDate
    HAVING COUNT(*) >= 5
) b ON s.dt = b.bookingDate
WHERE b.bookingDate IS NULL;

dt

2023-09-02

2023-09-03

2023-09-04

2023-09-05

2023-09-06

2023-09-07

2023-09-08

2023-09-09

2023-09-10

2023-09-11

2023-09-12

2023-09-13

2023-09-14

2023-09-15

2023-09-16

2023-09-17

2023-09-18

2023-09-19

2023-09-20

2023-09-21

2023-09-22

2023-09-23

2023-09-24

2023-09-25

2023-09-26

2023-09-27

2023-09-29

2023-09-30

/* Using NOT EXISTS, because we can */
SELECT s.dt
FROM (
    SELECT '2023-09-01' + INTERVAL (seq - 1) DAY AS dt
    FROM seq_1_to_30
) s
WHERE NOT EXISTS (
    SELECT 1 FROM bookings
    WHERE bookingDate = s.dt
    GROUP BY bookingDate
    HAVING COUNT(*) >= 5
);

dt

2023-09-02

2023-09-03

2023-09-04

2023-09-05

2023-09-06

2023-09-07

2023-09-08

2023-09-09

2023-09-10

2023-09-11

2023-09-12

2023-09-13

2023-09-14

2023-09-15

2023-09-16

2023-09-17

2023-09-18

2023-09-19

2023-09-20

2023-09-21

2023-09-22

2023-09-23

2023-09-24

2023-09-25

2023-09-26

2023-09-27

2023-09-29

2023-09-30
dt	num_bookings
2023-09-02	4
2023-09-03	0
2023-09-04	0
2023-09-05	0
2023-09-06	0
2023-09-07	0
2023-09-08	0
2023-09-09	0
2023-09-10	0
2023-09-11	0
2023-09-12	0
2023-09-13	0
2023-09-14	0
2023-09-15	0
2023-09-16	2
2023-09-17	0
2023-09-18	0
2023-09-19	0
2023-09-20	0
2023-09-21	0
2023-09-22	0
2023-09-23	0
2023-09-24	0
2023-09-25	0
2023-09-26	0
2023-09-27	0
2023-09-29	0
2023-09-30	0