10 M&T Bank SQL Interview Questions (Updated 2024)
M&T Bank employees write SQL queries often for data extraction and analysis for lending risk assessment. For this reason M&T Bank frequently asks SQL coding questions during interviews for Data Science, Data Engineering and Data Analytics jobs.
Thus, to help you study, we've curated 10 M&T Bank SQL interview questions – able to answer them all?
10 M&T Bank SQL Interview Questions
SQL Question 1: Calculate the Running Total of Deposits for Each Customer
As an analyst at M&T Bank, you have been given a task to analyze the deposits made by customers. Write a SQL query to calculate the running total of deposits for each customer, ordered by the deposit date. A "running total" for a row is defined as the sum of all the deposits made by that customer up to and including that row.
Consider a 'deposit' table as follows:
Example Input:
| deposit_id | customer_id | deposit_date | amount |
|---|---|---|---|
| 101 | 123 | 01/06/2022 | 2000 |
| 102 | 123 | 01/07/2022 | 1000 |
| 103 | 456 | 01/07/2022 | 3000 |
| 104 | 123 | 01/09/2022 | 500 |
| 105 | 456 | 01/09/2022 | 1500 |
In this case, our output should be a table that shows the customer id, deposit date, and running total of deposits for each customer up to the given date.
Example Output:
| customer_id | deposit_date | running_total |
|---|---|---|
| 123 | 01/06/2022 | 2000 |
| 123 | 01/07/2022 | 3000 |
| 456 | 01/07/2022 | 3000 |
| 123 | 01/09/2022 | 3500 |
| 456 | 01/09/2022 | 4500 |
Answer:
This query uses a window function to calculate the running sum of deposits grouped by customer id and ordered by the deposit date. For each row, it sums deposits made by the same customer including and prior to the current row.
For more window function practice, try this Uber SQL problem on DataLemur's online SQL coding environment:
Look at the open job listings at MT&T Bank and see what interests you!
SQL Question 2: Top Department Salaries
Suppose there was a table of M&T Bank employee salary data. Write a SQL query to find the top three highest paid employees within each department.
M&T Bank Example Input:
| employee_id | name | salary | department_id |
|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 |
| 2 | Daniel Rodriguez | 2230 | 1 |
| 3 | Olivia Smith | 2000 | 1 |
| 4 | Noah Johnson | 6800 | 2 |
| 5 | Sophia Martinez | 1750 | 1 |
| 8 | William Davis | 6800 | 2 |
| 10 | James Anderson | 4000 | 1 |
Example Input:
| department_id | department_name |
|---|---|
| 1 | Data Analytics |
| 2 | Data Science |
Example Output:
| department_name | name | salary |
|---|---|---|
| Data Analytics | James Anderson | 4000 |
| Data Analytics | Emma Thompson | 3800 |
| Data Analytics | Daniel Rodriguez | 2230 |
| Data Science | Noah Johnson | 6800 |
| Data Science | William Davis | 6800 |
Try this question interactively on DataLemur:
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 detailed solution with hints here: Top 3 Department Salaries.
SQL Question 3: How is a foreign key different from a primary key in a database?
To explain the difference between a primary key and foreign key, let's start with an example M&T Bank sales database:
:
+------------+------------+------------+------------+ | order_id | product_id | customer_id| quantity | +------------+------------+------------+------------+ | 1 | 303 | 1 | 2 | | 2 | 404 | 1 | 1 | | 3 | 505 | 2 | 3 | | 4 | 303 | 3 | 1 | +------------+------------+------------+------------+
In this table, could be a primary key. It is unique for each row in the table and cannot contain null values.
and could both be foreign keys. They reference the primary keys of other tables, such as a Products table and a Customers table, respectively. This establishes a relationship between the table and the other tables, such that each row in the sales database corresponds to a specific product and a specific customer.
SQL Question 4: Loan Payment Management
As a data analyst at M&T Bank, you've been asked to design a database for managing loan payments. You need to keep track of the clients, their loans, and the payments they make. Each loan is linked to a client, and each payment is linked to a loan.
Here are the details that need to be stored for each entity:
- Clients: Client ID, Name, and Email
- Loans: Loan ID, Client ID, Loan Amount, and Outstanding Amount
- Payments: Payment ID, Loan ID, Payment Amount, and Payment Date
The primary way the database will be used is to find out the outstanding amount for each client, so the design should facilitate this kind of query.
Now, write a PostgreSQL query to find out the Outstanding Loan Amount for each Client.
Your task includes:
- Design the , , and table.
- Generate sample data for each table.
- Write a PostgreSQL query to compute the Outstanding Loan Amount for each client.
Sample Input:
| client_id | name | |
|---|---|---|
| 1001 | John Doe | johndoe@example.com |
| 1002 | Jane Doe | janedoe@example.com |
Sample Input:
| loan_id | client_id | loan_amount | outstanding_amount |
|---|---|---|---|
| 5001 | 1001 | 50000 | 20000 |
| 5002 | 1002 | 70000 | 50000 |
Sample Input:
| payment_id | loan_id | payment_amount | payment_date |
|---|---|---|---|
| 1 | 5001 | 2000 | 2022-12-01 |
| 2 | 5001 | 1000 | 2022-12-02 |
| 3 | 5002 | 5000 | 2022-12-03 |
Answer:
This query joins the and tables using the field. It then groups the resulting records by and , and computes the sum of the field within each group. The result is a list of clients and their total outstanding loan amounts.
SQL Question 5: What do the SQL commands / do?
The / operator is used to remove to return all rows from the first SELECT statement that are not returned by the second SELECT statement.
Note that is available in PostgreSQL and SQL Server, while MINUS is available in MySQL and Oracle (but don't stress about knowing which DBMS supports what exact commands since the interviewers at M&T Bank should be lenient!).
For a tangible example in PostgreSQL, suppose you were doing an HR Analytics project for M&T Bank, and had access to M&T Bank'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:
SQL Question 6: Filter Customer Records Based on Account Transactions
Company M&T Bank has given you access to their customer records database. The database includes a table named with records indicating the customer's account balances and transactions. You have been asked to write an SQL query to filter out records of customers who have made transactions over $10,000 in the past month and have 'Checking' account type.
Example Input:
| account_id | customer_id | account_type | transaction_date | amount |
|---|---|---|---|---|
| 101 | 111 | Checking | 2022-07-01 | 11000 |
| 102 | 112 | Savings | 2022-07-03 | 20000 |
| 103 | 113 | Checking | 2022-07-05 | 5000 |
| 104 | 114 | Checking | 2022-07-10 | 12000 |
| 105 | 115 | Savings | 2022-07-15 | 9000 |
Note: All amounts and dates in this table are hypothetical and do not represent real customer data.
Answer:
The SQL query for the PostgreSQL you would use to solve this problem would be:
This SQL query filters from the table all records where the is 'Checking' and over $10,000 with transactions made in the past month. '1 month' interval is subtracted from the current date to get the transactions made in the past month.
SQL Question 7: What sets relational and NoSQL databases apart?
While knowing this answer is beyond the scope of most Data Analyst & Data Science interviews, Data Engineers at M&T Bank should be at least aware of SQL vs. NoSQL databases.
Relational databases and non-relational (NoSQL) databases have some key differences, particularly in terms of how data is stored. Whereas relational databases have tables, made up of rows and columns, NoSQL databases use various data models like:
- Wide-Column Stores – this database uses tables, rows, and columns, but unlike a relational database, the names and format of the columns can vary from row to row within the same table
- Key-Value Stores – instead of rows and columns, you have keys, where each key is associated with only one value in a collection (similar to a Python dictionary data structure!)
- Graph Stores – instead of rows of data, you have nodes, and then can also have edges between entities (much like a Graph Data Structure for those who've taken a Computer Science data structures & algorithms class)
This added flexibility makes NoSQL databases well-suited for handling non-tabular data or data with a constantly changing format. However, this flexibility comes at the cost of ACID compliance, which is a set of properties (atomic, consistent, isolated, and durable) that ensure the reliability and integrity of data in a database. While most relational databases are ACID-compliant, NoSQL databases may not provide the same level of guarantees.
SQL Question 8: Calculating Average Loan Amounts
As an analyst at M&T Bank, we are interested in understanding the average loan amount our customers are applying for. Please calculate the average loan amount applied for each type of loan for each customer in the year 2021.
Example Input:
| loan_id | customer_id | application_date | loan_type | loan_amount |
|---|---|---|---|---|
| 101 | 451 | 01/15/2021 | Mortgage | 350000 |
| 102 | 451 | 02/10/2021 | Auto | 20000 |
| 103 | 657 | 04/30/2021 | Personal | 12000 |
| 104 | 451 | 06/25/2021 | Mortgage | 400000 |
| 105 | 657 | 11/19/2021 | Auto | 18000 |
Example Output:
| customer_id | loan_type | avg_loan_amount |
|---|---|---|
| 451 | Mortgage | 375000.00 |
| 451 | Auto | 20000.00 |
| 657 | Personal | 12000.00 |
| 657 | Auto | 18000.00 |
Answer:
In the data provided, we have loan information including the customer_id, loan type, and loan amount. By grouping our data by the customer_id and the loan_type, and calculating the average on the loan_amount we can get the average loan amount per customer per loan type in 2021.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for calculating top factors within categories or this JPMorgan Chase Cards Issued Difference Question which is similar for dealing with financial amounts over time.
SQL Question 9: Average Loan Amount By Type and Year
M&T Bank offers various types of loans to its customers, such as personal loans, home loans, auto loans etc. The bank needs to analyze the average loan amount provided to their customers for each type of loan, each year. For this, they want to write an SQL query that will return the average loan amount given for all types of loan per year.
For this problem, we're assuming the existence of a table with the following format.
Example Input:
| loan_id | customer_id | loan_type | loan_date | loan_amount |
|---|---|---|---|---|
| 001 | 112 | auto | 01/15/2020 | 15000 |
| 002 | 245 | home | 02/15/2020 | 200000 |
| 003 | 678 | personal | 01/16/2021 | 5000 |
| 004 | 902 | auto | 02/18/2021 | 20000 |
| 005 | 345 | home | 03/20/2022 | 300000 |
Answer:
Here is the SQL query they can use. This query groups by the and with the clause, and it uses the function to calculate the average for each group.
This query would return a result set with each row containing the type of loan, the year, and the average amount loaned that year for that type of loan.
The is used to get the year part from the column.
Please ensure you have the necessary rights to execute this command and the relevant tables exist. The average value might be returned as a decimal number.
SQL Question 10: How would you speed up a slow SQL query?
There's several steps you can take to troubleshoot 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. After that, you can start changing your query, depending on what the source of the query performance issue is.
Generally, indexes can help speed up queries. Also de-normalizing your tables might help, to remove slow joins.
Lastly, you could always just upgrade your hardware! Time and money wasted improving query performance could just be better spent on mroe powerful database servers!
While this is a very surface-level approach to optimizing SQL query performance, it should do the trick for Data Analyst interviews and Data Science interviews at M&T Bank. Data Engineers should know a bit more about the and before the interview.
How To Prepare for the M&T Bank SQL Interview
The key to acing a M&T Bank SQL interview is to practice, practice, and then practice some more!
In addition to solving the above M&T Bank SQL interview questions, you should also solve the 200+ SQL coding questions which come from companies like Netflix, Google, and Amazon.
Each exercise has multiple hints, detailed solutions and most importantly, there is an online SQL code editor so you can easily right in the browser your SQL query answer and have it graded.
To prep for the M&T Bank SQL interview it is also useful to practice SQL questions from other banking & finanacial services companies like:
However, if your SQL foundations are weak, forget about going right into solving questions – improve your SQL foundations with this SQL interview tutorial.
This tutorial covers SQL topics like window functions and using ORDER BY – both of which come up frequently during M&T Bank SQL interviews.
M&T Bank Data Science Interview Tips
What Do M&T Bank Data Science Interviews Cover?
In addition to SQL interview questions, the other types of problems to practice for the M&T Bank Data Science Interview include:
- Probability & Stats Questions
- Python or R Coding Questions
- Product Analytics Questions
- Machine Learning Questions
- Behavioral & Resume-Based Questions
How To Prepare for M&T Bank Data Science Interviews?
I'm a tad biased, but I think the optimal way to study for M&T Bank Data Science interviews is to read my book Ace the Data Science Interview.
The book solves 201 interview questions taken from FAANG (FB, Apple, Amazon, Netflix, Google). It also has a refresher on SQL, AB Testing & ML. And finally it's vouched for by the data community, which is why it's got over 1000+ 5-star reviews on Amazon.