8 Marriott SQL Interview Questions (Updated 2024)
At Marriott International, SQL is used for analyzing customer booking patterns, allowing the company to identify peak travel times and make promotional offers accordingly. They also rely on SQL to optimize hotel inventory usage, ensuring that rooms are available when demand is high and maximizing occupancy rates, for this reason, Marriott asks SQL query questions in interviews for Data Science, Analytics, and Data Engineering jobs.
So, to help you prepare, we've curated 8 Marriott International SQL interview questions – able to solve them?
8 Marriott International SQL Interview Questions
SQL Question 1: Identify the Top Spenders at Marriott Hotels
Given the Marriott customer database, write a SQL query to identify the 'Whale users'. 'Whale users' are defined as the customers who stay at Marriott hotels frequently and spend a significantly higher amount than the average customer.
Here, frequency might be defined as total number of stays in the last year, and spending is defined simply as the total amount spent by the customer in the last year.
Example Input:
| booking_id | user_id | hotel_id | check_in_date | check_out_date | total_price |
|---|---|---|---|---|---|
| 6171 | 123 | 200 | 01/01/2022 | 01/07/2022 | 500 |
| 7802 | 265 | 300 | 01/08/2022 | 01/10/2022 | 300 |
| 5293 | 362 | 200 | 01/11/2022 | 01/14/2022 | 700 |
| 6352 | 123 | 200 | 01/15/2022 | 01/22/2022 | 800 |
| 4517 | 192 | 300 | 01/23/2022 | 01/29/2022 | 250 |
Expected Output:
| user_id | total_stays | total_spent |
|---|---|---|
| 123 | 2 | 1300 |
| 265 | 1 | 300 |
| 362 | 1 | 700 |
| 192 | 1 | 250 |
Answer:
The above query will return the user_id of all the 'Whale users', along with their total stays at Marriott and the total amount they have spent in the last year. It filters out the users who have spent less than the average amount spent by all users. The results are ranked in decreasing order of the amounts spent by the 'Whale users'.
To solve a similar power-user data analysis problem question on DataLemur's free online SQL code editor, try this recently asked Microsoft SQL interview question:
Explore the latest news from Marriott to see how they are enhancing guest experiences and adapting to the hospitality industry's challenges! Staying updated on Marriott's initiatives can provide insights into the evolving landscape of travel and accommodation.
SQL Question 2: Employees Earning More Than Managers
Given a table of Marriott employee salaries, write a SQL query to find all employees who make more than their own manager.
Marriott Example Input:
| employee_id | name | salary | department_id | manager_id |
|---|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 | |
| 2 | Daniel Rodriguez | 2230 | 1 | 10 |
| 3 | Olivia Smith | 8000 | 1 | 8 |
| 4 | Noah Johnson | 6800 | 2 | 8 |
| 5 | Sophia Martinez | 1750 | 1 | 10 |
| 8 | William Davis | 7000 | 2 | NULL |
| 10 | James Anderson | 4000 | 1 | NULL |
Example Output:
| employee_id | employee_name |
|---|---|
| 3 | Olivia Smith |
This is the output because Olivia Smith earns $8,000, surpassing her manager, William Davis who earns 7,800.
Test your SQL query for this question and run your code right in DataLemur's online SQL environment:
Answer:
First, we perform a SELF-JOIN where we treat the first table () as the managers' table and the second table () as the employees' table. Then we use a clause to filter the results, ensuring we only get employees whose salaries are higher than their manager's salary.
If the solution above is confusing, you can find a step-by-step solution here: Employees Earning More Than Managers.
SQL Question 3: What's a database view?
Database views are created to provide customized, read-only versions of your data that you can query just like a regular table. So why even use one if they're so similar to a regular table?
Views are advantageous for several reasons:
- views allow you to create a simpler versions of your data for specific users (such as hiding extraneous columns/rows from business analysts since they're relics of the Data Engineering pipelines setup)
- views help you comply with data security requirements by hiding sensitive data from certain users (important for regulated industries like govermnet and healthcare!)
- views can improve performance for complicated queries by pre-computing the results and caching them in a view (which is often faster than re-executing the original query)
SQL Question 4: Average Rating of Marriott Hotels by Month
As a data analyst at Marriott, you've been tasked with analysing the customer review data in order to assess the average rating of each hotel by month. The reviews data is stored in a table named . The table columns are as follows:
- : The unique identifier of each review.
- : The unique identifier of each user.
- : The date the review was submitted.
- : The unique identifier of the hotel.
- : The star rating given by the user.
Please write a SQL query to analyze this data and produce an output table that shows the average ratings by each hotel per month.
'Standout from the crowd' enhancement: Can you redefine the column in terms of year and month. For example, 2022-07-01 should be represented as 202207.
Example Input:
| review_id | user_id | review_date | hotel_id | rating |
|---|---|---|---|---|
| 1 | 101 | 2022-07-01 | 201 | 4 |
| 2 | 102 | 2022-07-02 | 202 | 5 |
| 3 | 103 | 2022-07-03 | 203 | 3 |
| 4 | 104 | 2022-07-04 | 201 | 2 |
| 5 | 105 | 2022-07-05 | 202 | 4 |
Answer:
This query uses a window function to calculate the average rating for each hotel by year and month. The function is employed to format the as year and month (YYYYMM). The window function computes the average score across all reviews for the same hotel within the same year and month. The output is sorted in ascending order by and then in descending order by , showcasing the hotel with the highest average rating at the top for each period.
p.s. Window functions show up pretty frequently during SQL interviews, so practice the 27+ window function questions on DataLemur
SQL Question 5: What does the operator do?
For a tangible example of in PostgreSQL, suppose you were doing an HR Analytics project for Marriott, and had access to Marriott's contractors and employees data. Assume that some employees were previously contractors, and vice versa, and thus would show up in both tables.
You could use operator to find all contractors who never were a employee using this query:
Note that is available in PostgreSQL and SQL Server, while is the equivalent operator which is available in MySQL and Oracle (but don't worry about knowing which RDBMS supports which exact commands since Marriott interviewers aren't trying to trip you up on memorizing SQL syntax).
SQL Question 6: Filter Marriott Customer Database Based on Boolean Conditions
Marriott International has a customer database that contains important details about its customers, such as their location, age, number of bookings, the average amount they spend per booking and whether they are part of the Marriott Bonvoy loyalty program or not.
As a Data Analyst at Marriott International, you are tasked with extracting a subset of these customer records based on the following conditions:
- The customer is aged between 25 and 50 years
- The customer is part of the Marriott Bonvoy loyalty program
- The customer has made bookings worth more than $2000 in total
Below are some example records:
Example Input:
| customer_id | name | age | location | total_bookings | average_spending | loyalty_member |
|---|---|---|---|---|---|---|
| 100 | John Doe | 45 | New York | 5 | 500 | True |
| 101 | Melissa | 28 | California | 7 | 300 | False |
| 102 | Mike | 40 | Florida | 2 | 1500 | True |
| 103 | Emma | 30 | Texas | 4 | 600 | True |
| 104 | Oliver | 55 | Nevada | 3 | 800 | False |
Answer:
This PostgreSQL query will return all records from the table where the customer's age is between 25 and 50, they are a part of the loyalty program, and their total spendings (calculated as the product of total bookings and average spending) are more than $2000.
SQL Question 7: Name the different types of joins in SQL. What does each one do?
A join in SQL allows you to retrieve data from multiple tables and combine it into a single set of results.
In SQL, there are four different types of JOINs. To demonstrate each kind, imagine you had two database tables, an advertising campaigns table which had information on Google Ads keywords and how much was bid for each keyword, and a sales table, which has data on how many products were sold and which Google Ads keyword drove that sale.
: An INNER JOIN retrieves rows from both tables where there is a match in the shared key or keys. For example, an INNER JOIN between the table and the table could be performed using the keyword column as the shared key. This would retrieve only the rows where the keyword in the table matches the keyword in the table.
: A LEFT JOIN retrieves all rows from the left table (in this case, the table) and any matching rows from the right table (the table). If there is no match in the right table, values will be returned for the right table's columns.
: A RIGHT JOIN retrieves all rows from the right table (in this case, the table) and any matching rows from the left table (the table). If there is no match in the left table, values will be returned for the left table's columns.
: A FULL OUTER JOIN retrieves all rows from both tables, regardless of whether there is a match in the shared key or keys. If there is no match, values will be returned for the columns of the non-matching table.
SQL Question 8: Average Room Occupancy
As an analyst at Marriott, you have been tasked to find out the average occupancy of all the hotel rooms each month. Write a SQL query to find out the average number of guests per room, per month.
Your data is in two tables and .
Example Input:
| room_id | hotel_id | capacity |
|---|---|---|
| 101 | 1 | 2 |
| 102 | 1 | 4 |
| 201 | 2 | 3 |
| 202 | 2 | 2 |
| 301 | 3 | 3 |
Example Input:
| booking_id | room_id | check_in_date | check_out_date | guest_count |
|---|---|---|---|---|
| 111 | 101 | 03/01/2022 | 03/07/2022 | 2 |
| 222 | 102 | 04/02/2022 | 04/04/2022 | 3 |
| 333 | 201 | 03/10/2022 | 03/20/2022 | 2 |
| 444 | 202 | 04/05/2022 | 04/07/2022 | 1 |
| 555 | 301 | 03/15/2022 | 03/25/2022 | 2 |
Example Output:
| month | hotel_id | average_occupancy |
|---|---|---|
| 3 | 1 | 2.00 |
| 3 | 2 | 2.00 |
| 3 | 3 | 2.00 |
| 4 | 1 | 3.00 |
| 4 | 2 | 1.00 |
Answer:
This query extracts the month from the check-in dates in the table and then joins the with the table based on the . It groups the data by month and , calculating the average guest count for each group. Finally, it orders the results by month and .
To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for calculating average values on a monthly basis or this Facebook Active User Retention Question which is similar for analysing user activity data monthly.
How To Prepare for the Marriott SQL Interview
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Marriott SQL interview is to solve as many practice SQL interview questions as you can! Besides solving the earlier Marriott SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like Netflix, Google, and Amazon.
Each problem on DataLemur has multiple hints, fully explained answers along with a discussion board to see how others solved it and crucially, there's an interactive coding environment so you can right in the browser run your SQL query answer and have it executed.
To prep for the Marriott SQL interview you can also be helpful to solve interview questions from other hospitality and restaurant companies like:
- McDonald's SQL questions
- Starbucks SQL questions
- Bloomin' Brands SQL questions
- Domino's SQL questions
But if your SQL coding skills are weak, don't worry about going right into solving questions – strengthen your SQL foundations with this SQL tutorial for Data Analytics.
This tutorial covers things like using LIKE and ordering data – both of these show up routinely during Marriott SQL interviews.
Marriott International Data Science Interview Tips
What Do Marriott Data Science Interviews Cover?
Besides SQL interview questions, the other question categories to prepare for the Marriott Data Science Interview include:
- AB Testing & Experimentation Questions
- Python or R Coding Questions
- Data Case Study Questions
- ML Interview Questions
- Behavioral Questions centered on Marriott cultural values
How To Prepare for Marriott Data Science Interviews?
To prepare for Marriott Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from FAANG, tech startups, and Wall Street
- a refresher covering Python, SQL & ML
- over 1000+ reviews on Amazon & 4.5-star rating
Don't forget about the behavioral interview – prep for that with this list of common Data Scientist behavioral interview questions.