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…