Postgres 10

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

select version();


version


PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


create table bookings (hotelid INT, timestampOfBooking date, numberOfGuests INt);
INSERT INTO bookings
VALUES
  (11,'22/11/2021', 2 ),
  (34,'23/11/2021', 2 ),
  (30,'23/11/2021', 5 ),
  (19,'24/11/2021', 7 ),
  (8 ,'25/11/2021', 12),
  (34,'25/11/2021', 5 );
6 rows affected

select * from bookings;


hotelid
timestampofbooking
numberofguests


11
2021-11-22
2

34
2021-11-23
2

30
2021-11-23
5

19
2021-11-24
7

8
2021-11-25
12

34
2021-11-25
5


/* Method 1 - Using DENSE_RANK() */

SELECT timestampOfBooking, total_beds FROM
(
   select timestampOfBooking, 
          sum(numberOfGuests) as total_beds,
          dense_rank() over (order by sum(numberOfGuests) DESC) as rnk
   from bookings
   group by timestampOfBooking
) as sq
where rnk = 2


timestampofbooking
total_beds


2021-11-23
7

2021-11-24
7


/* Method 2 - Using LIMIT AND OFFSET */

SELECT timestampOfBooking, 
       SUM(numberOfGuests) AS total_beds
FROM bookings
GROUP BY timestampOfBooking
HAVING sum(numberOfGuests)=
   (
    SELECT distinct SUM(numberOfGuests) AS total_beds
    FROM bookings
    GROUP BY timestampOfBooking
    ORDER BY total_beds DESC
    OFFSET 1 LIMIT 1
   );


timestampofbooking
total_beds


2021-11-23
7

2021-11-24
7

hotelid	timestampofbooking	numberofguests
11	2021-11-22	2
34	2021-11-23	2
30	2021-11-23	5
19	2021-11-24	7
8	2021-11-25	12
34	2021-11-25	5