9 FM Global SQL Interview Questions (Updated 2024)
Data Analysts and Data Engineers at FM Global uses SQL to analyze risk management data, including property valuation and natural disaster risk assessments, as well as optimizing property insurance predictions, such as identifying high-risk properties. Because of this, FM Global often tests jobseekers SQL interview problems.
So, to help you practice, here’s 9 FM Global SQL interview questions – can you solve them?
9 FM Global SQL Interview Questions
SQL Question 1: Analyze Property Risk Data
You are given a table with historical data about properties insured by FM Global. The data includes property id, inspection date, and risk score assigned during each inspection. The risk score ranges from 1 to 10, with 1 indicating the lowest risk and 10 the highest.
Write a SQL query to find the difference in the risk scores between the latest and second latest inspections for each property. Include only properties that have had at least two inspections.
Example Input:
| property_id | inspection_date | risk_score |
|---|---|---|
| 1001 | 2012-03-15 | 6 |
| 1002 | 2012-05-01 | 7 |
| 1001 | 2014-06-20 | 8 |
| 1003 | 2010-11-12 | 5 |
| 1002 | 2014-07-21 | 7 |
| 1001 | 2016-09-30 | 7 |
Example Output:
| property_id | latest_risk_score | second_latest_risk_score | score_diff |
|---|---|---|---|
| 1001 | 7 | 8 | -1 |
| 1002 | 7 | 7 | 0 |
Answer:
In this query, we are using window functions and to get the latest and second latest risk scores for each property. We then subtract the second latest score from the latest to find the difference. We include only properties that have a count greater than 1 in the clause to filter out properties that have had only one inspection.
To practice another window function question on DataLemur's free interactive SQL code editor, solve this Amazon SQL Interview Question:
SQL Question 2: Second Highest Salary
Given a table of FM Global employee salary data, write a SQL query to find the 2nd highest salary at the company.
FM Global Example Input:
| employee_id | salary |
|---|---|
| 1 | 2500 |
| 2 | 800 |
| 3 | 1000 |
| 4 | 1200 |
Example Output:
| second_highest_salary |
|---|
| 1200 |
Check your SQL query for this problem directly within the browser on DataLemur:
Answer:
You can find a detailed solution here: 2nd Highest Salary.
SQL Question 3: Can you explain the distinction between a clustered and a non-clustered index?
Clustered and non-clustered indexes are both used to improve query performance, but they differ in how they are implemented.
A clustered index determines the physical order of the data rows in a table, while a non-clustered index does not. As a result, a table can have only one clustered index, but it can have multiple non-clustered indexes.
In terms of query efficiency & performance, a clustered index is generally faster for SELECT queries (reads) but updates (writes) to a clustered index are slower, as they require the data rows to be physically rearranged.
SQL Question 4: Building Policies and Claims Analysis for FM Global.
FM Global provides comprehensive commercial and industrial property insurance, engineering-driven underwriting and risk management solutions. You have been given three tables: , , and . table has , , and . table has , , , . table has , , , , .
Your task is to find out the total policy coverage amount and total claim amount per client for the year 2021 so the company can evaluate risk and formulate its strategy for the next year.
Example Input:
| policy_id | client_id | coverage_amount |
|---|---|---|
| 1 | 101 | 1000000 |
| 2 | 102 | 2500000 |
| 3 | 103 | 3000000 |
| 4 | 104 | 5000000 |
Example Input:
| client_id | client_name | address | industry_type |
|---|---|---|---|
| 101 | Client A | Street 1, City A | IT |
| 102 | Client B | Street 2, City B | Financial Services |
| 103 | Client C | Street 3, City C | Health Care |
| 104 | Client D | Street 4, City D | Manufacturing |
Example Input:
| claim_id | policy_id | claim_amount | claim_status | date_of_claim |
|---|---|---|---|---|
| 1 | 1 | 500000 | Approved | 10/01/2021 |
| 2 | 2 | 800000 | Approved | 12/05/2021 |
| 3 | 3 | 1000000 | Denied | 05/01/2021 |
| 4 | 4 | 2000000 | Approved | 07/02/2021 |
Answer:
This query joins the three tables together. It calculates the total coverage for each client by grouping on from the table and sums the . It calculates the total claim amount only for approved claims in the year 2021. If a client has no claims in 2021, it shows a of 0 using the function. It returns the , , , and .
SQL Question 5: Could you describe the function of UNION in SQL?
is used to combine the results of multiple statements into a single result set.
Suppose you were doing an HR Analytics project for FM Global, and needed to analyze both FM Global's contractors and employees. You could use in the following way:
This statement would return a combined result set of FM Global contractors and employees who were hired after the start of the year 2023.
SQL Question 6: Filtering Client Risk Ratings
Suppose you have been given a task at FM Global, your task is to filter all the client records based on their risk rating. FM Global uses risk ratings to quantify each client's potential risk. You need to find all those clients which have high (3) risk rating, are from the 'USA', and were last assessed after .
Assume you have a table in your database:
Example Input:
| client_id | client_name | country | last_assessment_date | risk_rating |
|---|---|---|---|---|
| 101 | ANG Enterprise | USA | 05/05/2022 | 3 |
| 102 | BGH Corporation | Canada | 04/10/2022 | 2 |
| 103 | CHELL Industries | USA | 02/14/2022 | 2 |
| 104 | DELL Technologies | USA | 03/05/2022 | 1 |
| 105 | ESOFT Solutions | USA | 05/25/2022 | 3 |
We need to filter out those records which are from 'USA', have a risk_rating of 3, and were last assessed after .
Answer:
This query joins the boolean conditions using so that all conditions must be true for a row in to be selected. It matches clients based on their risk_rating, country, and the date they were last assessed. The date comparison only matches dates after January 1, 2022. This allows us to select high-risk clients from the USA, who were last assessed after .
SQL Question 7: How would you speed up a slow SQL query?
First things first, figure out why the query is slow! You can use and commands in PostgreSQL to identify any performance bottlenecks. You might discover that your query is inefficient, or that there are many database writes at the same time you are doing a read, or maybe too many people are concurrently running queries on the same database server.
For Data Analyst and Data Science positions, knowing the ins-and-outs of SQL performance tuning is out-of-scope for the SQL interview round. However, knowing that joins are expensive, and indexes can speed up queries, is generally enough of an answer for FM Global SQL interviews.
SQL Question 8: Average Claim Value by Policy Type
FM Global is a company that specializes in mutual insurance, so it handles policies for various types of properties and navigates numerous insurance claims. As an analyst for the company, you might be interested in finding out the average claim value per policy type in the past year.
In order to accomplish this, consider the following tables:
Example Input:
| policy_id | property_type | customer_id |
|---|---|---|
| 101 | Commercial | 5001 |
| 102 | Residential | 4025 |
| 103 | Industrial | 3502 |
| 104 | Commercial | 4750 |
| 105 | Industrial | 8921 |
Example Input:
| claim_id | policy_id | claim_value | claim_date |
|---|---|---|---|
| 9001 | 101 | 50000 | 2021-06-07 |
| 9002 | 102 | 25000 | 2021-07-10 |
| 9003 | 103 | 75000 | 2021-08-18 |
| 9004 | 104 | 40000 | 2021-09-26 |
| 9005 | 105 | 100000 | 2021-10-05 |
Now, you need to write a SQL query to find the average claim value by policy type in the past year.
Answer:
This query joins the and tables on the column, then filters for claims from the past year. It then groups the data by , and calculates the average for each .
SQL Question 9: Finding Specific Customers from USA
As a data analyst for FM Global, your task is to find all the customers whose name starts with 'J', end with an 'N' and are based in the USA. Your filtered list should only include their customer_id, full_name, and country.
Example Input:
| customer_id | full_name | country |
|---|---|---|
| 1001 | John Dawson | USA |
| 1002 | James Brown | Canada |
| 1003 | Jennifer Aniston | USA |
| 1004 | Justin Timberlake | USA |
| 1005 | Janet Jackson | USA |
| 1006 | Jack Nicholson | UK |
| 1007 | Jill Johnson | USA |
| 1008 | Henry Ford | USA |
Example Output:
| customer_id | full_name | country |
|---|---|---|
| 1001 | John Dawson | USA |
| 1003 | Jennifer Aniston | USA |
| 1004 | Justin Timberlake | USA |
| 1005 | Janet Jackson | USA |
| 1007 | Jill Johnson | USA |
Answer:
The above SQL query uses the LIKE keyword to filter the 'full_name' column where the string starts with 'J' and ends with 'N'. The '%' is used as a wildcard character that matches any sequence of characters. The statement 'country = 'USA'' ensures only customers based in USA are included in the result.
How To Prepare for the FM Global SQL Interview
The key to acing a FM Global SQL interview is to practice, practice, and then practice some more! Besides solving the earlier FM Global SQL interview questions, you should also solve the 200+ SQL questions on DataLemur which come from companies like Google, Microsoft and Silicon Valley startups.
Each interview question has multiple hints, step-by-step solutions and best of all, there's an interactive coding environment so you can right online code up your SQL query and have it graded.
To prep for the FM Global SQL interview it is also a great idea to solve interview questions from other insurance companies like:
- First American Financial SQL questions
- Old Republic SQL questions
- American Tower SQL questions
- Western & Southern SQL questions
Stay ahead of the curve with FM Global's latest news and research on commercial property insurance and risk management!
But if your SQL foundations are weak, don't worry about going right into solving questions – strengthen your SQL foundations with this DataLemur SQL tutorial.
This tutorial covers SQL concepts such as creating summary stats with GROUP BY and CASE/WHEN/ELSE statements – both of which pop up routinely during FM Global interviews.
FM Global Data Science Interview Tips
What Do FM Global Data Science Interviews Cover?
Besides SQL interview questions, the other types of problems covered in the FM Global Data Science Interview include:
- Statistics and Probability Questions
- Coding Questions in Python or R
- Data Case Study Questions
- ML Interview Questions
- Behavioral Interview Questions centered on FM Global culture and values
How To Prepare for FM Global Data Science Interviews?
To prepare for FM Global Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from Google, Microsoft & tech startups
- a crash course covering Product Analytics, SQL & ML
- over 1000+ 5-star reviews on Amazon
Also focus on the behavioral interview – prepare for it using this behavioral interview question bank.