logo

11 Fannie Mae SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

At Fannie Mae, SQL is often used for managing and querying vast real estate databases, and performing data cleansing. So, it shouldn't surprise you that Fannie Mae almost always asks SQL problems during interviews for Data Analyst, Data Science, and BI jobs.

Thus, to help you prepare, we've curated 11 Fannie Mae SQL interview questions – can you answer each one?

Fannie Mae SQL Interview Questions

11 Fannie Mae SQL Interview Questions

SQL Question 1: Identify the Top Mortgage Loan Users at "Fannie Mae"

Fannie Mae, popularly known for its mission to provide Mortgage loans, has a vast customers' database. The business wants to identify its Power Users i.e., the users with maximum number of mortgage loans applied in last year. The higher the number of mortgage loans, the greater the value of the user for the business. Can you write a SQL query to find out the having the highest number of mortgage loans applied in the last year?

Example Input:
loan_iduser_idloan_dateloan_amount
10712310/08/2021 00:00:00350000
28026516/03/2021 00:00:00500000
29232318/06/2021 00:00:00400000
33529226/07/2021 00:00:00200000
45109105/05/2021 00:00:00250000

Answer:


This query first filters the table to only contain loans from the last year (2021). It then groups the results by user and counts how many loans each user has. The results are then ordered by the number of loans in descending order, and the top user (i.e., the user with the most loans) is selected. Only the ID of this user and the number of loans they have are returned.

To practice a related customer analytics question on DataLemur's free online SQL code editor, try this Microsoft SQL Interview problem: Microsoft SQL Interview Question: Super Cloud Customer

SQL Question 2: Top Three Salaries

Suppose you had a table of Fannie Mae employee salary data. Write a SQL query to find the top three highest paid employees in each department.

Fannie Mae Example Input:

employee_idnamesalarydepartment_id
1Emma Thompson38001
2Daniel Rodriguez22301
3Olivia Smith20001
4Noah Johnson68002
5Sophia Martinez17501
8William Davis68002
10James Anderson40001

Example Input:

department_iddepartment_name
1Data Analytics
2Data Science

Example Output:

department_namenamesalary
Data AnalyticsJames Anderson4000
Data AnalyticsEmma Thompson3800
Data AnalyticsDaniel Rodriguez2230
Data ScienceNoah Johnson6800
Data ScienceWilliam Davis6800

You can solve this interview question directly within the browser on DataLemur:

Top 3 Department Salaries

Answer:

We use the DENSE_RANK() window function to generate unique ranks for each employee's salary within their department, with higher salaries receiving lower ranks. Then, we wrap this up in a CTE and filter the employees with a ranking of 3 or lower.


If the code above is confusing, you can find a step-by-step solution with hints here: Top 3 Department Salaries.

BTW Fannie Mae has a blog page! Read up on all the latest updates directly from the source.

SQL Question 3: What are some similarities and differences between unique and non-unique indexes?

While both types of indexes improve the performance of SQL queries by providing a faster way to lookup rows of data, a unique index enforces the uniqueness of the indexed columns, meaning that no duplicate values are allowed in the indexed columns. On the other hand, a non-unique index allows duplicate values in the indexed columns.

Here is an example of a unique index on the column of a table of Fannie Mae employees:


This index would ensure that no two Fannie Mae employees have the same , which could be used as a unique identifier for each employee.

Here is an example of a non-unique index on the column of the same table:


This index would not enforce uniqueness, but it could be used to improve the performance of queries that filter or sort the data based on the column. For example, if you want to retrieve all employees who were Data Analysts, the database can use the index to quickly locate and retrieve the desired records without having to search through all the employees.

Fannie Mae SQL Interview Questions

SQL Question 4: Calculate Monthly Average Loan Amount

Fannie Mae provides guarantee to mortgage lenders. It's crucial for them to understand the pattern of loan amounts they have been guaranteeing. For this question, you are asked to write a SQL query that calculates the monthly average loan amount for each state.

Assume you have the following table called loans:

Example Input:
loan_idloan_dateloan_amountstate
1012022-01-01250000NY
1022022-01-10300000NY
1032022-02-01280000CA
1042022-02-15350000CA
1052022-03-01450000NY
1062022-03-05260000CA

Your task is to calculate the monthly average loan amount for each state.

Expected output:

