Drill 2 of 9 in Hospitality questions
0

Interview Question Bank · COUNT(DISTINCT) · HAVING

2. Guests who stayed at 3+ different hotels

EasyHospitalityQuestion #207

Concept: COUNT(DISTINCT) · HAVINGshow

"How many different X" is COUNT(DISTINCT x), not COUNT(*): a guest who returns to the same hotel adds rows but not hotels. Filter the rows that count before grouping (WHERE), then filter the groups by their counts after grouping (HAVING), since WHERE can't see aggregates.

Example (different data):

SELECT customer_id, COUNT(DISTINCT store_id) AS stores, COUNT(*) AS visits
FROM store_visits
WHERE completed
GROUP BY customer_id
HAVING COUNT(DISTINCT store_id) >= 3;

Your task

Tidewell Hotels wants to thank guests who have explored the group, with a welcome gift for anyone who has stayed at three or more of its hotels.

Using completed stays only (status 'stayed' in tr_bookings), find guests who stayed at 3 or more different hotels. Return guest (name from tr_guests), hotels (distinct hotels stayed at) and stays (stayed bookings). Sort by hotels descending, then guest.

Starting Postgres in your browser…

Checking your access…