Longest Stay per Guest

Return each guest and the number of nights of their longest booking, ordered by nights descending then guest.

Table schema

Bookings(id INT PRIMARY KEY, room INT, guest VARCHAR(10), check_in DATE, check_out DATE)

Sample data

id | room | guest | check_in | check_out
1 | 101 | Ann | 2026-07-01 | 2026-07-05
2 | 101 | Bob | 2026-07-04 | 2026-07-08
3 | 102 | Cy | 2026-07-02 | 2026-07-04
4 | 101 | Dee | 2026-07-08 | 2026-07-10
5 | 103 | Eli | 2026-07-06 | 2026-07-10