You are working as a data analyst for a hotel booking platform similar to Booking.com or Airbnb. The platform collects guest reviews after each stay, and the business team wants to surface only the highest-quality hotels to promote in their marketing campaigns. Your job is to identify hotels that consistently receive strong ratings from guests. The platform has two tables: hotels , which stores hotel information including hotel_id , hotel_name , and city ; and reviews , which stores guest feedback including review_id , hotel_id (foreign key linking to hotels), guest_name , and rating (an integer from 1 to 5). A single hotel can have many reviews over time. Calculate the average rating for each hotel across all its reviews. Only include hotels whose average rating is strictly greater than 4.0 . Return the hotel name , city , number of reviews (aliased as total_reviews ), and the average rating (aliased as avg_rating ) rounded to 2 decimal places. Sort the results by avg_rating in descending order . Hint: Use a JOIN to combine the two tables, then GROUP BY the hotel. Remember that filtering on aggregated values (like averages) must be done using HAVING , not WHERE . A common pitfall is rounding before filtering — always filter first, then round for display.

Table Setup (SQL Schema)

CREATE TABLE hotels (
  hotel_id SERIAL PRIMARY KEY,
  hotel_name VARCHAR(100) NOT NULL,
  city VARCHAR(100) NOT NULL
);

CREATE TABLE reviews (
  review_id SERIAL PRIMARY KEY,
  hotel_id INT NOT NULL REFERENCES hotels(hotel_id),
  guest_name VARCHAR(100) NOT NULL,
  rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5)
);

INSERT INTO hotels (hotel_name, city) VALUES
  ('The Grand Palms', 'Mumbai'),
  ('Sunset Boulevard Inn', 'Bangalore'),
  ('Ocean Breeze Resort', 'Goa'),
  ('City Lights Hotel', 'Delhi'),
  ('The Heritage Stay', 'Jaipur'),
  ('Metro Suites', 'Hyderabad');

INSERT INTO reviews (hotel_id, guest_name, rating) VALUES
  (1, 'Arjun Mehta', 5),
  (1, 'Priya Sharma', 4),
  (1, 'Rohan Das', 5),
  (1, 'Sneha Iyer', 5),
  (2, 'Kiran Rao', 3),
  (2, 'Divya Nair', 4),
  (2, 'Anil Kumar', 3),
  (2, 'Meena Pillai', 2),
  (3, 'Suresh Babu', 5),
  (3, 'Lakshmi Reddy', 5),
  (3, 'Vijay Anand', 4),
  (3, 'Pooja Menon', 5),
  (3, 'Rahul Joshi', 5),
  (4, 'Neha Gupta', 2),
  (4, 'Amit Singh', 3),
  (4, 'Ritu Verma', 2),
  (4, 'Sanjay Patel', 4),
  (5, 'Kavya Nambiar', 5),
  (5, 'Deepak Sharma', 5),
  (5, 'Ananya Roy', 4),
  (5, 'Tarun Bose', 5),
  (6, 'Faisal Khan', 4),
  (6, 'Swathi Reddy', 4),
  (6, 'Manoj Tiwari', 3),
  (6, 'Rekha Nair', 4);