11 Marsh & McLennan SQL Interview Questions (Updated 2024)
Marsh & McLennan employees write SQL queries to analyze claims data and policyholder information to identify risk patterns and optimize insurance pricing, as well as to manage client databases for personalized policy recommendations and customer service. That is the reason why Marsh & McLennan often includes SQL questions during interviews for Data Analytics, Data Science, and Data Engineering jobs.
To help prep you for the Marsh & McLennan SQL interview, we've collected 11 Marsh & McLennan SQL interview questions in this blog.
11 Marsh & McLennan SQL Interview Questions
SQL Question 1: Identify the Top Customers at Marsh & McLennan
Marsh & McLennan wants to identify their 'whale' users - users who purchase insurance policies frequently and spend a high amount on these policies. Using the two tables and that present customer related details and purchases details respectively, write a SQL query to identify the top 10 customers based on frequency and amount of purchase in the last 12 months. Also, include their latest purchase date.
Example Input:
| customer_id | first_name | last_name |
|---|---|---|
| 1 | John | Doe |
| 2 | Alice | Smith |
| 3 | Bob | Johnson |
| 4 | Mike | Brown |
| 5 | Lilia | Gonzalez |
Example Input:
| purchase_id | customer_id | purchase_date | amount |
|---|---|---|---|
| 101 | 1 | 2021-06-08 | 5000 |
| 102 | 2 | 2021-06-10 | 6000 |
| 103 | 1 | 2021-07-12 | 2500 |
| 104 | 3 | 2021-11-20 | 3000 |
| 105 | 2 | 2022-01-09 | 4000 |
| 106 | 1 | 2022-04-01 | 5500 |
| 107 | 4 | 2021-10-03 | 1000 |
| 108 | 5 | 2022-06-01 | 500 |
Answer:
This query joins the 'Customers' table with the table on the field. It filters the purchases that are made in the last 12 months using a WHERE clause. It then groups by customer_id (also includes first_name and last_name for display convenience in SELECT statement), and orders the result by the count of the purchases and the total amount spent in descending order. This will give the top 10 customers who made the most purchases and spent the most in the last 12 months. The latest purchase date for each of these customers is also returned by using the MAX() aggregate function on purchase_date.
To solve a similar VIP customer analysis question on DataLemur's free interactive SQL code editor, try this Microsoft SQL Interview problem:
SQL Question 2: Highly-Paid Employees
Given a table of Marsh & McLennan employee salary data, write a SQL query to find employees who earn more money than their own boss.
Marsh & McLennan 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.
Solve this question directly within the browser on DataLemur:
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 code above is hard to understand, you can find a step-by-step solution with hints here: Highly-Paid Employees.
SQL Question 3: When it comes to database normalization, what's the difference between 1NF, 2NF, and 3NF?
Normal forms are guidelines that are used to help design a relational database in a way that minimizes redundancy and ensures the integrity of the data. The 3 most commonly use normal forms are the 1st, 2nd, and 3rd normal forms. Here's a brief explanation of each:
-
1st Normal Form (1NF) is all about keeping it simple - each column should only have one value and there should be no repeating groups of data.
-
2nd Normal Form (2NF) is about organization - your database should already be in 1NF and all the non-key columns should depend on the primary key. This means that each non-key column should be completely dependent on the entire primary key, not just part of it.
-
3rd Normal Form (3NF) is about independence - if your database is already in 2NF, then all the non-key columns should not depend on each other. They should be self-sufficient and not rely on other non-key columns.
SQL Question 4: Find The Average Insurance Premiums for Each Line of Business
As a data analyst at Marsh & McLennan, you are tasked with analyzing data about insurance premiums. For each line of business and each year, calculate the average insurance premiums.
Here is a hypothetical table named :
Example Input:
| premium_id | date_premium | line_of_business | premium |
|---|---|---|---|
| 1001 | 01/01/2020 | Auto | 500 |
| 1002 | 01/01/2020 | Home | 300 |
| 1003 | 02/01/2020 | Auto | 400 |
| 1004 | 02/01/2020 | Home | 350 |
| 1005 | 02/01/2021 | Auto | 600 |
Example Output:
| year | line_of_business | avg_premiums |
|---|---|---|
| 2020 | Auto | 450 |
| 2020 | Home | 325 |
| 2021 | Auto | 600 |
Answer:
You can use the SQL window function to calculate the yearly average insurance premium for each line of business as follows:
This query extracts the year from the column and calculates the average premium for each year and each . Here, specifies on which basis windows of rows with shared characteristics should be formed. Each window includes rows that share the same years and line of business. The function is applied to each of these windows separately. Finally, the clause groups rows that have the same values in specified columns into aggregated data.
p.s. Window functions show up pretty often during SQL interviews, so practice the 27+ window function questions on DataLemur
SQL Question 5: What's a foreign key?
A foreign key is a column or group of columns in a table that refers to the primary key in another table. The foreign key constraint helps maintain referential integrity between the two tables.
Let's examine employee data from Marsh & McLennan's HR database:
:
| employee_id | first_name | last_name | manager_id |
|---|---|---|---|
| 1 | Aubrey | Graham | 3 |
| 2 | Marshal | Mathers | 3 |
| 3 | Dwayne | Carter | 4 |
| 4 | Shawn | Carter |
In this table, serves as the primary key and functions as a foreign key because it links to the of the employee's manager. This establishes a relationship between Marsh & McLennan employees and their managers, allowing for easy querying to find an employee's manager or see which employees report to a specific manager.
The table may also have multiple foreign keys that reference primary keys in other tables. For example, and foreign keys could be used to connect each employee to their respective department and location.
SQL Question 6: Client Portfolio Management
Marsh & McLennan is a professional services firm providing advice and solutions in the areas of risk, strategy and human capital. A typical business problem they might face is managing client portfolios.
Imagine we have two tables:
A table, which includes information about the firm's clients.
Example Input:
| client_id | client_name | sector |
|---|---|---|
| 1 | Acme Corp | Technology |
| 2 | Globex Inc | Finance |
| 3 | Soylent Corp | Food and Beverage |
A table, which includes information about the investments made on behalf of each client.
Example Input:
| investment_id | client_id | asset_class | amount_invested | investment_date |
|---|---|---|---|---|
| 101 | 1 | Equity | $10,000 | 2020-01-01 |
| 102 | 1 | Fixed Income | $20,000 | 2020-01-01 |
| 103 | 2 | Equity | $15,000 | 2021-01-01 |
| 104 | 2 | Fixed Income | $10,000 | 2021-01-01 |
| 105 | 3 | Equity | $5,000 | 2022-01-01 |
Write a PostgreSQL query that finds the total amount invested in each asset class for each client, for investments made in the year 2021 or later.
Answer:
Example Output:
| client_id | client_name | asset_class | total_invested |
|---|---|---|---|
| 2 | Globex Inc | Equity | $15,000 |
| 2 | Globex Inc | Fixed Income | $10,000 |
| 3 | Soylent Corp | Equity | $5,000 |
This query will give the total amount invested in each asset class for each client in the year 2021 or later. It first joins the and table on , then narrows down the data to investments made in the year 2021 or later using a clause. Finally, it groups the data by , , and and calculates the total amount invested in each group.
SQL Question 7: Can you describe the meaning of a constraint in SQL in layman's terms?
Constraints are just rules for your DBMS to follow when updating/inserting/deleting data.
Say you had a table of Marsh & McLennan employees, and their salaries, job titles, and performance review data. Here's some examples of SQL constraints you could implement:
NOT NULL: This constraint could be used to ensure that certain columns in the employee table, such as the employee's first and last name, cannot contain NULL values.
UNIQUE: This constraint could be used to ensure that the employee ID is unique. This would prevent duplicate entries in the employee table.
PRIMARY KEY: This constraint could be used to combine the NOT NULL and UNIQUE constraints to create a primary key for the employee table. The employee ID could serve as the primary key.
FOREIGN KEY: This constraint could be used to establish relationships between the employee table and other tables in the database. For example, you could use a foreign key to link the employee ID to the department ID in a department table to track which department each employee belongs to.
CHECK: This constraint could be used to ensure that certain data meets specific conditions. For example, you could use a CHECK constraint to ensure that salary values are always positive numbers.
DEFAULT: This constraint could be used to specify default values for certain columns. For example, you could use a DEFAULT constraint to set the employee hire date to the current date if no value is provided when a new employee is added to the database.
SQL Question 8: Average Insurance Policy Value
As a data analyst at Marsh & McLennan (a professional services firm and global leader in insurance brokerage and risk management), you are requested to find the average value of insurance policies sold by each broker in the year 2023.
Example Input:
| policy_id | broker_id | issue_date | expiry_date | value |
|---|---|---|---|---|
| 1257 | 507 | 01/15/2023 00:00:00 | 01/14/2024 00:00:00 | 900 |
| 1624 | 120 | 03/08/2023 00:00:00 | 03/07/2024 00:00:00 | 800 |
| 1492 | 507 | 04/20/2023 00:00:00 | 04/19/2024 00:00:00 | 1100 |
| 998 | 120 | 07/05/2023 00:00:00 | 07/04/2024 00:00:00 | 700 |
| 1033 | 152 | 02/16/2023 00:00:00 | 02/15/2024 00:00:00 | 950 |
Example Output:
| broker_id | avg_policy_value |
|---|---|
| 507 | 1000 |
| 120 | 750 |
| 152 | 950 |
Answer:
This PostgreSQL query calculates the average policy value for each broker within the year 2023. It first filters out policy records issued in 2023 using a WHERE clause. Then it groups the filtered data by broker_id and calculates the average value of their respective policies using the AVG() function. The result is a table showing each broker's id and their average policy value for 2023.
To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for calculating averages or this Alibaba Compressed Mean Question which is similar for aggregating and analyzing data.
SQL Question 9: Calculate the Click-Through-Rate (CTR) for Marsh & McLennan's Digital Ads
Marsh & McLennan, a professional services firm, has recently run several marketing campaigns. The company wants to know the Click-Through Rate (CTR) per campaign, per day to analyze how effective each marketing campaign is.
In this context, CTR is calculated as total number of clicks on a campaign link divided by the total number of times the campaign was served, displayed as percentage.
We have two tables namely and .
Example Input:
| campaign_id | campaign_name | display_date | displays |
|---|---|---|---|
| 1 | Campaign A | 2022-09-01 | 10000 |
| 2 | Campaign B | 2022-09-01 | 20000 |
| 1 | Campaign A | 2022-09-02 | 15000 |
| 2 | Campaign B | 2022-09-02 | 25000 |
Example Input:
| click_id | campaign_id | click_date | clicks |
|---|---|---|---|
| 1 | 1 | 2022-09-01 | 200 |
| 2 | 2 | 2022-09-01 | 300 |
| 3 | 1 | 2022-09-02 | 250 |
| 4 | 2 | 2022-09-02 | 350 |
We have to write a SQL query that will join these two tables on and and , and compute CTR per campaign per display date.
Answer:
This SQL query first joins the campaign and click data on the respective IDs and dates. Then, for each and , it sums up the number of clicks and displays, and finally calculates the CTR by dividing the total clicks by total displays and multiplying by 100 to get the result as percentage. The results are presented in ascending order of and .
To practice a similar SQL interview question on DataLemur's free online SQL coding environment, attempt this Meta SQL interview question:
SQL Question 10: In SQL, are values same the same as zero or a blank space?
NULLs are NOT the same as zero or blank spaces in SQL. NULLs are used to represent a missing value or the abscence of a value, whereas zero and blank space are legitimate values.
It's important to handle NULLs carefully, because they can mess up your analysis very easily. For example, if you compare a NULL value using the = operator, the result will always be NULL (because just like Drake, nothing be dared compared to NULL). That's why many data analysis in SQL start with removing NULLs using the function.
SQL Question 11: Calculate the Average Insurance Premium for Each Insurance Type
As a data analyst at Marsh & McLennan, an insurance firm, you are asked to figure out which type of insurance brings in the highest average premium to the company. Marsh & McLennan offers different types of insurance - such as auto, home, life etc. You have access to a policy table that records all policies sold by the company. Can you write a SQL query to get the average premiums per insurance type?
Example Input:
| policy_id | client_id | type | start_date | premium |
|---|---|---|---|---|
| 831 | 451 | Auto | 2022-03-05 | 1200 |
| 472 | 362 | Home | 2022-04-21 | 1000 |
| 953 | 123 | Life | 2022-06-11 | 500 |
| 674 | 265 | Auto | 2022-05-30 | 1500 |
| 285 | 981 | Home | 2022-02-14 | 1100 |
Expected Output:
| insurance_type | average_premium |
|---|---|
| Auto | 1350 |
| Home | 1050 |
| Life | 500 |
Answer:
In this query, the clause is used to divide the policies into groups by insurance types. The function is then applied to calculate the average premium for each type.
Preparing For The Marsh & McLennan SQL Interview
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Marsh & McLennan SQL interview is to solve as many practice SQL interview questions as you can! Besides solving the earlier Marsh & McLennan SQL interview questions, you should also solve the 200+ SQL coding questions which come from companies like Amazon, Microsoft, Meta, and smaller tech companies.
Each exercise has hints to guide you, full answers and crucially, there is an online SQL coding environment so you can right in the browser run your query and have it checked.
To prep for the Marsh & McLennan SQL interview it is also wise to practice SQL questions from other insurance companies like:
Find out how Generative AI is being used by Marsh & McLennan to solve complex problems and improve decision-making!
However, if your SQL skills are weak, forget about jumping right into solving questions – improve your SQL foundations with this free SQL for Data Analytics course.
This tutorial covers topics including INTERCEPT/EXCEPT and handling timestamps – both of these pop up often during SQL job interviews at Marsh & McLennan.
Marsh & McLennan Data Science Interview Tips
What Do Marsh & McLennan Data Science Interviews Cover?
In addition to SQL interview questions, the other types of problems to prepare for the Marsh & McLennan Data Science Interview are:
- Probability & Statistics Questions
- Python Pandas or R Coding Questions
- Product-Sense and Business Case Questions
- ML Modelling Questions
- Resume-Based Behavioral Questions
How To Prepare for Marsh & McLennan Data Science Interviews?
I believe the best way to prepare for Marsh & McLennan Data Science interviews is to read the book I wrote: Ace the Data Science Interview.
It covers 201 data interview questions sourced from FAANG & startups. The book's also got a refresher covering Python, SQL & ML. And finally it's helped thousands of people land their dream job in data, which is why it's earned 1000+ reviews on Amazon and a 4.5-star rating.
While the book is more technical, it's also important to prepare for the Marsh & McLennan behavioral interview. Start by understanding the company's values and mission.