Example Output:
monthstateavg_loan_amount
2022-01NY275000
2022-02CA315000
2022-03NY450000
2022-03CA260000

Answer:


Explanation:

In this SQL query, we are grouping the data by month (using the function, which truncates the date to the precision specified, in this case 'month') and state. For each group, we calculate the average loan amount using the function. Finally, we order our result by month and state.

Pro Tip: Window functions are a frequent SQL interview topic, so practice all the window function problems on DataLemur

SQL Interview Questions on DataLemur

SQL Question 5: What's a self-join, and when would you use one?

A self-join is a type of JOIN where a table is joined to itself. To execute a self-join, you must include the table name twice in the FROM clause and assign a different alias to each instance. You can then join the two copies of the table using a JOIN clause, and use a WHERE clause to specify the relationship between the rows.

For example, say you had website visitor data for Fannie Mae, exported from the company's Google Analytics account. In support of the web-dev team, you had to analyze pairs of pages for UX or navigational issues. As part of that analysis, you wanted to generate all pairs of URLs, but needed to avoid pairs where both the URLs were the same since that's not a valid pair.

The self-join query would like the following:


This query returns the url of each page () along with the url of the page that referred to it (). The self-join is performed using the field, which specifies the id of the page that referred the visitor to the current page, and avoids any pages that referred themself (aka data anomalies).

SQL Question 6: Filter Customers Based on Loan Status and Property Type

As a data analyst at Fannie Mae, you have been asked to filter out customer records from the 'Loans' database table to track those who have an 'Active' loan status for 'Single Family' residences. You're also asked to filter based on the loan amount, specifically looking for loans that exceed $200,000.

Below is an example of how the data might look in the 'Loans' table:

Example Input:
loan_idcustomer_idloan_statusproperty_typeloan_amount
001564ActiveSingle Family250000
002317ActiveMulti Family180000
003284ClosedSingle Family220000
004458ActiveSingle Family230000
005186ActiveSingle Family190000

Your objective is to write a PostgreSQL query which filters out these specific customers.

Answer:


This query will return all fields from the Loans table () where the loan status is 'Active', the property type is 'Single Family', and the loan amount exceeds $200,000 ().

SQL Question 7: When would you use the function?

The function allows you to select the first non-NULL value from a list of values. It is often used to replace a NULL value with a default value in order to run aggregate functions like or on a column without being affected by values.

For example, if you conducted a customer satisfaction survey for a company and asked customers to rate their level of agreement with a statement on a scale of 1 to 10, some customers may not have answered all the questions, resulting in NULL values in the survey data:

customer_idquestion_idagree_amount
10119
101210
20216
2022NULL
30314
3032NULL

To replace the NULLs in the column with the default value of 5 which corresponds to neutral on the survey, you could run the following SQL query:


You'd get the following output:

customer_idquestion_idagree_amount
10119
101210
20216
20225
30314
30325

SQL Question 8: Average Mortgage Amount per Customer

At Fannie Mae, you have access to the table which contains data of the mortgages extended by the company to its customers over the years. Write a SQL query to find the average mortgage amount that each customer has taken from Fannie Mae.

Example Input:
mortgage_idcustomer_idissue_dateamount
10122306/12/2015250000
10222302/20/2018300000
10345611/29/2011125000
10445605/18/2017150000
10578907/01/2021400000

Answer:


This query groups records by the field and calculates the average for each group. The function calculates the average value of the field over each group. The result is a list of customers and the average mortgage amount they have taken.

Example Output:
customer_idaverage_amount
223275000
456137500
789400000

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for calculating highest values per group or this Stripe Repeated Payments Question which is similar for identifying repeated transactions by the same customer.

SQL Question 9: Analysis of Click-Through Rates for Fannie Mae's Digital Products

The marketing team at Fannie Mae wants to analyze the efficiency of their promotional campaigns, focusing on their digital products (like their mobile application). An important metric they are interested in is the Click-Through Rate (CTR), defined as the proportion of users who click on an ad link to the total number of users who view the ad.

More specifically, they would like to know the click-through rates of their ads on a monthly basis, with each ad promoting a specific digital product. By understanding these rates, they can pinpoint when and where their marketing strategies are most effective and take targeted actions to boost the popularity of their digital products.

We have two tables - and . The table logs the times when users viewed an ad, with each row representing an instance of an ad being shown to a user. The table logs the times when users clicked on one of these ads.

