Workspace
Akash Soni/

Project: SQL Associate Practical Exam

0
Beta
Spinner

Practical Exam: Hotel Operations

LuxurStay Hotels is a major, international chain of hotels. They offer hotels for both business and leisure travellers in major cities across the world. The chain prides themselves on the level of customer service that they offer.

However, the management has been receiving complaints about slow room service in some hotel branches. As these complaints are impacting the customer satisfaction rates, it has become a serious issue. Recent data shows that customer satisfaction has dropped from the 4.5 rating that they expect.

You are working with the Head of Operations to identify possible causes and hotel branches with the worst problems.

Data

The following schema diagram shows the tables available. You have only been provided with data where customers provided a feedback rating.

Unknown integration
DataFrameavailable as
branch
variable
SELECT * FROM public.branch
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
request
variable
SELECT * FROM public.request
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
service
variable
SELECT * FROM public.service
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Task 1

Before you can start any analysis, you need to confirm that the data is accurate and reflects what you expect to see.

It is known that there are some issues with the branch table, and the data team have provided the following data description.

Write a query to return data matching this description. You must match all column names and description criteria.

Column NameCriteria
idNominal. The unique identifier of the hotel.
Missing values are not possible due to the database structure.
locationNominal. The location of the particular hotel. One of four possible values, 'EMEA', 'NA', 'LATAM' and 'APAC'.
Missing values should be replaced with “Unknown”.
total_roomsDiscrete. The total number of rooms in the hotel. Must be a positive integer between 1 and 400.
Missing values should be replaced with the default number of rooms, 100.
staff_countDiscrete. The number of staff employeed in the hotel service department.
Missing values should be replaced with the total_rooms multiplied by 1.5.
opening_dateDiscrete. The year in which the hotel opened. This can be any value between 2000 and 2023.
Missing values should be replaced with 2023.
target_guestsNominal. The primary type of guest that is expected to use the hotel. Can be one of 'Leisure' or 'Business'.
Missing values should be replaced with 'Leisure'.
Unknown integration
DataFrameavailable as
clean_branch_data
variable
-- Write your query for task 1 in this cell
SELECT id,
	COALESCE(location,'Unknown') as location,
	COALESCE(total_rooms,100) as total_rooms,
	COALESCE(staff_count,1.5 * total_rooms) as staff_count,
	CASE WHEN opening_date = '-' OR opening_date IS NULL THEN '2023' ELSE opening_date END AS opening_date,
	CASE WHEN target_guests LIKE 'B%' THEN 'Business'
	     WHEN target_guests LIKE 'L%' THEN 'Leisure'
	     ELSE 'Leisure' END AS target_guests
FROM branch

This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Task 2

The Head of Operations wants to know whether there is a difference in time taken to respond to a customer request in each hotel. They already know that different services take different lengths of time.

Calculate the average and maximum duration for each branch and service. Your output should include the columns service_id, branch_id, avg_time_taken and max_time_taken. Values should be rounded to two decimal places where appropriate.

Unknown integration
DataFrameavailable as
average_time_service
variable
-- Write your query for task 2 in this cell
SELECT service_id, 
		branch_id, 
		ROUND(AVG(time_taken),2) AS avg_time_taken, 
		MAX(time_taken) AS max_time_taken
FROM Request
GROUP BY service_id,branch_id
ORDER BY service_id,branch_id
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Task 3

The management team want to target improvements in Meal and Laundry service in Europe (EMEA) and Latin America (LATAM).

Write a query to return the description of the service, the id and location of the branch, the id of the request as request_id and the rating for the services and locations of interest to the management team.

Use the original branch table, not the output of task 1.

Unknown integration
DataFrameavailable as
target_hotels
variable
-- Write your query for task 3 in this cell

SELECT S.description,
		B.id,
		B.location,
		R.id as request_id,
		R.rating
FROM Request R
JOIN Service S
ON R.service_id = S.id
JOIN Branch B 
ON B.id = R.branch_id
WHERE B.location IN ('LATAM','EMEA') AND S.description IN ('Meal','Laundry')
ORDER BY S.description



This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Task 4

So that you can take a more detailed look at the lowest performing hotels, you want to get service and branch information where the average rating for the branch and service combination is lower than 4.5 - the target set by management.

Your query should return the service_id and branch_id, and the average rating (avg_rating), rounded to 2 decimal places.

Unknown integration
DataFrameavailable as
average_rating
variable
-- Write your query for task 4 in this cell

SELECT service_id,
		branch_id,
		ROUND(AVG(rating),2) as avg_rating
FROM Request
GROUP BY service_id,branch_id
HAVING ROUND(AVG(rating),2) < 4.5
ORDER BY service_id,branch_id
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
  • AI Chat
  • Code