Example Input:
impression_iduser_idtime_impressedproduct
112307/01/2022 00:00:00App v1.0
226507/01/2022 00:00:00App v1.0
336207/01/2022 00:00:00App v1.0
419207/01/2022 00:00:00App v1.0
598107/01/2022 00:00:00App v1.0
Example Input:
click_iduser_idtime_clickedproduct
112307/01/2022 01:00:00App v1.0
226507/01/2022 01:00:00App v1.0
336207/01/2022 02:00:00App v1.0

Answer:


This query will give the month, product, and click-through rate (in percentage terms) by joining two subqueries: one that groups the total number of impressions per month and product, and another that groups the total number of clicks per month and product. The click-through rate is calculated by dividing the total number of clicks by the total number of impressions (and multiplied by 100 to get a percentage). Usage of LEFT JOIN ensures that even products that didn't get any clicks, but impressions are also included in the final result. The output is ordered by month and product for better readability.

To practice a related problem on DataLemur's free online SQL coding environment, solve this Meta SQL interview question: Facebook Click-through-rate SQL Question

SQL Question 10: What is a foreign key?

A foreign key is a field in a database table that links to the primary key of another table, establishing a connection between the two tables. For example, let's analyze Fannie Mae's Google Ads campaigns data:

:

+------------+------------+------------+------------+ | ad_id | campaign_id| keyword | click_count| +------------+------------+------------+------------+ | 1 | 201 | Fannie Mae reviews | 120 | | 2 | 202 | Fannie Mae pricing | 150 | | 3 | 101 | buy Fannie Mae | 65 | | 4 | 101 | Fannie Mae alternatives | 135 | +------------+------------+------------+------------+

is a foreign key that connects to the of the corresponding Google Ads campaign. This establishes a relationship between the ads and their campaigns, enabling easy querying to find which ads belong to a specific campaign or which campaigns a specific ad belongs to.

The table may also have multiple foreign keys that reference primary keys in other tables. For example, and foreign keys could be used to link each ad to its ad group and the Google Ads account that the campaigns belong to, respectively.

SQL Question 11: Analyzing Mortgage Data

Fannie Mae, as a mortgage loan company, has a database that stores all the data regarding their loans, including the borrower, loan amount, property state, and date when the loan was acquired. They want to analyze their loans data by identifying the state with the highest total loan amounts each year.

Assuming we have a table named 'loans' in the following format:

Example Input:

Given this, provide a SQL query that returns the state with the highest total loan amount for each year.

Example Output:

Answer:

Given this, we can use a PostgreSQL query as shown below:


This SQL query first creates a temporary table 'yearly_totals' with the total loan amount for each state each year. Then, for each year, it selects the state with the highest total loan amount.

Preparing For The Fannie Mae SQL Interview

The best way to prepare for a SQL interview, besides making sure you have strong SQL fundamentals, is to practice a ton of real SQL questions that were asked in recent job interviews. Beyond just solving the earlier Fannie Mae SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like Facebook, Google and unicorn tech startups. DataLemur Question Bank

Each DataLemur SQL question has multiple hints, step-by-step solutions and crucially, there's an interactive SQL code editor so you can right online code up your SQL query answer and have it executed.

To prep for the Fannie Mae SQL interview you can also be a great idea to solve SQL problems from other mortgage & loan companies like:

However, if your SQL query skills are weak, don't worry about jumping right into solving questions – refresh your SQL knowledge with this DataLemur SQL tutorial.

SQL tutorial for Data Scientists & Analysts

This tutorial covers topics including joining a table to itself and filtering on multiple conditions using AND/OR/NOT – both of which pop up frequently in Fannie Mae SQL interviews.

Fannie Mae Data Science Interview Tips

What Do Fannie Mae Data Science Interviews Cover?

In addition to SQL interview questions, the other topics covered in the Fannie Mae Data Science Interview are:

Fannie Mae Data Scientist

How To Prepare for Fannie Mae Data Science Interviews?

I'm a tad biased, but I believe the optimal way to prep for Fannie Mae Data Science interviews is to read my book Ace the Data Science Interview.

The book has 201 interview questions taken from Microsoft, Amazon & startups. It also has a crash course on Stats, ML, & Data Case Studies. 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.

Ace the DS Interview